TSQL Selecting 2 & 3 week intervals

I am attempting to populate a table based on 2 and 3 week intervals for a semi-monthly pay period in TSQL. The table should populate,

2 week date
2 week date
3 week date
2 week date
2 week date
3 week date

..based on the first date I supply, subsequently adding 2 or 3 weeks to the last date supplied. I should be able to supply a start date and end date. It may be that it's just early in the morning, but can't think of an elegant way to accomplish this task. Any point开发者_如何学Goers?

Thanks! George

WITH    dates (d, n) AS
        (
        SELECT  @mydate, 1
        UNION ALL
        SELECT  DATEADD(week, CASE n % 3 WHEN 0 THEN 3 ELSE 2 END, d), n + 1
        FROM    dates
        WHERE   d < @enddate
        )
INSERT
INTO    mytable
SELECT  d
FROM    dates
OPTION  (MAXRECURSION 0)

Horrid brute force approach - because the 2,2,3 is difficult to loop just adding it regardless into the temp table and then filtering at the end incase a couple extra entries go in - not the most efficient but if you are needing to just get a range one off then it works.

So the caveat here is: ok for one off, I wouldn't use in production :)

declare @start datetime
declare @end datetime
declare @calculated datetime

set @start = '20010101'
set @end = '20011231'

set @calculated = @start

Create Table #Dates (PayDate datetime)

while @calculated <= @end
begin
    set @calculated = DateAdd(wk,2,@calculated)
    insert into #Dates(paydate) values (@calculated)
    set @calculated = DateAdd(wk,2,@calculated)
    insert into #Dates(paydate) values (@calculated)
    set @calculated = DateAdd(wk,3,@calculated)
    insert into #Dates(paydate) values (@calculated)
end
select * from #Dates where paydate >= @start and paydate <= @end
drop table #dates

So you have a 7-week cycle -- figure out which 7-week period you're in from some known starting point and then which week of this group of 7 you are.