0

I have data that looks like this:

name      |sides|start     |end       
----------+-----+----------+----------
STR00248  |2    |2021-01-04|          
STR00250  |1    |2021-01-04|          
STR00246  |1    |2021-03-29|2021-09-12
STR00247  |1    |2021-03-29|2021-04-25
STR00248  |2    |2021-03-29|2021-09-12
STR00247  |1    |2021-03-29|2021-09-12
STR00248  |2    |2021-05-11|2021-08-10
STR00250  |1    |2021-05-24|2021-11-07
STR00250  |1    |2021-07-01|          
STR00250  |1    |2021-07-19|2022-06-19

I want to get the sum of sides for STR00250 grouped by unique combinations of start and end. If end is NULL, it should be considered not to end. So the result should look something like this:

sum
---
1 (2021-01-04 - 2021-05-23)
2 (2021-05-24 - 2021-06-30)
3 (2021-07-01 - 2021-07-18)
4 (2021-07-19 - 2022-06-18)
3 (2022-06-19 - 2021-11-07)
2 (2021-11-08 - forward)

I don't need the dates, just the sums, although it would be nice to have the dates for error message purposes (in which case they should be in a separate column). Unfortunately browsing StackExchange hasn't triggered any ideas on how to accomplish my goal.

Here is the table structure and some sample data:

CREATE TABLE sample (
`name` VARCHAR(140),
`sides` INT(11),
`start` DATE,
`end` DATE
);
INSERT INTO sample VALUES
('STR00248', 2, '2021-01-04', NULL),
('STR00250', 1, '2021-01-04', NULL),
('STR00246', 1, '2021-03-29', '2021-09-12'),
('STR00247', 1, '2021-03-29', '2021-04-25'),
('STR00248', 2, '2021-03-29', '2021-09-12'),
('STR00247', 1, '2021-03-29', '2021-09-12'),
('STR00248', 2, '2021-05-11', '2021-08-10'),
('STR00250', 1, '2021-05-24', '2021-11-07'),
('STR00250', 1, '2021-07-01', NULL),
('STR00250', 1, '2021-07-19', '2022-06-19');
5
  • For end dates that are NULL, is that equivalent to 9999-12-31? (i.e. the syntactic end of time) or is it inferred to be the day before the next start date or something?
    – Hannah Vernon
    Commented Jul 7, 2021 at 22:05
  • It is equivalent to 9999-12-31, yes; i.e. there is no end date.
    – jobu1342
    Commented Jul 7, 2021 at 22:06
  • I really don;t understand where the expected results come from. STR00250 only has 4 rows which sum up to 4, and most of your end dates don't exist in the table. 2022-06-19 - 2021-11-07 doesn't make sense as start and end dates, and also aren't in the table. Please explain the logic you are trying to achieve Commented Jul 8, 2021 at 2:30
  • @Charlieface I'm looking for SUM(sides) based on timespans between the first start and the last end. Each row of the result is defined by unique SUM(sides) + timespan combinations in order of time.
    – jobu1342
    Commented Jul 20, 2021 at 0:00
  • That doesn't seem to be your expected results: for example how does 2022-06-19 - 2021-11-07 make sense, and how does 2022-06-19 become a start and not an end? You also say "grouped by unique combinations of start and end" what does unique combinations mean here? Please detail the logic, it's really unclear Commented Jul 20, 2021 at 0:10

1 Answer 1

0

Check this:

WITH 
-- gathering all mentioned dates with ther weights
cte1 AS ( SELECT name, sides, start dt, 1 weight
          FROM sample
          UNION ALL
          SELECT name, sides, `end`, -1
          FROM sample ),
-- calculating the cumulative sum for each date
cte2 AS ( SELECT DISTINCT
                 name, 
                 dt, 
                 SUM(sides * weight) OVER (PARTITION BY name ORDER BY dt IS NULL, dt) cum
          FROM cte1 )
-- add period end date, remove excess (NULL) rows
SELECT name, 
       dt start,
       LEAD(dt) OVER (PARTITION BY name ORDER BY dt IS NULL, dt) - INTERVAL 1 DAY `end`,
       cum `sum`
FROM cte2
HAVING start IS NOT NULL
ORDER BY name, dt IS NULL, dt;

If you need the info for separate name then add according WHERE to each subquery in cte1.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=46a3202548714c17dc553b43d1fa5ccc

1
  • This is what I was looking for, but if I have to know the names before hand, it defeats the purpose.
    – jobu1342
    Commented Jul 16, 2021 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.