Find max sum of daily total within date range

Question

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')

Answer 1

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

Answer 2

Using the data from your fiddle (see my own fiddle here) - however, (final edit), see the fiddle based on the image in the question here - explained in the 2nd edit below):

--
-- Works in 5.7
-- 

SELECT SUM(quantity) AS the_sum,
CASE
  WHEN (dt_start >= '2021-01-14' AND dt_start < '2021-01-16') THEN 'per_1'
  WHEN (dt_start >= '2021-01-16' AND dt_start < '2021-01-19') THEN 'per_2'
  ELSE 'per_3'
END AS period
FROM timetable
GROUP BY
  CASE
    WHEN (dt_start >= '2021-01-14' AND dt_start < '2021-01-16') THEN 'per_1'
    WHEN (dt_start >= '2021-01-16' AND dt_start < '2021-01-19') THEN 'per_2'
    ELSE 'per_3'
  END
ORDER BY 
  the_sum DESC;

Result:

the_sum period
    625  per_2
    275  per_1
    250  per_3

Now, because you don't have window functions, you have to rejoin back to that (pretty horrible) construct above to get the 625, per_2 record. A good reason to upgrade to version 8!

Obviously, you can vary the period dates to suit your requirements - MySQL has WEEK, MONTH and QUARTER functions which could come in useful!

But with 8, you can do:

-- Doesn't work in 5.7 - but works in 8

SELECT 
  the_sum, period
FROM
(
  SELECT 
    the_sum,
    period,
    ROW_NUMBER() OVER (ORDER BY the_sum DESC) AS rn 
  FROM
  (
    SELECT SUM(quantity) AS the_sum,
      CASE
        WHEN (dt_start >= '2021-01-14' AND dt_start < '2021-01-16') THEN 'per_1'
        WHEN (dt_start >= '2021-01-16' AND dt_start < '2021-01-19') THEN 'per_2'
      ELSE 'per_3'
    END AS period
    FROM timetable
    GROUP BY
    CASE
      WHEN (dt_start >= '2021-01-14' AND dt_start < '2021-01-16') THEN 'per_1'
      WHEN (dt_start >= '2021-01-16' AND dt_start < '2021-01-19') THEN 'per_2'
      ELSE 'per_3'
    END
  ORDER BY 
    CASE
      WHEN (dt_start >= '2021-01-14' AND dt_start < '2021-01-16') THEN 'per_1'
      WHEN (dt_start >= '2021-01-16' AND dt_start < '2021-01-19') THEN 'per_2'
      ELSE 'per_3'
    END,
    period
  ) AS t1
) AS t2
WHERE rn = 1;

Result:

the_sum   period
    625    per_2

You have to be careful when using window functions with stuff like this - if you have equal maxima, you could get erratic results - look at RANK and DENSE_RANK window functions for this also.

