Group over dynamic date ranges-CodePudding

I have a table with IDs, dates and values. I want to always merge the records based on the ID that are within a 90 day window. In the example below, these are the rows marked in the same color.

The end result should look like this:

The entry with the RowId 1 opens the 90 days window for the ID 133741. RowId 2 and 3 are in this window and should therefore be aggregated together with RowId 1.

RowId 4 would be in a 90 day window with 2 and 3, but since it is outside the window for 1, it should no longer be aggregated with them but should be considered as the start of a new 90 day window. Since there are no other entries in this window, it remains as a single line.

The date for line 5 is clearly outside the 90 day window of the other entries and is therefore also aggregated individually. Just like line 6, since this is a different ID.

Below some example code:


create table #Table(RowId int, ID nvarchar(255) , Date date, Amount numeric(19,1));
insert into #Table values


('1','133742',  '2021-07-30',   '1.00'  ),
('2','133742',  '2021-08-05',   '3.00'  ),
('3','133742',  '2021-08-25',   '10.00' ),
('4','133742',  '2021-11-01',   '7.00'  ),
('5','133742',  '2022-08-25',   '11.00' ),
('6','133769',  '2021-11-13',   '9.00'  );

I tried with windowfunctions and CTEs but I could'nt find a way to include all my requirements

CodePudding user response：

With the window function first_value() over() we calculate the distance in days divided by 90 to get the derived Grp

Example

with cte as (
Select *
      ,Grp = datediff(day,first_value(Date) over (partition by id order by date) ,date) / 90
 from #Table
 )
 Select ID 
       ,Date = min(date)
       ,Amount = sum(Amount)
 From  cte
 Group By ID,Grp
 Order by ID,min(date)

Results

ID      Date        Amount
133742  2021-07-30  14.0
133742  2021-11-01  7.0
133742  2022-08-25  11.0
133769  2021-11-13  9.0