Mvc List only available and not occupied rooms

Question

Mvc List only available and not occupied rooms

Navigation

#1 by (1 votes)

1

I have the following problem: A reservation has a client and a room associated with it. When creating a reservation, I have to fill in some fields (such as customer name, date of entry, date of departure and the room where you will be staying).

Room selection is done through a dropdownlist that shows all rooms created. The problem is this same, I do not want to show all the rooms created, but rather, all the rooms available, ie those that do not have reservations on the date of creation of the reservation.

The query I've assembled so far is this:

SELECT Quarto.ID_Quarto FROM Quarto
LEFT OUTER JOIN Reserva ON Quarto.ID_Quarto = Reserva.ID_Quarto
WHERE DataEntrada > GETDATE()
And DataSaida < GETDATE()
OR Reserva.ID_Quarto IS NULL

sql asp.net-mvc

asked by anonymous 22.05.2018 / 17:01

1 answer

Autofocus and Required in hidden fields by javascript List grid and list view with bootstrap 4

score 1 · Accepted Answer

Assuming the exit date will always be bigger than that of the entry, you can use the following:

SELECT DISTINCT Quarto.ID_Quarto
FROM Quarto Q1
LEFT JOIN Reserva R1 ON Q1.ID_Quarto = R1.ID_Quarto
WHERE Reserva.ID_Quarto IS NULL
   OR NOT EXISTS (SELECT 1 FROM Quarto Q2 
                  LEFT JOIN Reserva R2 ON Q2.ID_Quarto = R2.ID_Quarto
                  WHERE Q1.ID_Quarto = Q2.ID_Quarto
                    AND R2.DataEntrada <= GETDATE() 
                    AND (R2.DataSaida IS NULL OR R2.DataSaida > GETDATE())
                    -- o is null é desnecessário se a data de saída é previamente cadastrada