March 12, 2004 at 1:12 pm
I may need to exclude Fridays, Saturdays, Sundays or any combination of those from a select statement. I have flags set up to tell me which days to exclude. For example, if sun_flag = 'Y' and sat_flag = 'Y', then I want to exclude them from my select statement. How can I do this?
I'm getting a count of holidays but if the holday is on a saturday for example and the sat_flag = 'Y', then I don't want to count it.
Or if I'm getting a count of holidays but if the holday is on a friday or sunday for example and the fri_flag = 'Y' and the sun_flag = 'Y', then I don't want to count it.
March 12, 2004 at 2:02 pm
SELECT count(*) --DATENAME (dw, colDATE)
FROM tblHolidays
WHERE
(CASE WHEN (DATENAME(dw, colDATE) = 'Friday' and @fri_flag = 'Y')
OR (DATENAME(dw, colDATE) = 'Saturday' and @sat_flag = 'Y')
OR (DATENAME(dw, colDATE) = 'Sunday' and @sun_flag = 'Y' )
THEN 0 ELSE 1 END) = 1
Replace the tblHolidays with your table and replace colDate with your date field and this will do the trick.
March 12, 2004 at 2:08 pm
Is there a way to do this with a range of dates too?
Thanks so much!
March 13, 2004 at 6:30 am
Range of Dates? If you want to only look at a range of dates in addition to the weekend flags you can append the critiria to the where clause.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply