SQL Server. Stored procedure to get the biweekly periods

I'm currently trying to write a stored procedure that can compute the biweekly periods when a date is passed in as a parameter.

The business logic: the first Monday of the year is first day of the biweekly period.

For example in 2010:

    period  period_start  period_end
    1       2010-01-04    2010-01-17
    2       2010-01-18    2010-01-31
    3       2010-02-01    2010-02-14
    ....
    26      2010-12-20    2011-01-02

Passing today's date of 2010-12-31 will return 26, 2010-12-20 and 2011-01-02. How t开发者_JS百科o achieve this?

@Lamak did the hard part, figuring out the first Monday in the year (upvoted). I revised his routine a bit to take a datetime value, like so:

--  Return first Monday for the year being passed in
CREATE FUNCTION dbo.FirstMonday (@TargetDay datetime)
RETURNS DATE
AS
BEGIN
DECLARE @Return DATE

--  Set to first of its year
SET @TargetDay = dateadd(dd, -datepart(dayofyear, @TargetDay) + 1, @TargetDay)

;WITH Dates AS
(
  SELECT @TargetDay AS DateVal
  UNION ALL
  SELECT DATEADD(d, 1, DateVal) AS DateVal
  FROM Dates
  WHERE DATEADD(d, 1, DateVal) < DATEADD(m, 1, @TargetDay)
)
SELECT @Return = MIN(DateVal)
FROM Dates
WHERE DATENAME(WEEKDAY,DateVal) = 'Monday'

RETURN @Return
END 
GO

--  Test it
print dbo.FirstMonday(getdate())
print dbo.FirstMonday('Jan 1, 2010')

From there, it's just some datetime arithmatic:

DECLARE
  @FirstMonday  datetime
 ,@TargetDay    datetime
 ,@BiWeek       int

SET @TargetDay = getdate()
--SET @TargetDay = 'Jan 17, 2010'

--  Get the first Monday
SET @FirstMonday = dbo.FirstMonday(@TargetDay)

--  Calculate the bi-weekly period
SET @BiWeek = datediff(dd, @FirstMonday, @TargetDay) / 14

--  Print results
PRINT @BiWeek + 1
PRINT dateadd(dd, @BiWeek * 14, @FirstMonday)
PRINT dateadd(dd, @BiWeek * 14 + 13, @FirstMonday)

--  Or return them as a dataset
SELECT
  @BiWeek + 1                                   Period
 ,dateadd(dd, @BiWeek * 14, @FirstMonday)       PeriodStart
 ,dateadd(dd, @BiWeek * 14 + 13, @FirstMonday)  PeriodEnd

This fails when you pick a day in the year that falls before the first Monday, as it returns the first biweekly period after that date. (Or does it fail? You did not specify what the proper period is for such dates... :)

However, as @HLGEM says, depending on what you are doing you are probably better off building and using some form of biweekly period lookup table, especially if you need proper handling of those early dates.

You can create a function to get the first Monday of a year. Something like this should work:

CREATE FUNCTION dbo.FirstMonday(@Year VARCHAR(4))
RETURNS DATE
AS
BEGIN
DECLARE @Return DATE;

WITH Dates AS  
(
  SELECT CONVERT(DATE,@Year+'0101') AS DateVal 
  UNION ALL 
  SELECT DATEADD(d, 1, DateVal) AS DateVal 
  FROM Dates 
  WHERE DATEADD(d, 1, DateVal) < DATEADD(m, 1, @Year+'0101')
)
SELECT @Return = MIN(DateVal)
FROM Dates
WHERE DATENAME(WEEKDAY,DateVal) = 'Monday'

RETURN @Return
END