Post reply

Where clause based on case

  • March 12, 2004 at 1:12 pm

    #110651

    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

    #498615

    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

    #498618

    Is there a way to do this with a range of dates too?

    Thanks so much!

  • March 13, 2004 at 6:30 am

    #498663

    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.

    ...and colDate between @Date1 and @Date2

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply