Cumulating dates in SQL

Question

I have this table with this sample data:

DECLARE @Sample TABLE (fill_date DATETIME, days_supplied INT)

INSERT INTO @Sample (fill_date, days_supplied)
VALUES (    
    '02/17/2005', --> DATEADD(dd, 500, '02/17/2005') = '07/02/2006'
    500
    ),
    (   
    '06/13/2005', --> DATEADD(dd, 30, '07/02/2006') = '08/01/2006'
    30
    ),
    (   
    '08/11/2005', --> DATEADD(dd, 30, '08/01/2006') = '08/31/2006'
    30
    )

I need to add days_supplied to fill_date in the first row, which would be 2006-07-02. If the result is higher than fill_date in the next row i would need to add days_supplies of the next row to the sum of the days_supplied and fill_date of the previous row. If the result of the first row isnt higher than fill_date of the next row then i would need to add days_supplied and fill_date of the next row, and so on.

This is the result i need in the end:

last_fill_date: '08/31/2006'.

What would be the best way to achieve this? Any help is appreciated

What version of SQL Server is used? – Vitaly Borisov Feb 12 '19 at 00:30 — Vitaly Borisov, Feb 12 '19 at 00:30

score 3 · Accepted Answer · answered Feb 12 '19 at 00:33

For this type of logic, you need to use a recursive CTE -- alas:

with s as (
      select s.*, row_number() over (order by fill_date) as seqnum
      from sample s
     ),
     cte as (
      select fill_date, days_supplied, dateadd(day, days_supplied, fill_date) as end_date, seqnum
      from s
      where seqnum = 1
      union all
      select (case when s.fill_date > cte.end_date then s.fill_date else cte.end_date end),
             s.days_supplied,
             dateadd(day, s.days_supplied, (case when s.fill_date > cte.end_date then s.fill_date else cte.end_date end)),
             s.seqnum
      from cte join
           s
           on s.seqnum = cte.seqnum + 1
     )
select max(end_date)
from cte;

Here is a db<>fiddle.

Note that there is a slight ambiguity to the question. I think the rows are ordered by fill_date, which is what this answer assumes.

Yes you're right the rows would be ordered by fill_date. Thanks! — joey0xx, Feb 12 '19 at 01:02

score 2 · Answer 2 · answered Feb 12 '19 at 00:33

2

Using the window functions

Example

Select *
      ,NewValue = DateAdd(DAY,sum(days_supplied) over (Order By Fill_date),min(fill_date) over())
 From  @Sample

Returns

fill_date               days_supplied   NewValue
2005-02-17 00:00:00.000 500             2006-07-02 00:00:00.000
2005-06-13 00:00:00.000 30              2006-08-01 00:00:00.000
2005-08-11 00:00:00.000 30              2006-08-31 00:00:00.000

EDIT - If you want just the final Record

Select Top 1 
       *
      ,NewValue = DateAdd(DAY,sum(days_supplied) over (Order By Fill_date),min(fill_date) over())
 From  @Sample
 Order By NewValue desc

answered Feb 12 '19 at 00:33

John Cappelletti

79,615
7
44
66

This works with the provided sample, but it doesnt work in the scenario where the date result of the first row isnt higher than the fill_date of the next row – joey0xx Feb 12 '19 at 00:47
@joey0xx I see that Gordon had it covered :) – John Cappelletti Feb 12 '19 at 00:50

Cumulating dates in SQL

2 Answers2