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

Question

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

Navigation

#1 by (0 votes)

0

Personally, I'm having a little problem finishing a college job and would be of great help if you helped me with a small 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.

This is the following, I'm doing a database project for a mall and I need to report a busy spot a day in the shopping center parking lot.

I would get something like this

Quantidade de Vagas | Data
                    |
                    |
                    |

The number of vacancies field is the sum of all vacancies for 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 has been 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

asked by anonymous 23.11.2017 / 00:50

1 answer

Error Undefined offset I get exited with non-zero status in this code pq?

score 0 · Answer 1

Judging by the view you tried to create, the script below will bring you how many vacancies were occupied on a given day. Note that your "entry_time" field is a DATETIME, so we will only select 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 "vacant" 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