Thinking outside the box

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

My Links

Advertisement

News

Archives

Post Categories

How to get the Weekday and Nth from a date

You call this function with a date. The function returns a table with one record and 3 columns.
First column is Weekday; Monday = 1, Tuesday = 2, Wednesday = 3; Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7.
Second column is number of occurencies of that date since beginning of selected period type.
Third columns is number of occurencies left of that period type.

CREATE FUNCTION dbo.fnGetWeekdayAndNths
(
    @theDate DATETIME,
    @theType CHAR(1)
)
RETURNS TABLE
AS
RETURN (   SELECT 1 + DATEDIFF(DAY, -53690, @theDate) % 7 AS theWeekday,
                    1 +(theDelta - 1) / 7 AS Beginning,
                    DATEDIFF(DAY, DATEADD(DAY, -1, thePeriod), @theDate) / 7 - 1 AS Ending
            FROM    (
                        SELECT CASE UPPER(@theType)
                                    WHEN 'M' THEN DATEADD(MONTH, DATEDIFF(MONTH, -53690, @theDate), -53659)
                                    WHEN 'Q' THEN DATEADD(QUARTER, DATEDIFF(QUARTER, -53690, @theDate), -53600)
                                    WHEN 'Y' THEN DATEADD(YEAR, DATEDIFF(YEAR, -53690, @theDate), -53325)
                                END AS thePeriod,
                                CASE UPPER(@theType)
                                    WHEN 'M' THEN DATEPART(DAY, @theDate)
                                    WHEN 'Q' THEN DATEDIFF(DAY, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @theDate), 0), DATEADD(QUARTER, DATEDIFF(QUARTER, -53690, @theDate), -53600))
                                    WHEN 'Y' THEN DATEPART(DAYOFYEAR, @theDate)
                                END AS theDelta
                    ) AS d
            WHERE   UPPER(@theType) IN('Y', 'Q', 'M')
        )

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

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET