CTE Weekly occurrences, Sql Server

CTE Weekly occurrences

Language: Layout:

set datefirst 1

/*
    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

);

/*
    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'));

/*
    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)

View schema
Execution time: 0,05 sec, rows selected: 78, rows affected: 2, absolute service time: 0,23 sec

	Id	Subject	RecurrenceCount	WeekDays	StartDate	EndDate	Periodicity	StartingWeek
1	1	Every 5 Weeks Fri, Sat, Sun	72	Friday, Saturday, Sunday	02.12.2016 00:00:00	02.12.2016 00:00:00	5	49
2	2	Every 3 Weeks Tue, Wed, Thu	20	Tuesday, Wednesday, Thursday	01.11.2016 00:00:00	01.11.2016 00:00:00	3	45

	Id	KeyStartDate	KeyDow	KeyWeek	RowNr	OccNr
1	1	02.12.2016 00:00:00	Friday	49	1	1
2	1	03.12.2016 00:00:00	Saturday	49	2	2
3	1	04.12.2016 00:00:00	Sunday	49	3	3
4	1	06.01.2017 00:00:00	Friday	50	4	4
5	1	07.01.2017 00:00:00	Saturday	50	5	5
6	1	08.01.2017 00:00:00	Sunday	50	6	6
7	1	10.02.2017 00:00:00	Friday	51	7	7
8	1	11.02.2017 00:00:00	Saturday	51	8	8
9	1	12.02.2017 00:00:00	Sunday	51	9	9
10	1	17.03.2017 00:00:00	Friday	52	10	10
11	1	18.03.2017 00:00:00	Saturday	52	11	11
12	1	19.03.2017 00:00:00	Sunday	52	12	12
13	1	21.04.2017 00:00:00	Friday	53	13	13
14	1	22.04.2017 00:00:00	Saturday	53	14	14
15	1	28.04.2013 00:00:00	Sunday	1	15	15
16	1	31.05.2013 00:00:00	Friday	2	16	16
17	1	01.06.2013 00:00:00	Saturday	2	17	17
18	1	02.06.2013 00:00:00	Sunday	2	18	18
19	1	05.07.2013 00:00:00	Friday	3	19	19
20	1	06.07.2013 00:00:00	Saturday	3	20	20
21	1	07.07.2013 00:00:00	Sunday	3	21	21
22	1	09.08.2013 00:00:00	Friday	4	22	22
23	1	10.08.2013 00:00:00	Saturday	4	23	23
24	1	11.08.2013 00:00:00	Sunday	4	24	24
25	1	13.09.2013 00:00:00	Friday	5	25	25
26	1	14.09.2013 00:00:00	Saturday	5	26	26
27	1	15.09.2013 00:00:00	Sunday	5	27	27
28	1	18.10.2013 00:00:00	Friday	6	28	28
29	1	19.10.2013 00:00:00	Saturday	6	29	29
30	1	20.10.2013 00:00:00	Sunday	6	30	30
31	1	22.11.2013 00:00:00	Friday	7	31	31
32	1	23.11.2013 00:00:00	Saturday	7	32	32
33	1	24.11.2013 00:00:00	Sunday	7	33	33
34	1	27.12.2013 00:00:00	Friday	8	34	34
35	1	28.12.2013 00:00:00	Saturday	8	35	35
36	1	29.12.2013 00:00:00	Sunday	8	36	36
37	1	31.01.2014 00:00:00	Friday	9	37	37
38	1	01.02.2014 00:00:00	Saturday	9	38	38
39	1	02.02.2014 00:00:00	Sunday	9	39	39
40	1	07.03.2014 00:00:00	Friday	10	40	40
41	1	08.03.2014 00:00:00	Saturday	10	41	41
42	1	09.03.2014 00:00:00	Sunday	10	42	42
43	1	11.04.2014 00:00:00	Friday	11	43	43
44	1	12.04.2014 00:00:00	Saturday	11	44	44
45	1	13.04.2014 00:00:00	Sunday	11	45	45
46	1	16.05.2014 00:00:00	Friday	12	46	46
47	1	17.05.2014 00:00:00	Saturday	12	47	47
48	1	18.05.2014 00:00:00	Sunday	12	48	48
49	1	20.06.2014 00:00:00	Friday	13	49	49
50	1	21.06.2014 00:00:00	Saturday	13	50	50
51	1	22.06.2014 00:00:00	Sunday	13	51	51
52	1	25.07.2014 00:00:00	Friday	14	52	52
53	1	26.07.2014 00:00:00	Saturday	14	53	53
54	1	27.07.2014 00:00:00	Sunday	14	54	54
55	1	29.08.2014 00:00:00	Friday	15	55	55
56	1	30.08.2014 00:00:00	Saturday	15	56	56
57	1	31.08.2014 00:00:00	Sunday	15	57	57
58	1	03.10.2014 00:00:00	Friday	16	58	58
59	1	04.10.2014 00:00:00	Saturday	16	59	59
60	1	05.10.2014 00:00:00	Sunday	16	60	60
61	1	07.11.2014 00:00:00	Friday	17	61	61
62	1	08.11.2014 00:00:00	Saturday	17	62	62
63	1	09.11.2014 00:00:00	Sunday	17	63	63
64	1	12.12.2014 00:00:00	Friday	18	64	64
65	1	13.12.2014 00:00:00	Saturday	18	65	65
66	1	14.12.2014 00:00:00	Sunday	18	66	66
67	1	16.01.2015 00:00:00	Friday	19	67	67
68	1	17.01.2015 00:00:00	Saturday	19	68	68
69	1	18.01.2015 00:00:00	Sunday	19	69	69
70	1	20.02.2015 00:00:00	Friday	20	70	70
71	1	21.02.2015 00:00:00	Saturday	20	71	71
72	1	22.02.2015 00:00:00	Sunday	20	72	72