Search code examples
mysqlgroup-bysumhavingdate-arithmetic

SQL counting days from periods

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;

3 examples

Edit 1

2 other options 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

Solution

  • 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.