PARTITION BY with date between 2 date

Question

I work on Azure SQL Database working with SQL Server

In SQL, I try to have a table by day, but the day is not in the table. I explain it by the example below:

TABLE STARTER: (Format Date: YYYY-MM-DD)

Date begin	Date End	Category	Value
2021-01-01	2021-01-03	1	0.2
2021-01-02	2021-01-03	1	0.1
2021-01-01	2021-01-02	2	0.3

For the result, I try to have this TABLE RESULT:

Date	Category	Value
2021-01-01	1	0.2
2021-01-01	2	0.3
2021-01-02	1	0.3 (0.2+0.1)
2021-01-02	2	0.3
2021-01-03	1	0.3 (0.2+0.1)

For each day, I want to sum the value if the day is between the beginning and the end of the date. I need to do that for each category.

In terms of SQL code I try to do something like that:

   SELECT SUM(CAST(value as float)) OVER (PARTITION BY Date begin, Category) as value,
          Date begin, 
          Category, 
          Value
   FROM TABLE STARTER

This code calculates only the value that has the same Date begin but don't consider all date between Date begin and Date End. So in my code, it doesn't calculate the sum of the value for the 02-01-2021 of Category 1 because it doesn't write explicitly. (between 01-01-2021 and 03-01-2021)

Is it possible to do that in SQL?

Thanks so much for your help!

The database is on Azure SQL Database working with SQL Server — Ouakrat, Jun 09 '22 at 11:38
Does `02-01-2021` mean Feb 1st, 2021 or Jan 2nd, 2021? Please use ISO format (YYYY-MM-DD) so it's easy to understand. — The Impaler, Jun 09 '22 at 13:46
I don't quite understand the logic. If the value ends on a certain date, shouldn't that date be `0` — Charlieface, Jun 09 '22 at 15:12
@TheImpaler: I change the format for a better understanding (Thanks for this comment) — Ouakrat, Jun 09 '22 at 15:58
@Charlieface: The logic is we want the value by day and by category. So we need to sum the value for every line the day is include between the "Date begin" and the "Date End" — Ouakrat, Jun 09 '22 at 16:01

score 2 · Accepted Answer · answered Jun 09 '22 at 18:15

You can use a recursive CTE to expand the date ranges into the list of separate days. Then, it's matter of joining and aggregating.

For example:

with
r as (
  select category, 
    min(date_begin) as date_begin, max(date_end) as date_end 
  from starter
  group by category
),
d as (
  select category, date_begin as d from r
 union all
  select d.category, dateadd(day, 1, d.d)
  from d
  join r on r.category = d.category
  where d.d < r.date_end
)
select d.d, d.category, sum(s.value) as value
from d
join starter s on s.category = d.category
              and d.d between s.date_begin and s.date_end
group by d.category, d.d;

Result:

 d           category  value 
 ----------- --------- ----- 
 2021-01-01  1         0.20  
 2021-01-01  2         0.30  
 2021-01-02  1         0.30  
 2021-01-02  2         0.30  
 2021-01-03  1         0.30

See running example at db<>fiddle.

Note: Starting in SQL Server 2022 it seems there is/will be a new GENERATE_SERIES() function that will make this query much shorter.

Thanks you! Exactly what i'm looking for! thanks for fiddle too! :D — Ouakrat, Jun 13 '22 at 07:45

PARTITION BY with date between 2 date

1 Answers1