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.