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')
)