Finding Occurrences In The Future, Periods And Start Dates

Question

Okay, I'll simplify this post.

I have a table "tasks" containing, for example, a task name, the date it starts, and a frequency. Based on these fields I need to be able to specify a date range and return each occurrence of that task for when it's due. For example, if I have a task with the frequency of M (for monthly) and my date range is today and a year in the future, then I will return twelve occurrences of that task from my output.

You'd think this is simply, but I've spent the last few days bleeding from my eyes trying to figure this one out. The output needs to also say when the task is next due.

Answer 1

How about something like this. You can expand the case to allow for other frequencies

DECLARE @Table TABLE(
     TaskName VARCHAR(10),
     StartDate DATETIME,
     Frequency VARCHAR(10) --let say D,W,M daily, weekly, monthly
)

INSERT INTO @Table (TaskName,StartDate,Frequency) SELECT 'TADA', '15 Jan 2009', 'M'

DECLARE @StartDate DATETIME,
     @EndDate DATETIME

SELECT  @StartDate = '27 Nov 2009',
     @EndDate = '27 Nov 2010'

;WITH cte AS(
    SELECT TaskName,
      StartDate,
      Frequency
    FROM @Table
    UNION ALL
    SELECT TaskName,
      CASE 
       WHEN Frequency = 'M' THEN DATEADD(mm,1,StartDate)
      END,
      Frequency
    FROM cte
    WHERE StartDate <= @EndDate
)
SELECT  *
FROM    cte
WHERE   StartDate BETWEEN @StartDate AND @EndDate
OPTION (MAXRECURSION 0)