Peter Larsson Blog

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  (
            SELECT  DATEADD(DAY,
                    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