Post reply

CASE WHEN in WHERE clause

  • November 6, 2009 at 9:36 am

    #227713

    Hi All,

    I have a need to have a case statement in the where clause as follows:

    WHERE (CASE WHEN EndDate < CURRENT_TIMESTAMP THEN

    (StartDate > CURRENT_TIMESTAMP)

    ELSE (EndDate >= CURRENT_TIMESTAMP AND

    StartDate <= CURRENT_TIMESTAMP)

    END)

    But it complains on the ">" in the second line with "Incorrect syntax near '>' "

    How do I do this?

    Thanks.

  • November 6, 2009 at 9:41 am

    #1075979

    Hi

    Try OR:

    WHERE

    (EndDate < CURRENT_TIMESTAMP AND StartDate > CURRENT_TIMESTAMP)

    OR

    (EndDate >= CURRENT_TIMESTAMP AND StartDate <= CURRENT_TIMESTAMP)

    BUT

    Have a look to your execution plan to avoid full table scans.

    Greets

    Flo

  • November 6, 2009 at 9:46 am

    #1075988

    It may help if you provided the entire query not just part of it. Going further, it would help even more if you could provide the DDL (CREATE TABLE statement(s)) for the table(s) involved, sample data (as INSERT statement(s)), expected results based on the sample data.

    For more guidance on how to posts questions that get the best help please read the first article I have referenced in my signature block below regarding "Asking for assistance". If you follow the instructions in that article you will get a lot of help and tested code in return.

  • November 6, 2009 at 2:29 pm

    #1076138

    Hi Florian,

    Thanks for your reply. What do i need to check/uncheck in Execution plan to set Full Table Scan OFF?

    Thanks again.

  • November 6, 2009 at 2:53 pm

    #1076146

    ramadesai108 (11/6/2009)

    What do i need to check/uncheck in Execution plan to set Full Table Scan OFF?

    LOL. Performance tuning would be easy if one could do that.

    What he's suggesting you to do is to look at the exec plan and make sure that you don't have table scans. If you do, check the indexes maybe tweak the query.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 6, 2009 at 3:45 pm

    #1076160

    Indexes are existing on all the tables.

  • November 6, 2009 at 3:47 pm

    #1076162

    Just because you have indexes on the tables doesn't mean they are the right indexes. Looking at the execution plan can help you determine if the indexes you have are correct.

  • November 6, 2009 at 3:56 pm

    #1076165

    Thanks, Lynn. That really helped.

  • November 6, 2009 at 3:57 pm

    #1076166

    Take a look at this article, and the ones that will be published next week for info on indexes

    http://www.sqlservercentral.com/articles/Indexing/68439/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 6, 2009 at 4:06 pm

    #1076171

    Gail, That has helped even more. I will be looking for the next article next week. Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

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