Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Extended Get Nth Weekday of period

Recently I posted a function which returned the Nth weekday of a month, either from the beginning of month or from the end of month. Function is found here http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx

I have fiddled around with it and have now extended the function to find the Nth weekday not only for a month, but also for a quarter or a year. Below is the function.

CREATE FUNCTION dbo.fnGetNthWeekdayOfPeriod
(
    @theDate DATETIME,
    @theWeekday TINYINT,
    @theNth SMALLINT,
    @theType CHAR(1)
)
RETURNS DATETIME
BEGIN
    RETURN (
                SELECT theDate
                FROM    (
                            SELECT DATEADD(DAY, theDelta +(@theWeekday + 6 - DATEDIFF(DAY, '17530101', theFirst) % 7) % 7, theFirst) AS theDate
                            FROM    (
                                        SELECT CASE UPPER(@theType)
                                                    WHEN 'M' THEN DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')
                                                    WHEN 'Q' THEN DATEADD(QUARTER, DATEDIFF(QUARTER, @theNth, @theDate), '19000101')
                                                    WHEN 'Y' THEN DATEADD(YEAR, DATEDIFF(YEAR, @theNth, @theDate), '19000101')
                                                END AS theFirst,
                                                CASE SIGN(@theNth)
                                                    WHEN -1 THEN 7 * @theNth
                                                    WHEN 1 THEN 7 * @theNth - 7
                                                END AS theDelta
                                        WHERE   @theWeekday BETWEEN 1 AND 7
                                                AND (
                                                        @theNth BETWEEN -5 AND 5
                                                        AND UPPER(@theType) = 'M'
                                                    OR
                                                        @theNth BETWEEN -14 AND 14
                                                        AND UPPER(@theType) = 'Q'
                                                    OR
                                                        @theNth BETWEEN -53 AND 53
                                                        AND UPPER(@theType) = 'Y'
                                                )
                                                AND @theNth <> 0
                                    ) AS d
                        ) AS d
                WHERE   CASE UPPER(@theType)
                            WHEN 'M' THEN DATEDIFF(MONTH, theDate, @theDate) 
                            WHEN 'Q' THEN DATEDIFF(QUARTER, theDate, @theDate) 
                            WHEN 'Y' THEN DATEDIFF(YEAR, theDate, @theDate) 
                        END = 0
            )
END

Print | posted on Thursday, June 18, 2009 11:34 AM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# re: Extended Get Nth Weekday of period

I used the Extended Get Nth Weekday of period function and it works and fast.
When I using this

Select dbo.fnGetNthWeekdayOfPeriod ('08-January-2010', 1, 2, 'Y') AS SecondMondayOfYear2010

or this

Select dbo.fnGetNthWeekdayOfPeriod ('08-August-2010', 1, 2, 'Y') AS SecondMondayOfYear2010

or this

--- date format dd-mm-yyyy
Select dbo.fnGetNthWeekdayOfPeriod (03-08-2010', 1, 2, 'Y') AS SecondMondayOfYear2010

or this

--- date format mm-dd-yyyy
Select dbo.fnGetNthWeekdayOfPeriod ('08-03-2010', 1, 2, 'Y') AS SecondMondayOfYear2010

all returns the date 2010-01-11 00:00:00.000

I am on SQL Server 2005

on testing with @theType 'M' and 'Q'
the @theDate is in the format of mm-dd-yyyy within single quote
or dd-mmm-yyyy within single quote

Please help!
8/10/2010 9:05 PM | KC Lee
Gravatar

# re: Extended Get Nth Weekday of period

update: The comments made last time was my listake. The function works perfectly.
Thanks.
1/3/2011 8:57 PM | KC Lee
Gravatar

# re: Extended Get Nth Weekday of period

You're welcome.
1/3/2011 9:32 PM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET