T-SQL Calculate recurrences of a Date

Question

I am working on a CTE which calculate the recurrences of a Week but I am having some problems when the pattern cross the year.

The CTE should Calculate all occurrences based on the following parameters:

• Recurrence Count - how many times it will happen
• Week Days - in which day of the week it will happen
• Start Date - when the pattern calculation will start
• Periodicity - How often in terms of weeks, i.e. 1 every week, 2 every 2 weeks
• Starting Week - The Week number of the First Occurrence, which reflects the Start Date column

This is how I store the patterns:

/*
    Pattern Table
*/
CREATE TABLE Pattern (
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY
,   [Subject] [nvarchar](100) NULL
,   [RecurrenceCount] [int] NULL
,   [WeekDays] [varchar](max) NULL
,   [StartDate] [datetime] NULL
,   [EndDate] [datetime] NULL
,   [Periodicity] [int] NULL
,   [StartingWeek] [int] NULL

);

Below is a couple of patterns I am using to test my CTE:

/*
    Pattern samples for test
*/
Insert into Pattern Values (N'Every 5 Weeks Fri, Sat, Sun', 72, 'Friday, Saturday, Sunday', N'2016-12-02', N'2016-12-02', 5, datepart(wk, N'2016-12-02'));
Insert into Pattern Values (N'Every 3 Weeks Tue, Wed, Thu', 20, 'Tuesday, Wednesday, Thursday', N'2016-11-01', N'2016-11-01', 3, datepart(wk, N'2016-11-01'));

I start counting considering first day of week Monday

SET DATEFIRST 1

And this is the CTE I am using to run this calculations:

/*
    Display Patterns
*/
select * from Pattern

DECLARE @mindate DATE       = (SELECT MIN(StartDate) FROM Pattern)
DECLARE @maxmindate DATE    = (SELECT MAX(StartDate) FROM Pattern)
DECLARE @maxcount INT       = (SELECT MAX(RecurrenceCount) FROM Pattern)
DECLARE @maxdate DATE       = DATEADD(WK, @maxcount + 10, @maxmindate)

/*
    CTE to generate required occurrences
*/
;With cteKeyDate As (
    Select 
        KeyStartDate = @MinDate, 
        KeyDOW = DateName(WEEKDAY, @MinDate), 
        KeyWeek = datepart(WK,@MinDate)
    Union All
    Select 
        KeyStartDate =  DateAdd(DD, 1, df.KeyStartDate) ,
        KeyDOW = DateName(WEEKDAY,DateAdd(DD, 1, df.KeyStartDate)), 
        KeyWeek= DatePart(WK,DateAdd(DD, 1, df.KeyStartDate))
    From cteKeyDate DF
    Where DF.KeyStartDate <= @MaxDate
) 

SELECT 
    Id, KeyStartDate, KeyDow, KeyWeek, RowNr, OccNr = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY StartDate) 
FROM
    (Select 
        A.Id
        ,A.StartDate
        ,A.EndDate
        ,Count = A.RecurrenceCount
        ,Days = A.WeekDays
        ,Every = A.Periodicity              
        ,KeyStartDate = CASE
        /* 
            if no periodicity (1) then it is sequential
            if periodicity, first week doesn't apply (MIN KeyWeek)
        */
        WHEN A.Periodicity = 1 
            OR ( Periodicity <> 1 AND (SELECT MIN(C.StartingWeek) FROM Pattern AS C WHERE C.Id = A.Id) = KeyWeek )
            THEN KeyStartDate
        /* Otherwise formula ADD WEEKS => Current Week Min Week */
        ELSE
            DATEADD( WK, ((A.Periodicity - 1) * ( KeyWeek - (SELECT MIN(C.StartingWeek) FROM Pattern AS C WHERE C.Id = A.Id) ) ) , KeyStartDate )
        END
        ,KeyDow
        ,KeyWeek
        ,RowNr = Row_Number() over (Partition By A.Id Order By B.KeyStartDate)
        ,Periodicity = A.Periodicity
    from 
        Pattern A
        Join cteKeyDate B on B.KeyStartDate >= DATEADD(DAY, -1, A.StartDate) and Charindex(KeyDOW, A.WeekDays) > 0
    ) Final                 
Where 
    RowNr <= Count AND Id = 1
Option (maxrecursion 32767)

Now, if I test again my patterns, for example, the first one, I get this result, which has the bug when the occurrences happen in the next year. The RowNr 15 is wrong because it should happen on 23 of April (Sunday) and not the next week.

