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:

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

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

CodePudding user response：

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:

CodePudding user response：

emm, you can just group by from_date and person. like

select from_date, 
person,
sum(tax) as tax
from table
group by from_date, person

CodePudding user response：

May be not best solution. But below query should work

Declare @FromDate date,@ToDate date;

select @FromDate=min(date_from) from YourTable

select @ToDate=max(date_to) from YourTable

Create table #temp ([Date] date,person varchar(50), tax bigint)

WHILE (@FromDate <= @ToDate)
BEGIN

insert into #temp
select 
@FromDate,
person,
(select Sum(tax) from YourTable where @FromDate between date_from AND date_to)
from YourTable where @FromDate between date_from AND date_to

group by person


set @FromDate=DATEADD(DAY,1,@FromDate)

END


select * from #temp

drop table #temp

CodePudding user response：

Here is an approach that doesn't create all days for all persons (i.e. several rows per row in the table). It merely creates all days, then aggregates the table twice, once to get the amounts that get added a day and once those that get subtracted. Then it's outer joining these sums to the days and get the running total.

This is based on Salman A's idea hence to work with a running total.

with days (day, max_day) as
(
  select min(date_from), max(date_to) from my_table
  union all
  select dateadd(day, 1, day), max_day from days
  where day < max_day
)
, starters (day, total) as
(
  select date_from, sum(tax) from my_table group by date_from
)
, enders (day, total) as
(
  select date_to, sum(tax) from my_table group by date_to
)
select
  days.day,
  sum(coalesce(starters.total, 0) - coalesce(enders.total, 0))
    over (order by days.day) as tax
from days
left join starters on starters.day = days.day
left join enders on dateadd(day, 1, enders.day) = days.day
order by days.day;

If you want to limit this to a date range then:

select *
from (above select)
where day >= @date_from and day < @date_to
order by day

Demo with slighty changed sample data to have date gaps in the data: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4d64c881b3026abb5f5ab23aa09fc259