CODEWITHSUNDEEP
sqlmariadb
Carsten
Carsten

Reputation: 33

Iterate between date range in SQL

I have data that looks as follows:

start_date | end_date   | value
-----------------------------
2022-01-01   2022-01-05   10
2022-01-01   2022-01-04   5
2022-01-03   2022-01-06   100

I want to sum the values of rows, that are started, but not yet ended. For a single date I can write

SELECT SUM(value) FROM `xxx` WHERE start_date <= '2022-01-04' AND end_date > '2022-01-04');

to get all "open" rows on Jan 4th (110).

What's the easiest way to get these values for every day between two dates in SQL in a single query? It's a MariaDB server.

Ideally the result would look like this:

date       | open_values
------------------------
2022-01-01   15
2022-01-02   15
2022-01-03   115
2022-01-04   110
2022-01-05   100
2022-01-06   0

Thank you for your help!

Upvotes: 0

Views: 451

Answers (1)

SOS
SOS

Reputation: 6550

Use a calendar table to return the individual dates. JOIN to it and use a conditional SUM to calculate the total value, per day.

SELECT c.CalendarDate
       , SUM( IF(t.End_Date > c.CalendarDate, t.value, 0) ) AS TotalValue
FROM   CalendarTable c INNER JOIN YourTable t 
            ON t.Start_Date <= c.CalendarDate
            AND t.End_Date >= c.CalendarDate
GROUP BY c.CalendarDate 
ORDER BY c.CalendarDate
;

Results:

CalendarDate | TotalValue
:----------- | ---------:
2022-01-01   |         15
2022-01-02   |         15
2022-01-03   |        115
2022-01-04   |        110
2022-01-05   |        100
2022-01-06   |          0

Technically, you could also use a CTE. However, a calendar table will be more efficient.

-- Demo: Generate 1 year's worth of dates
WITH recursive CalendarCTE AS (
   SELECT '2022-01-01' AS CalendarDate
   UNION ALL
   SELECT DATE_ADD(CalendarDate, INTERVAL 1 DAY)
   FROM   CalendarCTE
   WHERE  CalendarDate < '2023-01-01'
)
-- INSERT INTO CalendarTable (CalendarDate)
SELECT c.CalendarDate
       , SUM( IF(t.End_Date > c.CalendarDate, t.value, 0) ) AS TotalValue
FROM   CalendarCTE c INNER JOIN YourTable t 
            ON t.Start_Date <= c.CalendarDate
            AND t.End_Date >= c.CalendarDate
GROUP BY c.CalendarDate 
ORDER BY c.CalendarDate
;

db<>fiddle here

Upvotes: 1

Related Questions