Find all available BookingSlots in a certain timespan-CodePudding

I have a table with BookingSlots. Each slot is 15 minutes long.

id |     startTime       | userId  | status
-------------------------------------------
1  | 2022-01-01T01:00:00 | 1       | free
-------------------------------------------
2  | 2022-01-01T01:00:00 | 2       | free
-------------------------------------------
3  | 2022-01-01T01:15:00 | 1       | free
-------------------------------------------
4  | 2022-01-01T01:15:00 | 2       | booked

I want to find all BookingSlots that are free at a given date for a timespan of 15 minutes grouped by the userId. So when the I would run the query at 01:08:00, the result should be:

id |     startTime       | userId  | status
-------------------------------------------
1  | 2022-01-01T01:00:00 | 1       | free
-------------------------------------------
3  | 2022-01-01T01:15:00 | 1       | free

So far my query looks like this:

SELECT
    *
FROM
    "BookingSlot"
WHERE ("startTime" < '2022-01-01T01:08:00'
    AND '2022-01-01T01:08:00' < "startTime"   interval '15 minutes')
    OR("startTime" < timestamp '2022-01-01T01:08:00'   interval '15 minutes'
        AND timestamp '2022-01-01T01:08:00'   interval '15 minutes' < "startTime"   interval '15 minutes')
    AND "status" = 'free'

But the query result includes the row with id 2, which does not match the criteria, because the next slot of user 2 is already booked.

CodePudding user response：

Use the aggregate function bool_and() as a window function to find users with a status other than free.

select 
    "id", "startTime", "userId"
from (
    select
        "id", "startTime", "userId",
        bool_and(status = 'free') over (partition by "userId") as not_booked
    from
        "BookingSlot"
    where 
        "startTime" >= timestamp '2022-01-01T01:08:00' - interval '15 min' and 
        "startTime" <= timestamp '2022-01-01T01:08:00'   interval '15 min'
    ) s
where not_booked;

Test it in db<>fidlle.

CodePudding user response：

You just have to filter on status = 'free and startDate in the right range, and then to select only the userId with 2 rows filtered using the clause HAVING count(*) = 2 :

SELECT *
 FROM "BookingSlot"
WHERE status = 'free'
  AND startDate > '2022-01-01T01:08:00' :: timestamp - interval '15 minutes'
  AND startDate <= '2022-01-01T01:08:00' :: timestamp   interval '15 minutes'
GROUP BY userId
HAVING count(*) = 2