Question 115129 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL select available rooms

I work on an hotel app, and I am trying to query my sql db for the available rooms between 2 dates.

The table rate looks like this:

room_id date unavailable price
1 16/08/2022 false 10
1 17/08/2022 null 20
1 18/08/2022 null 30
2 16/08/2022 false 10
2 17/08/2022 true 20
2 18/08/2022 null 30
3 16/08/2022 false 10
3 17/08/2022 false 20
3 19/08/2022 false 30

I am looking for the rooms available between from = 16/08/2022 and to = 18/08/2022, i.e. the room_ids

  • with at least one row for every date between from and to
  • where none of these row has unavailable = true

expected output

room_id
1
Tags:

2

Answers


  1. 0

    use a not exists

      with t as (select * from rate 
     where date BETWEEN '2022-08-16' AND '2022-08-18')
    select distinct room_id from t a where not exists 
    ( select 1 from t b where a.room_id = b.room_id
      and b.unavailable = true)
    Login or Signup to reply.
  2. 0

    You can use a GROUP BYwhere you count the available dates and compare it to the dates you nned,

    more complex pattern would need more sophisticated algorithms

    SELECT "room_id" FROM rooms WHERE  "date" BETWEEN '2022-08-16'::date AND '2022-08-18'::date
AND ("unavailable" = false OR "unavailable" IS NULL)
GROUP BY "room_id"
HAVING COUNT(*) =  EXTRACT(DAY FROM '2022-08-18'::timestamp - '2022-08-16'::timestamp) + 1

    | room_id |
| ------: |
|       1 |

    db<>fiddle here

    every cimlication has its price in execution time

    WITH rooms_group as(
SELECT "room_id", "date", "unavailable", 
ROW_NUMBER() OVER(PARTITION BY "room_id", "date" ORDER BY CASE "unavailable" WHEN true Then 1
ELSE 2 END ) rn
FROM rooms )
SELECT "room_id" FROM rooms_group WHERE  "date" BETWEEN '2022-08-16'::date AND '2022-08-18'::date
AND ("unavailable" = false OR "unavailable" IS NULL)
AND rn = 1
GROUP BY "room_id"
HAVING COUNT(*) =  EXTRACT(DAY FROM '2022-08-18'::timestamp - '2022-08-16'::timestamp) + 1

    | room_id |
| ------: |
|       1 |

    db<>fiddle here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search