T-SQL CTE, calculation on Previous Row value

Question

I have a CTE that returns a set of rows depending on a pattern in the following way:

Pattern Table (Create 100 rows for the next Monday starting from Start Date)

|                                        |
|--Id--|--Start Date--|--Count--|--Days--|
|                                        |
|--AB--|  01-01-2000  |   100   | Monday |
|                                        |

And I have a function that calculate the next occurring day by passing a starting date as following:

dbo.fx_get_next_occurrence(@startDate DATETIME, @days VARCHAR)

In order to generate the rows I am using a CTE as following:

WITH Occurrences_CTE(Num, Id, StartDate)
AS
(
    SELECT
        SP.n
        ,F.Id
        ,dbo.fx_get_next_occurrence(F.Days, F.StartDate)
    FROM
        #RecurrencePattern AS F 
        INNER JOIN
        dbo.Numbers AS SP
        ON SP.n between 1 and F.[Count]
)

dbo.numbers is a table containing a list of incremental numbers.

Now, when I run the SELECT of course the CTE returns always the same "next date" because the Date is never increased, so this SELECT:

SELECT 
    CTE.Id, CTE.Num, CTE.StartDate
FROM 
    Occurrences_CTE CTE

Generates something like the following result:

|                             |
|--Id--|--Num--|--Start Date--|
|                             |
|--AB--|--001--|--01-03-2000--|
|--AB--|--002--|--01-03-2000--|
|--AB--|--003--|--01-03-2000--|
|--AB--|--004--|--01-03-2000--|
|--AB--|--005--|--01-03-2000--|

Now, despite the function which works as expected, how can I get from the CTE the previous calculated date row so that I can pass this date to my function to move to the next available day starting from the previous one? JOIN the CTE to itself, but then?

In am on SQL 2012 and 2014 so I can use LAG and other functions.

John Cappelletti · Accepted Answer

Added a field for Every. So 1 = every week, 2 would be every other week and so on.

Take this for a spin

Declare @Table table (id varchar(25),StartDate Date,Count int,Days varchar(25),Every int)
Insert into @Table values
('AB','2000-01-01',50,'Monday',2),
('CD','2000-01-01',50,'Monday,Friday',2)


Declare @MinDate Date = (Select min(StartDate) from @Table)
Declare @MaxCntr Int  = (Select max(Count) from @Table)
Declare @MaxDate Date = DateAdd(wk,@MaxCntr+10,@MinDate)

;With cteKeyDate As (
    Select KeyDate = @MinDate,KeyDOW = DateName(WEEKDAY,@MinDate),KeyWeek = datepart(WK,@MinDate)
    Union All
    Select KeyDate =  DateAdd(DD, 1, df.KeyDate) ,KeyDOW = DateName(WEEKDAY,DateAdd(DD, 1, df.KeyDate)), KeyWeek= DatePart(WK,DateAdd(DD, 1, df.KeyDate))
    From cteKeyDate DF
    Where DF.KeyDate <= @MaxDate
) 
Select * 
 From (
        Select A.ID
              ,A.StartDate
              ,A.Count
              ,A.Days 
              ,A.Every
              ,KeyDate
              ,KeyDow
              ,KeyWeek
              ,RowNr = Row_Number() over (Partition By A.ID Order By B.KeyDate)
         from @Table A
         Join cteKeyDate B on B.KeyDate>=A.StartDate and Charindex(KeyDOW,Days)>0 
      ) Final
 Where RowNr<=Count and (KeyWeek) % (Every) = 0 
 Option (maxrecursion 32767)

T-SQL CTE, calculation on Previous Row value

Answers (2)

Related Questions