SQL counting days from periods-CodePudding

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;

Edit 1

I will only talk about example on bottom. E.g. date_Start - June 3 date_end - June 15

GREATEST(date_start, '2022-05-1') returns June 3 LEAST(date_end, '2022-05-31') retruns may 31

DATEDIFF(date_end, date_start) returns -3 and it is still counted as day from may while it should be skipped

CodePudding user response：

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

SELECT r.id_room  
FROM rooms r LEFT JOIN bookings b 
ON b.id_room = r.id_room 
AND 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(COALESCE(DATEDIFF(
             LEAST(b.end_date, '2022-05-31 12:00:00'),
             GREATEST(b.start_date, '2022-05-01 12:00:00')
       ), 0)) < 21;

Also, use a proper join.