title: T-SQL Calculate recurrences of a Date
body: <p>I am working on a CTE which calculate the recurrences of a Week but I am having some problems when the <em>pattern</em> cross the year.</p>
  
  <p><strong>The CTE should</strong>
  Calculate all occurrences based on the following parameters:</p>
  
  <ul>
  <li>Recurrence Count - <em>how many times it will happen</em></li>
  <li>Week Days - <em>in which day of the week it will happen</em></li>
  <li>Start Date - <em>when the pattern calculation will start</em></li>
  <li>Periodicity - <em>How often in terms of weeks, i.e. 1 every week, 2 every 2 weeks</em></li>
  <li>Starting Week - <em>The Week number of the First Occurrence, which reflects the Start Date column</em></li>
  </ul>
  
  <p>This is how I store the patterns:</p>
  
  <pre><code>/*
      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
  
  );
  </code></pre>
  
  <p>Below is a couple of patterns I am using to test my CTE:</p>
  
  <pre><code>/*
      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'));
  </code></pre>
  
  <p>I start counting considering first day of week <strong>Monday</strong></p>
  
  <pre><code>SET DATEFIRST 1
  </code></pre>
  
  <p>And this is the CTE I am using to run this calculations:</p>
  
  <pre><code>/*
      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 &lt;= @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 &lt;&gt; 1 AND (SELECT MIN(C.StartingWeek) FROM Pattern AS C WHERE C.Id = A.Id) = KeyWeek )
              THEN KeyStartDate
          /* Otherwise formula ADD WEEKS =&gt; 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 &gt;= DATEADD(DAY, -1, A.StartDate) and Charindex(KeyDOW, A.WeekDays) &gt; 0
      ) Final                 
  Where 
      RowNr &lt;= Count AND Id = 1
  Option (maxrecursion 32767)
  </code></pre>
  
  <p>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 <strong>RowNr 15</strong> is wrong because it should happen on 23 of April (Sunday) and not the next week. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>You can execute the code with the samples <a href="http://rextester.com/GWEY37271" rel="nofollow noreferrer">here</a>.</p>