Getting days extra when querying for room occupancy

Question

I'm getting extra days when querying for room occupancy. I think this is because it still does SUM(DATEDIFF(...) +1) even on days with two reservations. So for example in february it returns 31 days for a room.

How can i add one day except for days with two reservations, then it only has to +1 it for one of the two.

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

Here's a fiddle that represents the unneeded extra day, https://www.db-fiddle.com/f/ta53CprLisnRXh9A8wVwZv/1 it returns 7 days but the room is reserved for 5

This is not a complete question, because no premise or sample data has been given. Please add this information to your question. — Tim Biegeleisen, Feb 02 '20 at 10:46
@TimBiegeleisen I'm sorry https://www.db-fiddle.com/f/ta53CprLisnRXh9A8wVwZv/1 — Ronald McDonald, Feb 02 '20 at 11:12

Sebastian Brosch · Answer 1 · 2020-02-02T11:57:38.447

You can use a calendar table to solve this:

SELECT 
  room_id, 
  COUNT(DISTINCT date_value)
FROM test INNER JOIN (
  SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS date_value
  FROM
    (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
    (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
    (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
    (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
    (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
  ) calendar ON calendar.date_value BETWEEN CAST(start_time AS DATE) AND CAST(end_time AS DATE)
WHERE 
  (start_time >= '2019-02-01 00:00:00' AND start_time < '2019-03-01 00:00:00')
  OR
  (end_time >= '2019-02-01 00:00:00' AND end_time < '2019-03-01 00:00:00')
GROUP BY room_id;

The calendar table is a generated table with all days since 1970-01-01 as date value. You can join your table to the calendar table and count all distinct days matching your table.

demo

What is this magic, how does it work if i may ask? Where does the calendar table come from? — Ronald McDonald, Feb 02 '20 at 11:49
this returns crazy high amount of days on some rooms, weirdly — Ronald McDonald, Feb 02 '20 at 12:22

score 0 · Answer 2 · answered Feb 02 '20 at 13:54

You are adding the + 1 before doing the SUM() rather than afterwards. If you fix the parentheses, then you will fix your problem:

SELECT SUM(DATEDIFF(LEAST(end_time, '2019-03-01'),
                    GREATEST(start_time, '2019-02-01')
                   )
          ) + 1 as days 
FROM test 
WHERE start_time < '2019-03-01' AND
      end_time >= '2019-02-01'
GROUP BY room_id;

Notes about the date arithmetic.

I assume that you want rooms that are reserved for any part of February. Your logic misses reservations that start in January and end in March.

Also, using time components with datediff() is counterintuitive. That function only cares about the date.

Getting days extra when querying for room occupancy

2 Answers2