SQL command to add up the vacancies occupied on a specific day of a parking lot

Asked

Viewed 84 times

0

Talk guys, I’m having a little trouble finishing a college job and it would be really helpful if you guys would help me with a little VIEW.

Here are the fields of the parking table:

CREATE TABLE Seg.Estacionamento (
valor_hora decimal(4,2),
numero_vagas integer,
hora_saida datetime,
hora_entrada datetime,
vaga varchar(6)
)

This table records each entry in the parking lot.

The thing is, I’m doing a database project for a shopping mall and I need to make a report of busy parking spaces per day.

Would something like this

Quantidade de Vagas | Data
                    |
                    |
                    |

The Number of Vacancies field is the sum of all vacancies of the date in question and the Date has to be day-to-day. But I have no idea how to write this VIEW, what I’ve tried so far was something like this, but I know it’s completely wrong:

CREATE VIEW v_Vagas
AS
SELECT SUM(E.vaga) as "Quantidade de vagas ocupadas" FROM Estacionamento AS E
WHERE EXISTS(SELECT DAY(E.hora_entrada))

Please help me, it’s very important

sql sql-server

  • Well, there are some things that remain open in your question. For example, a vacancy that has been filled and vacated 3 times during the day counts as 1 or 3?

  • The report would not be "of maximum number of vacancies occupied per day"? Or what you want to know is which vacancies were occupied each day? // What contains the column vaga?

1 answer

0

Judging by the view you tried to create, the script below will bring you how many vacancies have been filled on a given day. Note that your "input time" field is a DATETIME, so we will select only the date information with CAST().

CREATE VIEW v_Vagas
AS

    SELECT SUM(CAST(E.vaga AS INT)) AS 'Quantidade de vagas ocupadas', CAST(hora_entrada AS DATE) AS 'Data'
    FROM Estacionamento AS E
    GROUP BY CAST(hora_entrada AS DATE)

GO

However, if the goal is just to know how many cars have parked during the day, we can count how many "entry time" lines exist on the same day, without needing the "vacancy field".

CREATE VIEW v_Vagas
AS

    SELECT COUNT(hora_entrada) AS 'Quantidade de vagas ocupadas',
        CAST(hora_entrada AS DATE) AS 'Data'
    FROM Estacionamento AS E
    GROUP BY CAST(hora_entrada AS DATE)

GO

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.