Get value for every date in table with date ranges

I’m working with table, its structure looks like this:

• from_date DATE %Y-%m-%d
• to_date DATE %Y-%m-%d
• person VARCHAR
• tax INT

Table shows that in period between date_from and date_to for such person there was such tax

The whole point is that these periods can intersect and activities can sum. Roughly speaking, here is an example of a table:

date_from	date_to	person	tax
2020-01-30	2020-02-02	person1	20
2020-01-30	2020-01-31	person1	10
2020-02-01	2020-02-03	person1	15

And I need a table with structure:

• date DATE %Y-%m-%d
• person VARCHAR
• tax INT

where I get tax aggregated for every date for every person and it will be look like this

date	person	tax
2020-01-30	person1	30
2020-01-31	person1	30
2020-02-01	person1	35
2020-02-02	person1	35
2020-02-03	person1	15

Hope I was able to explain in an understandable way what I want.

Below the code I tried. But recursive approach does not solve the problem because the table has about 5 millions rows

WITH cte AS (
 SELECT person, date_from, date_to, tax, date_from AS report_date
 FROM my_table
 WHERE date_from >= @date_from AND date_to < @date_to

 UNION ALL

 SELECT person, date_from, date_to, tax, DATEADD(DAY, 1, report_date) AS report_date
 FROM cte
 WHERE report_date < date_to
)

SELECT person, report_date, SUM(tax) tax
FROM cte
GROUP BY person, report_date

Answer

You need a list of dates consisting of all start date, end date and end date + 1 in your table. Then calculate running sum such that:

• On start date the tax is added to sum
• On end date the tax remains unchanged
• On end date + 1 the tax is removed from sum

A group by is needed to compact rows having same date into one row.

WITH cte AS (
    SELECT person, ca.*
    FROM t
    CROSS APPLY (VALUES
        (date_from, tax),
        (date_to, 0),
        (DATEADD(DAY, 1, date_to), -tax)
    ) AS ca(date, value)
)
SELECT person, date, SUM(SUM(value)) OVER (
    PARTITION BY person
    ORDER BY date
) AS tax_value
FROM cte
GROUP BY person, date
ORDER BY person, date

And the result:

person	date	tax_value
person1	2020-01-30	30
person1	2020-01-31	30
person1	2020-02-01	35
person1	2020-02-02	35
person1	2020-02-03	15
person1	2020-02-04	0