SQL counting days from periods

June 13, 2022

My problem is that I want to sum periods of date from only may, but as you can see below some of events starts before first day of may and some end after last may day.

There is my code:

SELECT * FROM rooms p, bookings r WHERE p.id_room = r.id_room group by 
r.id_room having 
case
    WHEN (month(r.start_date) = 5 AND month(r.end_date) = 5) THEN 
    sum(datediff(r.end_date, r.start_date)) < 21
    WHEN (month(r.start_date) < 5 AND month(r.end_date) = 5) THEN 
    sum(datediff(r.end_date, '2022-05-01 12:00:00')) < 21
    WHEN (month(r.start_date) = 5 AND month(r.end_date) > 5) THEN 
    sum(datediff('2022-05-31 12:00:00', r.start_date)) < 21
END;

>Solution :

Simplify the HAVING clause by using the functions LEAST() and GREATEST():

SELECT r.id_room  
FROM rooms r INNER JOIN bookings b 
ON b.id_room = r.id_room 
WHERE b.end_date > '2022-05-01 12:00:00' AND b.start_date < '2022-05-31 12:00:00'
GROUP BY r.id_room 
HAVING SUM(DATEDIFF(
             LEAST(b.end_date, '2022-05-31 12:00:00'),
             GREATEST(b.start_date, '2022-05-01 12:00:00')
       )) < 21;

Also, use a proper join.