Select between two dates if one date contains null-CodePudding

Hi kind folks of stackoverflow!

I want this query to check if a room(rum, in swedish), is available between two dates. However, I would like the query to first check if the room has been checked out, otherwise use fDatum AND tDatum. If the room has been checked out, then it should use fDatum and checkUt.

In the database we store both when the room has been booked from and to (fDatum, tDatum), as well as which date the room has checked in and out (checkIn, checkUt).

EDIT: The output will show the room numbers of the rooms that are not booked between 2021-10-12 and 2021-10-14, along with the room type (single, double, family).

Below you will find the code in use right now. But if a guest checks out earlier than 2021-10-12, the room may still show up as booked if the room was booked to the 2021-10-12. The guest must still pay for the room for the 2021-10-12, but if the guest checked out early, the hotel wants to be able to rent out the room.

Select rumNr, rumTyp
From Rum
Where RumNr NOT IN (Select RumNr
                    From BokningRum
                    Where fDatum BETWEEN '2021-10-12' AND '2021-10-14'
                    OR tDatum BETWEEN '2021-10-12' AND '2021-10-14')
GROUP BY rumNr
;

so I want the code to work like this instead:

Select rumNr, rumTyp
From Rum
Where RumNr NOT IN (Select RumNr
From BokningRum
Where fDatum BETWEEN '2021-10-12' AND '2021-10-14'
                    OR checkUt BETWEEN '2021-10-12' AND '2021-10-14')
                    -- If checkUt is null, then do
                    -- Where fDatum BETWEEN '2021-10-12' AND '2021-10-14'
                    -- OR tDatum BETWEEN '2021-10-12' AND '2021-10-14')
GROUP BY rumNr
;

CodePudding user response：

Select rumNr, rumTyp
From Rum
Where RumNr NOT IN (
Select RumNr
                    From BokningRum
                    Where 
                    case when !isnull(checkUt) then
                     fDatum BETWEEN '2021-10-12' AND '2021-10-14'                    
                    OR  tDatum BETWEEN '2021-10-12' AND '2021-10-14'
                    else
                     fDatum BETWEEN '2021-10-12' AND '2021-10-14'  
                    end
                    )
GROUP BY rumNr;

CodePudding user response：

Thanks to @Guram, I managed to solve it. I know my explanation was not great but thank you all so much for your help. This is the final query that will earn me those extra marks.

Thanks again!

Select rumNr, rumTyp
From Rum
Where RumNr NOT IN (
Select RumNr
                    From BokningRum
                    Where 
                    case when checkUt<tDatum then
                     (fDatum BETWEEN '2021-10-12' AND '2021-10-14'                    
                    OR  checkUt BETWEEN '2021-10-12' AND '2021-10-14')
                    else
                    ( fDatum BETWEEN '2021-10-12' AND '2021-10-14'
                     OR  tDatum BETWEEN '2021-10-12' AND '2021-10-14')
                    end
                    )
GROUP BY rumNr;

The output will be the rooms that are available for booking those dates.

rumNr,rumTyp
1,Enkel
2,Enkel
3,Enkel
4,Enkel
5,Enkel
6,Enkel
7,Enkel
8,Enkel
9,Enkel
10,Enkel
11,Dubbel
12,Dubbel
13,Dubbel
14,Dubbel
16,Dubbel
17,Dubbel
18,Dubbel
19,Dubbel
20,Dubbel
21,Dubbel
23,Dubbel
25,Dubbel
26,Dubbel
27,Dubbel
28,Dubbel
29,Dubbel
30,Dubbel
31,Familje
32,Familje

CodePudding user response：

The result of the proposed SQL above #2:

The result of your original SQL, assuming rumNr is the PRIMARY KEY in Rum:

Notice rumNr = 7 in the result.

Your original SQL, with GROUP BY removed, with the assumption that rumNr is unique or the primary key of Rum.

Select rumNr, rumTyp
  From Rum
 Where RumNr NOT IN (
             Select RumNr
               From BokningRum
              Where 
                  case when checkUt<tDatum then
                       ( fDatum BETWEEN '2021-10-12' AND '2021-10-14'                    
                    OR  checkUt BETWEEN '2021-10-12' AND '2021-10-14')
                  else
                       ( fDatum BETWEEN '2021-10-12' AND '2021-10-14'
                    OR   tDatum BETWEEN '2021-10-12' AND '2021-10-14')
                  end
             )
-- GROUP BY rumNr
;

The setup:

CREATE TABLE Rum        (rumNr int, rumTyp VARCHAR(10));
CREATE TABLE BokningRum (RumNr int, fDatum DATE, tDatum DATE, checkUt DATE);

INSERT INTO Rum VALUES
   ( 1, 'type1')
 , ( 2, 'type1')
 , ( 3, 'type1')
 , ( 4, 'type1')
 , ( 5, 'type1')
 , ( 6, 'type1')
 , ( 7, 'type1')
 , ( 8, 'type1')
 , ( 9, 'type1')
 , (10, 'type1')
 , (30, 'type2')
;

INSERT INTO BokningRum VALUES
   ( 1, '2021-10-12', '2021-10-14', null)
 , ( 2, '2021-10-12', '2021-10-14', '2021-10-13')
 , ( 3, '2021-10-12', '2021-10-14', '2021-10-14')
 , ( 4, '2021-10-12', '2021-10-14', '2021-10-15')
 , ( 5, '2021-10-11', '2021-10-13', null)
 , ( 6, '2021-10-13', '2021-10-13', null)
 , ( 7, '2021-10-11', '2021-10-15', null)
 , (10, '2021-10-08', '2021-10-12', null)
;