CODEWITHSUNDEEP
sqlsql-servert-sql
VoonArt
VoonArt

Reputation: 904

MSSQL Recursive dateadd

Hello Iv got big trouble with one query whith next DateTo is counted from previous record.

I have one table

create table #t1 (
M_ID int, --ID of group
STEP int, -- Step number
DateTo Datetime, --DateTo
AddDays int) --Number of days to add to NEXT record

--TestData

INSERT INTO #t1
select 1, 1, GETDATE(), 0 union
select 1, 2, null, 1 union
select 1, 3, null, 0 union
select 1, 4, null, 0 union
select 2, 1, GETDATE(), 0 union
select 2, 2, NULL, 1 union
select 2, 3, NULL, 0

How table looks.

enter image description here Logic:

If step = 1 then DateTo = GETDATE()
At step 2 of M_ID 1 previous row had 0 days to add so it should copy DateTo from previous row
At step 3 of M_ID 1 previous row has 1 day to add to previous DateTo

Im on the end of my rope...

FAQ Have to be done in T-SQL (8KK records+)

Upvotes: 0

Views: 337

Answers (1)

GriGrim
GriGrim

Reputation: 2921

;with cte as (
    select M_ID, STEP, DateTo, AddDays
    from #t1
    where STEP = 1
    union all
    select t.M_ID, t.STEP, dateadd(dd, c.AddDays, c.DateTo), t.AddDays
    from #t1 t
    inner join cte c on t.M_ID = c.M_ID and t.STEP = c.STEP + 1
)

select *
from cte

Upvotes: 2

Related Questions