MySQL – How to Find Max Sum of Daily Total Within Date Range

MySQL

MYSQL 5.7 question (NOT spreadsheet question) I've been stumped on this for a week (spreadsheet below is just for illustration purposes)

Orders are entered per customer within date range

  • order_number, quantity, start_date, end_date

  • 01, 100, 2021-01-14, 2021-01-16

  • 02, 125, 2021-01-14, 2021-01-16

  • 03, 200, 2021-01-17, 2021-01-23

  • 04, 050, 2021-01-14, 2021-01-19

  • …

  • 10, 150, 2021-01-21, 2021-01-16

I need to find the MAX within an entered date range as illustrated in the table below.
ie. I enter variable start_date_search (2021-01-14) and end_date_search (2021-01-23), and I come up with the MAX on any given day within the range (675 in this example below.)

I don't need to output the totals for each day, but that would be cool too.

Schema (MySQL v5.7)

CREATE TABLE timetable (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
quantity INT(6) NOT NULL,
start_dt DATETIME,
end_dt DATETIME
);

INSERT INTO timetable (quantity, start_dt, end_dt)
VALUES (100, '2021-01-14 10:00:00', '2021-01-16 10:00:00'),
(125, '2021-01-14 10:00:00', '2021-01-16 10:00:00'),
(200, '2021-01-17 10:00:00', '2021-01-23 10:00:00'),
(50, '2021-01-14 10:00:00', '2021-01-19 10:00:00'),
(100, '2021-01-21 10:00:00', '2021-01-23 10:00:00'),
(75, '2021-01-16 10:00:00', '2021-01-21 10:00:00'),
(100, '2021-01-16 10:00:00', '2021-01-19 10:00:00'),
(200, '2021-01-16 10:00:00', '2021-01-19 10:00:00'),
(50, '2021-01-18 10:00:00', '2021-01-19 10:00:00'),
(150, '2021-01-21 10:00:00', '2021-01-23 10:00:00')

data table of reservations by date

Best Answer

You will need a calendar table to distribute the quantity to each date in the interval:

CREATE TABLE calendar (dt date not null primary key);
INSERT INTO calendar (dt)
VALUES ('2021-01-14'),('2021-01-15'),('2021-01-16'),('2021-01-17')
     , ('2021-01-18'),('2021-01-19'),('2021-01-20'),('2021-01-21');

Now you can use a join for that:

SELECT MAX(qt)
FROM (
    SELECT dt, SUM(quantity) as qt
    FROM timetable t
    JOIN calendar c
        ON c.dt BETWEEN date(t.dt_start) AND date(t.dt_end)
    WHERE c.dt BETWEEN '2021-01-14' AND '2021-01-23'
    GROUP BY dt
) tt;

A calendar table is very useful for many tasks so I suggest you create one and populate it. Many DBMS including newer versions of MySQL support recursive CTE:s which can be used instead.

Getting the totals for each dt:

SELECT dt, SUM(quantity) as qt
FROM timetable t
JOIN calendar c
    ON c.dt BETWEEN t.dt_start AND t.dt_end
WHERE c.dt BETWEEN '2021-01-14' AND '2021-01-23'
GROUP BY dt;

Fiddle

Related Question