Returning a chain of events with a recursive CTE
This is a question that cropped up SQLServerCentral.
Imagine you have a list of dates, for which you need to know the next day after a specified interval. |
So in this list you want to return those with an interval of 3Days (ie those in Red)
|
| 01Jan |
| 02Jan |
| 07jan |
| 11Jan |
| 12Jan |
| 28jan |
| |
| To add further complication there are also multiple ranges. Heres some sample data |
drop table #DateSteps
go
Create table #DateSteps
(
Handler integer,
EventDate smalldatetime
)
go
Create unique index idxDateSteps on #DateSteps(handler,EventDate)
go
insert into #dateSteps values(1,'01jan1990')
insert into #dateSteps values(1,'07jan1990')
insert into #dateSteps values(1,'11jan1990')
insert into #dateSteps values(1,'12jan1990')
insert into #dateSteps values(1,'28jan1990')
insert into #dateSteps values(1,'08mar1990')
insert into #dateSteps values(2,'02jan1990')
insert into #dateSteps values(2,'04jan1990')
insert into #dateSteps values(2,'10jan1990')
insert into #dateSteps values(2,'12jan1990')
insert into #dateSteps values(2,'15mar1990')
| The key to this routine is the use of row_number() within the derived table at the recursive element of the CTE. This enables us to return only the next row that is three days or more after the input date. |
with cteStartDates(Handler,EventDate)
as
(
Select Handler,
min(EventDate)
from #datesteps
group by Handler
),
CteRecursiveSkip(Handler,EventDate)
as
(
Select Handler,EventDate
from cteStartDates
union all
Select x.Handler,x.EventDate
from (
Select #datesteps.Handler,#datesteps.EventDate,
RowNum = row_number()
over (partition by #datesteps.Handler
order by #datesteps.EventDate)
from CteRecursiveSkip
join #datesteps
on #datesteps.Handler = CteRecursiveSkip.Handler
and #datesteps.Eventdate >= CteRecursiveSkip.EventDate+3
) as x
where x.RowNum = 1
)
Select *
from CteRecursiveSkip
order by Handler,EventDate
| Yet another cursor resigned to the garbage can. Performance should also be quite good across large datasets. |