Generating Records based on frequency

Try

Select s.Requestid + Row_Number() Over(Partition By s.Requestid Order By c.dt) As Requestid,
  Cast(c.dt As smalldatetime) As ProductionMonth,
  s.assignedanalystid As AssignedAnalyst,
  s.frequency As freqcd,
  Cast(c.dt As smalldatetime) As EnteredDate,
  s.requesttypeid As rptdesc,
  s.CustomerName As groupname,
  s.Requestid As originalrequestid

INTO NewTable -- create new table on the fly

From @Sample s
Inner Join dbo.Calendar c On c.dt Between s.CompletedDate And DateAdd(month, 1, s.ExpirationDate)
Where (s.frequency = 'Monthly' And c.D = 1)
  Or (s.frequency = 'Weekly' And DateDiff(day, s.CompletedDate, c.dt) % 7 = 0 And c.dt > s.CompletedDate And c.dt <= DateAdd(week, 1, s.ExpirationDate))
  Or (s.frequency = 'Bi-Weekly' And DateDiff(day, s.CompletedDate, c.dt) % 14 = 0 And c.dt > s.CompletedDate And c.dt <= DateAdd(week, 2, s.ExpirationDate))

AND s.RequestId = @RequestID

Order By originalrequestid, Requestid;

---

For every expert, there is an equal and opposite expert. - Becker's Law





My blog



My TechNet articles