Counting reservations for each day, where res could span multiple days and should count for each day

Question

I have a table with reservations in it. Each row is a reservation and has a start & end datetime field.

I want to construct a query which gives me the count of reservations on each day in a certain time interval, eg april 2018.

Selecting all the reservations within the given interval is fairly simple:

SELECT * FROM reservation 
WHERE start <= '2018-05-01 00:00:00' 
AND end >= '2018-04-01 00:00:00'

But then the 'trouble' starts.
I want to display a 'count' of reservations on each day in the interval. But a reservation could span multiple days. So grouping them on DAY(start) is not correct.

I don't want to query each day in the interval seperately as this would be very server-intensive.

Is there a way to do this through a MySQL query?

Sample data:

id | start               | end 
2  | 2018-04-01 12:00:00 | 2018-04-03 09:00:00
3  | 2018-04-01 09:00:00 | 2018-04-01 11:00:00
4  | 2018-04-06 13:00:00 | 2018-05-20 09:00:00

Result for 2018-04-01 to 2018-04-06:

2018-04-01 | 2   (2/3)
2018-04-02 | 1   (2)
2018-04-03 | 1   (2)
2018-04-04 | 0
2018-04-05 | 0 
2018-04-06 | 1   (4)

in a sqlfiddle: http://sqlfiddle.com/#!9/e62ffa/2/0

Answer 1

First we will reuse the answer from DBA StackExchange. (You can use the accepted answer if you want, you would just need to create a dedicated table for that).

We will just modify the query a bit by using the condition that you need.

Your condition:

SELECT * FROM reservation 
WHERE start <= '2018-05-01 00:00:00' 
AND end >= '2018-04-01 00:00:00'

Modified answer from DBA Stackexchange:

SELECT date_field
FROM
(
    SELECT
        MAKEDATE(YEAR(NOW()),1) +
        INTERVAL (MONTH(NOW())-1) MONTH +
        INTERVAL daynum DAY date_field
    FROM
    (
        SELECT t * 10 + u daynum
        FROM
            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
            (SELECT 0 u 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) B
        ORDER BY daynum
    ) AA
) AAA
/*WHERE MONTH(date_field) = MONTH(NOW())*/
WHERE date_field BETWEEN '2018-04-01' AND '2018-05-01'

Take note that I only changed the WHERE Clause.

Now using that query as a DERIVED TABLE, we will include your Reservations table using LEFT JOIN.

 SELECT D.date_field
  , COUNT(R.Id)
  FROM (
    /* The query from above goes here */
  ) D
  LEFT JOIN Reservations R ON D.date_field BETWEEN DATE(R.StartDate) AND DATE(R.EndDate)
  GROUP BY D.date_field

Notice again that we used the DATE function to truncate the TIME part of our StartDate and EndDate because for example, 2018-04-01 denotes the whole day and it cannot be in between 2018-04-01 09:00:00 and 2018-04-01 11:00:00 for some under the hood reason I am not completely familiar of.

Here is a SQL Fiddle Demo of the result.

If someone could help me on this one. SELECT '2018-04-02' BETWEEN '2018-04-01 23:59:59' AND '2018-04-02 00:00:00' will result to 1 (TRUE). It seems that by default DATE will have a TIMESTAMP of 00:00:00.

Update for More Flexible Date Range (2018-04-11)

The query above from DBA StackExchange only lists down the days of the current month. I tried to search a bit and found this another good answer here in StackOverflow. Here is a part of the query:

SELECT CURDATE() - INTERVAL (A.A+ (10 * B.A)) DAY AS Date
    FROM (
        SELECT 0 AS A 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
    ) AS A
    CROSS JOIN (
        SELECT 0 AS A 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
    ) AS B

The query above will generate numbers (1 to 100) using CROSS JOIN and then subtracting it to the Current Date, then you will have dates from now up to 100 days back. You can add another CROSS JOIN of numbers to generate 1000 numbers if necessary.

I assume you will have StartDate and EndDate in your stored procedure or somewhere. We can replace the CURDATE with EndDate and then we will have 100 days back up to our EndDate. We will just add a WHERE clause to filter only the dates that we need using subquery/derived table.

SELECT D.Date
    FROM (
        SELECT CURDATE() - INTERVAL (A.A+ (10 * B.A)) DAY AS Date
            FROM (
                SELECT 0 AS A 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
            ) AS A
            CROSS JOIN (
                SELECT 0 AS A 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
            ) AS B
    ) AS D
    WHERE D.Date BETWEEN @startDate AND @endDate

We can now use LEFT JOIN to include the Reservations table.

Here is another SQL Fiddle Demo for that. This also includes the Start and End Date variables, and a sample date range spanning from a previous year to the current year.

Again if you need more than 100 days of range, we will just need to add another CROSS JOIN of numbers, let's name that as C:

CROSS JOIN (
    SELECT 0 AS A 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
) AS C

And then add it to the calculation of past days in the SELECT statement.

SELECT CURDATE() - INTERVAL (A.A + (10 * B.A) + (100 * C.A)) DAY AS Date