Generating rows on days between multiple dates, counting days between each, and the first and last

Question

I need to generate rows in between multiple events, counting how many days since one event and the next one, and also between the 'InitialEvent' and the 'ClosingEvent'

This would show how long it was since that event took was the most recent event until the next one came. See table below:

PersonID	EventName	DateID	DaysSince_InitialEvent	DaysSince_CurrentEvent	DimensionID	AnotherDimensionID	etc
1	InitialEvent	20210501	-	-	-	-
1	AnEvent1	20210505	-	-	-	-	-
1	AnEvent4	20210508	-	-	-	-	-
1	AnEvent2	20210511	-	-	-	-	-
1	ClosingEvent	20210513	-	-	-	-	-
2	InitialEvent	20210510	-	-	-	-	-
2	AnEvent1	20210513	-	-	-	-	-
2	ClosingEvent	20210510	-	-	-	-	-

I need it to output something like the table below, which will show how long each event has been in place before another took place:

PersonID	EventName	DateID	DaysSince_InitialEvent	DaysSince_CurrentEvent	DimensionID	AnotherDimensionID
1	InitialEvent	20210501	0	0	-	-
1	InitialEvent	20210502	1	1	-	-
1	InitialEvent	20210503	2	2	-	-
1	InitialEvent	20210504	3	3	-	-
1	AnEvent1	20210505	4	0	-	-
1	AnEvent1	20210506	5	1	-	-
1	AnEvent1	20210507	6	2	-	-
1	AnEvent1	20210508	7	3	-	-
1	AnEvent1	20210509	8	4	-	-
1	AnEvent1	20210510	9	5	-	-
1	AnEvent2	20210511	10	0	-	-
1	AnEvent2	20210512	11	1	-	-
1	ClosingEvent	20210513	12	0	-	-
2	InitialEvent	20210510	0	0	-	-
2	InitialEvent	20210511	1	1	-	-
2	InitialEvent	20210512	2	2	-	-
2	AnEvent1	20210513	3	0	-	-
2	AnEvent1	20210514	4	1	-	-
2	ClosingEvent	20210515	5	0	-	-

There are thousands of PersonID, and probably 20 - 30 events each. All can happen concurrently. There are also 10 or so dimensions, but these remain static.

For example, I'd subsequently need to see that on 20210610 (2021-06-10), there were two active events:

PersonID 1, AnEvent1, 9 days since initial event, 5 days in the current event.
PersonID 2, InitialEvent, 0 days since initial event, 0 days in the current event.

I've started creating a CTE, and a cross joined table on a dim.date table, but get completely lost.

Can anyone suggest a solution?

I hope the issue is clear and easy to understand.

A similar issue is listed here

Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. Shall we guess that `DateID` is an `int`? Is there a `date` anywhere to be found? If only DDL could be declassified ... . — HABO, Jun 29 '21 at 00:28

score 1 · Answer 1 · answered Jun 29 '21 at 00:06

One method is a recursive CTE:

with cte as (
      select personId, eventName, dateId, 0 as dayssincecurrentevent,
             dateadd(day, -1, lead(dateid) over (partition by personid order by dateid)) as end_dateid
      from t
      union all
      select personId, eventName, dateadd(day, 1, dateid) ,
             dayssincecurrentevent + 1,
             end_dateid
      from cte
      where dateid < end_dateid
     )
select personId, eventName, dateid, dayssincecurrentevent,
       datediff(day, min(dateid) over (partition by personid), dateid) as dayssinceinitialevent
from cte
order by personid, dateid;

Here is a db<>fiddle.

Note: If one date might have a period of over 100 days, you need to add option (maxrecursion 0) to the query.

Generating rows on days between multiple dates, counting days between each, and the first and last

1 Answers1