# Thinking outside the box

Patron Saint of Lost Yaks

## Get the Nth weekday of any arbitrary period

I have previously posted some algorithm how to find the Nth weekday for a fixed period such as month, quarter or year.
See http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx
http://weblogs.sqlteam.com/peterl/archive/2009/06/18/How-to-get-the-Weekday-and-Nth-from-a-date.aspx
http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx
http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx

This algorithm in the function allows you to get a weekday for any arbitrary period, both forward and backwards. If you pass a positive number for @Nth you will return the Nth weekday within the specified period, starting forwards from @FromDate. If you pass a negative number for @Nth, you will return the Nth weekday starting backwards from @ToDate.

CREATE FUNCTION    dbo.fnWeekdayOfPeriod
(
@FromDate AS DATETIME,
@ToDate AS DATETIME,
@WeekDay TINYINT,
@Nth INT
)
RETURNS DATETIME
AS
BEGIN
RETURN  (
CASE
WHEN @Weekday < theFrom % 7 THEN theFrom - theFrom % 7 + @Weekday + 7
ELSE theFrom - theFrom % 7 + @Weekday
END,
'17530101') AS theDate
FROM    (
SELECT  CASE SIGN(@Nth)
WHEN 1 THEN DATEDIFF(DAY, '17530101', @FromDate) + 7 * @Nth - 7
ELSE DATEDIFF(DAY, '17530101', @ToDate) + 7 * @Nth + 1
END AS theFrom,
CASE SIGN(@Nth)
WHEN 1 THEN DATEDIFF(DAY, '17530101', @FromDate) + 7 * @Nth - 1
ELSE DATEDIFF(DAY, '17530101', @ToDate) + 7 * @Nth + 7
END AS theTo,
DATEDIFF(DAY, '17530101', @FromDate) AS theMin,
DATEDIFF(DAY, '17530101', @ToDate) AS theMax
WHERE   @Nth BETWEEN -430307 AND 430307
AND @Nth <> 0
AND @Weekday BETWEEN 0 AND 7
) AS d
WHERE   CASE
WHEN @Weekday < theFrom % 7 THEN theFrom - theFrom % 7 + @Weekday + 7
ELSE theFrom - theFrom % 7 + @Weekday
END BETWEEN theMin AND theMax
)
END

Print | posted on Tuesday, November 03, 2009 4:11 PM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Comments have been closed on this topic.