1

So here is my situation. I am trying to write a script which will (among other things) allow me to measure prime time block utilization. For reasons unknown to and unchangeable by me, various departments here consider "prime time" to be different on one day of the week. I've gotten around this by writing the following into SQL:

CASE WHEN DATENAME(DW, OD.CALENDAR_DT) = 'Tuesday' 
     THEN CAST(OD.CALENDAR_DT + '08:00:00.000' AS DATETIME) 
     ELSE CAST(OD.CALENDAR_DT + '07:00:00.000' AS DATETIME) 
END AS PT_BLOCK_START

However, one department has their prime time block measured differently on Tuesdays, but not consistently. Prime Time is measured most days from 7:30-5, but on the 1st and 3rd Tuesday of a month it's 9 to 5 and on the 2nd and 4th Tuesday it is 8 to 5. I am trying to figure out how to write a statement similar to the above that accounts for which Tuesday of the month it is, but the little hamster than runs on the wheel in my skull seems to have keeled over dead.

Any thoughts will be greatly appreciated and will surely bring mounds of good karma, if you believe in that sort of thing.

0

This code works by passing a date value, such as "getdate()", to a function.

Works great. Not my work.

Source: http://mikedefehr.com/2011/07/25/finding-the-nth-weekday-of-a-given-month/

CREATE FUNCTION nth_weekday_of_month_fn ( 
@dt DATETIME, 
@dow VARCHAR(10), 
@num INT 
) 
RETURNS DATETIME 
AS 
BEGIN

DECLARE @RetDate DATETIME;

WITH MonthDays AS 
( 
SELECT DATEADD(DAY,number-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)) AS
MonthDate 
FROM MASTER..spt_values 
WHERE number > 0 
   AND TYPE = 'P' 
) 
, WeekDays AS 
( 
SELECT *, ROW_NUMBER() OVER(ORDER BY MonthDate) DayIndex 
FROM MonthDays 
WHERE YEAR(@dt) = YEAR(MonthDate) 
   AND MONTH(@dt) = MONTH(MonthDate) 
   AND DATENAME(dw,MonthDate) LIKE @dow + '%' 
) 
SELECT @RetDate = MonthDate 
FROM WeekDays 
WHERE DayIndex = @num RETURN (@RetDate) END 
GO

For 1st and 3rd Tuesdays of current month:

SELECT dbo.nth_weekday_of_month_fn(GETDATE(),'Tuesday',1) 
SELECT dbo.nth_weekday_of_month_fn(GETDATE(),'Tuesday',3)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.