Id  KeyStartDate    KeyDow      KeyWeek RowNr   OccNr
1   02.12.2016      Friday      49      1       1
2   03.12.2016      Saturday    49      2       2
3   04.12.2016      Sunday      49      3       3
4   06.01.2017      Friday      50      4       4
5   07.01.2017      Saturday    50      5       5
6   08.01.2017      Sunday      50      6       6
7   10.02.2017      Friday      51      7       7
8   11.02.2017      Saturday    51      8       8
9   12.02.2017      Sunday      51      9       9
10  17.03.2017      Friday      52      10      10
11  18.03.2017      Saturday    52      11      11
12  19.03.2017      Sunday      52      12      12
13  21.04.2017      Friday      53      13      13
14  22.04.2017      Saturday    53      14      14
15  28.04.2013      Sunday      1       15      15
16  31.05.2013      Friday      2       16      16
17  01.06.2013      Saturday    2       17      17

While the second pattern is calculated just fine. I think I have a problem in the logic when the pattern cross the year and the number of weeks reset to 0 in SQL but I cannot find a solution, I struggled now for few days.

You can execute the code with the samples here.

Answer 1

Spend some time on this. Your calculation used is flawed. Unless there is special rules not indicated in your question I do not see why some dates are special. I prefer to use variable tables.

    /*
        Pattern Table
    */
    DECLARE @Pattern TABLE(
        [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY
        ,[Subject] [nvarchar](100) NULL
        ,[RecurrenceCount] [int] NULL
        ,[WeekDays] [varchar](max) NULL
        ,[StartDate] [datetime] NULL
        ,[EndDate] [datetime] NULL
        ,[Periodicity] [int] NULL
        ,[StartingWeek] [int] NULL
    );

    /*
        Populate with values based on Recurreance and Startdate. The startdate will give the start week, which make the start week obsolete.
    */
    DECLARE @PreferredDate TABLE(
        [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY
        ,[PreferredDate] [datetime] NULL
        ,[PreferredWeek] [int] NULL
        ,[PreferredYear] [int] NULL
    )

It is very important to always retrieve the current setting for datefirst. You will break someone else's calculation if they use another setting. I have also added the pattern id for obvious reasons.

    DECLARE @DateFirst int = @@dateFirst --DATEFIRST is a global setting

    DECLARE @mindate DATE       = (SELECT MIN(StartDate) FROM @Pattern WHERE id=@PreferredSubjectID)
    DECLARE @maxmindate DATE    = (SELECT MAX(StartDate) FROM @Pattern WHERE Id=@PreferredSubjectID)
    DECLARE @maxcount INT       = (SELECT MAX(RecurrenceCount) FROM @Pattern WHERE Id=@PreferredSubjectID) 
    DECLARE @maxdate DATE       = DATEADD(WK, @maxcount + 50, @maxmindate)

    SET DATEFIRST 1

    DECLARE @PreferredSubjectID int  = 1

The @preferreddate table is populated using the following:

    /*
        CTE to generate required preferred dates
    */
    ;With ctePreferredDate AS (
        Select  PreferredDate = @MinDate, PreferredWeek = DATEPART(WK, @MinDate), PreferredYear = DATEPART(YYYY, @MinDate)
        Union All
        SELECT  PreferredDate = DATEADD(WK,(SELECT Periodicity FROM @Pattern WHERE Id=@PreferredSubjectID), PreferredDate)
                ,PreferredWeek = DATEPART(WK,DATEADD(WK,(SELECT Periodicity FROM @Pattern WHERE Id=@PreferredSubjectID), PreferredDate))
                ,PreferredYear = DATEPART(yyyy,DATEADD(WK,(SELECT Periodicity FROM @Pattern WHERE Id=@PreferredSubjectID), PreferredDate))
        From ctePreferredDate pFD
        Where pFD.PreferredDate <= @MaxDate

    )
    INSERT INTO @PreferredDate (PreferredDate, PreferredWeek, PreferredYear)
    SELECT PreferredDate, PreferredWeek, PreferredYear
    FROM ctePreferredDate

The final CTE table is populated using the following:

    /*
        CTE to generate required occurrences
    */
    ;With cteKeyDate As (
        Select  KeyStartDate = @MinDate 
                ,KeyDOW = DateName(WEEKDAY, @MinDate)
                ,KeyWeek = datepart(WK,@MinDate)
                ,id = @PreferredSubjectID
                ,KeyOccurrance = @maxcount
        Union All
        Select  KeyStartDate =  DateAdd(DD, 1, df.KeyStartDate)
                ,KeyDOW = DateName(WEEKDAY,DateAdd(DD, 1, df.KeyStartDate))
                ,KeyWeek= DatePart(WK,DateAdd(DD, 1, df.KeyStartDate))
                ,id=@PreferredSubjectID
                ,KeyOccurrance = @maxcount
        From cteKeyDate DF
        Where DF.KeyStartDate <= @MaxDate
    ) 
    SELECT  StartDate
            ,[DayOfWeek]
            ,[Week]
            ,OccNr = ROW_NUMBER() OVER         (PARTITION BY Id ORDER BY StartDate) 
    FROM
        (
        SELECT  cte.KeyStartDate AS StartDate
                ,cte.KeyDOW AS [DayOfWeek]
                ,cte.KeyWeek AS [Week]
                ,cte.id
                ,cte.KeyOccurrance AS Occurrance
                ,RowNr = ROW_NUMBER() OVER         (PARTITION BY KeyOccurrance ORDER BY KeyStartDate)
        FROM    cteKeyDate cte
                INNER JOIN
                @PreferredDate pfd
                    ON  cte.KeyWeek = pfd.PreferredWeek
                        AND YEAR(cte.KeyStartDate) = pfd.PreferredYear
        WHERE cte.KeyDOW IN (SELECT LTRIM(RTRIM(Item)) FROM fn_SplitString((SELECT weekdays from @Pattern WHERE Id=1),','))
        )cte
    WHERE   cte.RowNr <= cte.Occurrance
    ORDER BY cte.StartDate
    Option (maxrecursion 32767)

    SET DATEFIRST @DateFirst --Quite important

Results

2016/12/02  Friday  49  1
2016/12/03  Saturday    49  2
2016/12/04  Sunday  49  3
2017/01/06  Friday  2   4
2017/01/07  Saturday    2   5
2017/01/08  Sunday  2   6
2017/02/10  Friday  7   7
2017/02/11  Saturday    7   8
2017/02/12  Sunday  7   9
2017/03/17  Friday  12  10
2017/03/18  Saturday    12  11
2017/03/19  Sunday  12  12
2017/04/21  Friday  17  13
2017/04/22  Saturday    17  14
2017/04/23  Sunday  17  15
2017/05/26  Friday  22  16
2017/05/27  Saturday    22  17
2017/05/28  Sunday  22  18
2017/06/30  Friday  27  19
2017/07/01  Saturday    27  20
2017/07/02  Sunday  27  21
2017/08/04  Friday  32  22
2017/08/05  Saturday    32  23
2017/08/06  Sunday  32  24
2017/09/08  Friday  37  25
2017/09/09  Saturday    37  26
2017/09/10  Sunday  37  27
2017/10/13  Friday  42  28
2017/10/14  Saturday    42  29
2017/10/15  Sunday  42  30
2017/11/17  Friday  47  31
2017/11/18  Saturday    47  32
2017/11/19  Sunday  47  33
2017/12/22  Friday  52  34
2017/12/23  Saturday    52  35
2017/12/24  Sunday  52  36
2018/01/26  Friday  4   37
2018/01/27  Saturday    4   38
2018/01/28  Sunday  4   39
2018/03/02  Friday  9   40
2018/03/03  Saturday    9   41
2018/03/04  Sunday  9   42
2018/04/06  Friday  14  43
2018/04/07  Saturday    14  44
2018/04/08  Sunday  14  45
2018/05/11  Friday  19  46
2018/05/12  Saturday    19  47
2018/05/13  Sunday  19  48
2018/06/15  Friday  24  49
2018/06/16  Saturday    24  50
2018/06/17  Sunday  24  51
2018/07/20  Friday  29  52
2018/07/21  Saturday    29  53
2018/07/22  Sunday  29  54
2018/08/24  Friday  34  55
2018/08/25  Saturday    34  56
2018/08/26  Sunday  34  57
2018/09/28  Friday  39  58
2018/09/29  Saturday    39  59
2018/09/30  Sunday  39  60
2018/11/02  Friday  44  61
2018/11/03  Saturday    44  62
2018/11/04  Sunday  44  63
2018/12/07  Friday  49  64
2018/12/08  Saturday    49  65
2018/12/09  Sunday  49  66
2019/01/11  Friday  2   67
2019/01/12  Saturday    2   68
2019/01/13  Sunday  2   69
2019/02/15  Friday  7   70
2019/02/16  Saturday    7   71
2019/02/17  Sunday  7   72

The splitstring function:

ALTER FUNCTION [dbo].[fn_SplitString](
    @InputStr   varchar(Max),
    @Seperator  varchar(10))
RETURNS @OutStrings TABLE (ItemNo int identity(1,1), Item varchar(256))

AS
BEGIN

    DECLARE @Str varchar(2000),
            @Poz int, @cnt int

    --DECLARE @OutStrings TABLE (Item varchar(2000))

    SELECT @Poz = CHARINDEX (@Seperator, @InputStr), @cnt = 0
    WHILE @Poz > 0 AND @cnt <= 10000
    BEGIN
        SELECT @Str = SubString(@InputStr, 1, @Poz - 1)
        INSERT INTO @OutStrings(Item) VALUES(@Str)

        SELECT @InputStr = Right(@Inputstr, Len(@InputStr) - (len(@Str) + len(@Seperator)))
        SELECT @Poz = CHARINDEX (@Seperator, @InputStr), @cnt = @cnt + 1
    END
    IF @InputStr <> ''
    BEGIN
        INSERT INTO @OutStrings(Item) VALUES(@InputStr)
    END

    RETURN
END

Answer 2

The reason you are getting that issue is you are using the datepart for getting the week numbers, so once the year changes the datepart goes back to 1. This should be changed... I made this change and The dates now get in the order of sequence, check this out. The change is the first CTE by the way.

/*
    Display Patterns
*/
select * from Pattern

DECLARE @mindate DATE       = (SELECT MIN(StartDate) FROM Pattern)
DECLARE @maxmindate DATE    = (SELECT MAX(StartDate) FROM Pattern)
DECLARE @maxcount INT       = (SELECT MAX(RecurrenceCount) FROM Pattern)
DECLARE @maxdate DATE       = DATEADD(WK, @maxcount + 10, @maxmindate)

declare @minWeekPart INT = DATEPART(WK,@MinDate)
/*
    CTE to generate required occurrences
*/
;With cteKeyDate As (
    Select 
        KeyStartDate = @MinDate, 
        KeyDOW = DateName(WEEKDAY, @MinDate), 
        KeyWeek = @minWeekPart
    Union All
    Select 
        KeyStartDate =  DateAdd(DD, 1, df.KeyStartDate) ,
        KeyDOW = DateName(WEEKDAY,DateAdd(DD, 1, df.KeyStartDate)), 
        KeyWeek= @minWeekPart + datediff(WK,@MinDate,DateAdd(DD, 1, df.KeyStartDate))
    From cteKeyDate DF
    Where DF.KeyStartDate <= @MaxDate
) 
--select * from cteKeyDate
--    order by 1
--Option (maxrecursion 32767)


SELECT 
    Id, KeyStartDate, KeyDow, KeyWeek, RowNr, OccNr = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY StartDate) 
FROM
    (Select 
        A.Id
        ,A.StartDate
        ,A.EndDate
        ,Count = A.RecurrenceCount
        ,Days = A.WeekDays
        ,Every = A.Periodicity              
        ,KeyStartDate = CASE
        /* 
            if no periodicity (1) then it is sequential
            if periodicity, first week doesn't apply (MIN KeyWeek)
        */
        WHEN A.Periodicity = 1 
            OR ( Periodicity <> 1 AND (SELECT MIN(C.StartingWeek) FROM Pattern AS C WHERE C.Id = A.Id) = KeyWeek )
            THEN KeyStartDate
        /* Otherwise formula ADD WEEKS => Current Week Min Week */
        ELSE
            DATEADD( WK, ((A.Periodicity - 1) * ( KeyWeek - (SELECT MIN(C.StartingWeek) FROM Pattern AS C WHERE C.Id = A.Id) ) ) , KeyStartDate )
        END
        ,KeyDow
        ,KeyWeek
        ,RowNr = Row_Number() over (Partition By A.Id Order By B.KeyStartDate)
        ,Periodicity = A.Periodicity
    from 
        Pattern A
        Join cteKeyDate B on B.KeyStartDate >= DATEADD(DAY, -1, A.StartDate) and Charindex(KeyDOW, A.WeekDays) > 0
    ) Final                 
Where 
    RowNr <= Count AND Id = 1
    order by 2 
Option (maxrecursion 32767)

I did run it in the rextester and you can find it here --> http://rextester.com/GWEY37271