TSQL Selecting 2 & 3 week intervals.

Question

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 pointers?

Thanks! George

Answer 1

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.

Answer 2

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)

Answer 3

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