How to get occupation of a month?

I have a reservation system but now I want to calculate what the occupation of a room in a month is.

But I’m having trouble with getting accurate results because if a reservations start date is at f.e. 28 december and ends on the 3rd of january it will count or 6 days or none at all depending on the query.

It should count just 3 days since only 3 of the 6 days of the reservation are in december

SELECT 
    room_id, 
    SUM(DATEDIFF(end_time, start_time)) as days 
FROM reservations 
WHERE 
    start_time >= '2019-12-01 00:00:00' 
    AND end_time < '2020-01-01 00:00:00' 
GROUP BY room_id

​x
 
SELECT     room_id,     SUM(DATEDIFF(end_time, start_time)) as days FROM reservations WHERE     start_time >= '2019-12-01 00:00:00'     AND end_time < '2020-01-01 00:00:00' GROUP BY room_id​

Answer

Your condition in the WHERE clause is wrong because it will reject reservations where start_date and end_date are not both in the same month:

SELECT 
  room_id, 
  SUM(DATEDIFF(
    LEAST(dropoff_time, '2019-12-31 23:59:59'),
    GREATEST(pickup_time, '2019-12-01 00:00:00')
  ) + 1) as days 
FROM reservations 
WHERE 
  (pickup_time >= '2019-12-01 00:00:00' AND pickup_time < '2020-01-01 00:00:00')
  OR
  (dropoff_time >= '2019-12-01 00:00:00' AND dropoff_time < '2020-01-01 00:00:00')
GROUP BY room_id

 
SELECT   room_id,   SUM(DATEDIFF(    LEAST(dropoff_time, '2019-12-31 23:59:59'),    GREATEST(pickup_time, '2019-12-01 00:00:00')  ) + 1) as days FROM reservations WHERE   (pickup_time >= '2019-12-01 00:00:00' AND pickup_time < '2020-01-01 00:00:00')  OR  (dropoff_time >= '2019-12-01 00:00:00' AND dropoff_time < '2020-01-01 00:00:00')GROUP BY room_id​

Advertisement

Answer