EDIT (following discussion re. calendar tables see comment after @Lennart's answer)

What you want may be something like this - (see bottom of the fiddle here)?

SELECT
  c.dt AS "The date",
  COALESCE(t1.the_sum, 0) AS "Sum from"
FROM calendar c
LEFT JOIN
(
  SELECT
    SUM(t.quantity) AS the_sum,
    t.dt_start AS dat
  FROM timetable t
  GROUP BY dt_start
) AS t1
ON  c.dt = CAST(t1.dat AS DATE)
ORDER BY c.dt;

Result (snipped for brevity):

 The date:   Sum From:
2021-01-12           0
2021-01-13           0
2021-01-14         275
2021-01-15           0
2021-01-16         375
2021-01-17         200
2021-01-18          50
2021-01-19           0
...
...

Or could you clarify? I've kept some of my experiments on the fiddle - might give you some ideas?

Edit (2nd - Again!):

Using your own data in the image from the question, what you can do is something like this (see final fiddle here):

CREATE TABLE timetable 
(
  id          INT NOT NULL PRIMARY KEY,
  order_no    SMALLINT NOT NULL,
  delivery_no INT NOT NULL,    -- maybe completed_no or batch_no? Whatever!
  quantity    SMALLINT NOT NULL,
  dt_start    DATETIME
);

And populate, as per the data in the image (first 4 orders only - I leave it to the OP/reader to fill in the other orders):

INSERT INTO timetable 
(id, order_no, delivery_no, quantity, dt_start)

VALUES 
(1, 1, 1, 100, '2021-01-14 10:00:00'),
(2, 1, 2, 100, '2021-01-15 10:00:00'),
(3, 1, 3, 100, '2021-01-16 10:00:00'),

(4, 2, 1, 125, '2021-01-14 10:00:00'),
(5, 2, 2, 125, '2021-01-15 10:00:00'),
(6, 2, 3, 125, '2021-01-16 10:00:00'),

( 7, 3, 1, 200, '2021-01-17 10:00:00'),
( 8, 3, 2, 200, '2021-01-18 10:00:00'),
( 9, 3, 3, 200, '2021-01-19 10:00:00'),
(10, 3, 4, 200, '2021-01-20 10:00:00'),
(11, 3, 5, 200, '2021-01-21 10:00:00'),
(12, 3, 6, 200, '2021-01-22 10:00:00'),
(13, 3, 7, 200, '2021-01-23 10:00:00'),

(14, 4, 1, 50, '2021-01-14 10:00:00'),
(15, 4, 2, 50, '2021-01-15 10:00:00'),
(16, 4, 3, 50, '2021-01-16 10:00:00'),
(17, 4, 4, 50, '2021-01-17 10:00:00'),
(18, 4, 5, 50, '2021-01-18 10:00:00'),
(19, 4, 6, 50, '2021-01-19 10:00:00');

Then (using MySQL 5.7 which is apparently your version), you can do the following:

--
-- Works in 5.7
--
SELECT
  t1.order_no AS "Order No.",
  t1.dt_start AS "Start Date",
  t2.dt_start AS "End Date",
  ROUND(SUM(t1.quantity), 0) AS "Total/order",
  ROUND(AVG(t1.quantity), 0) AS "Avg/day/order",
  (
    SELECT COUNT(z.order_no) 
    FROM timetable z WHERE z.order_no = t1.order_no GROUP BY order_no ORDER By order_no
  ) AS "No. of deliveries",  
  (SELECT SUM(a.quantity) FROM timetable a) AS "Tot sales"
FROM 
  timetable t1
  JOIN timetable t2
  ON   t1.dt_start = (
                       SELECT MIN(x.dt_start) 
                       FROM timetable x 
                       WHERE x.order_no = t1.order_no
                       GROUP BY x.order_no
                     )
  AND  t2.dt_start = (
                       SELECT MAX(y.dt_start) 
                       FROM timetable y 
                       WHERE y.order_no = t2.order_no
                       GROUP BY y.order_no
                     )
  AND t1.order_no = t2.order_no
GROUP BY t1.order_no, t1.dt_start, t2.dt_start
ORDER BY t1.order_no, t1.dt_start, t2.dt_start;

Result:

Order No.   Start Date  End Date    Total/order Avg/day/order   No. of deliveries   Tot sales
1   2021-01-14 10:00:00 2021-01-16 10:00:00 100 100 3   2375
2   2021-01-14 10:00:00 2021-01-16 10:00:00 125 125 3   2375
3   2021-01-17 10:00:00 2021-01-23 10:00:00 200 200 7   2375
4   2021-01-14 10:00:00 2021-01-19 10:00:00 50  50  6   2375

At the bottom of the (3rd) fiddle, I've used the calendar table proposed by @Lennart.

If you upgrade to version 8, you have many more features - esp. for you, window functions, as mentioned earlier, you'll have a few hoops to jump through. Ask another question on any future issues - and please have the fiddle correspond to a) your version of MySQL (use dbfiddle.uk) and b) ensure that the table(s) and data and the desired result are consistent (see my comment under @Lennart's answer re. your fiddle here).