Case in Where Clause

demiwolf · March 20th, 2006, 05:27 PM

Unless if someone has a better suggestion? I can't seem to get the syntax right on this:

where dti.employeeName = 'Doe, John' and
Case when (DateName(weekday, '03/01/2005') != 'Sunday') then
        PostingDate > (DATEADD(dd, DATEDIFF(dd,0,'03/01/2005'), -datepart(weekday, '03/01/2005'))) --and (PostingDate < '03/16/2005')
    Else
PostingDate >= '03/01/2005' and PostingDate < '03/16/2005'
    End

It gives me the following error: Line 47: Incorrect syntax near '>'.

Help would be greatly appreciated...
-S.

rstelma · March 20th, 2006, 06:37 PM

If you're trying to find a result between two dates try using the BETWEEN function. It is very well explained in the BOL. Besides, we can't tell here what line is erroring. I don't know which line is line 47. Also, don't assume we can understand exactly what you're trying to accomplish with you query. If you do explain as much as possible, someone here might have an easier way to do it.

Jeff Mason · March 20th, 2006, 07:24 PM

The CASE ... END construction in SQL is an expression and not a statement like in other languages.

Thus, you use CASE...END in a WHERE clause like this:

Code:

SELECT ...
WHERE PostingDate >
        CASE WHEN <somecondition> THEN <somedatevalue>
             WHEN <someothercondition> THEN <someotherdatevalue>
             ...
        END
...

I'm not sure I understand from the code you posted what you are trying to accomplish, so I can't be more specific on the exact expression you need...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

demiwolf · March 23rd, 2006, 12:45 PM

Hi guys, thanks for the help. I figured it out (as in it doesn't error anymore).

However, it's now doing something really interesting. It picks up the first case every time.

-Edit- I figured it out... wayward date calculation. For anyone needing to reference it, here's the code that worked out.

where dti.employeename = @empName and PostingDate >=
    Case when (DateName(weekday, @startDate) != 'Sunday' AND e.OfficeCode in ('S', 'K', 'L', 'R') AND e.WorkPlace = 'F') then
         DATEADD(dd, DATEDIFF(dd,0, @startDate), -datepart(weekday, @startDate))
    Else
        DATEADD(dd, DATEDIFF(dd,0, @startDate), 0)
    End
    and PostingDate < DATEADD(dd, DATEDIFF(dd,0,@endDate), 1)