CASE Statment Help

  • This may be a simple one for you guys, but I just don't know the correct syntax.

    I have the following CASE statement:

    CASE WHEN ModifiedDate > DateClosed THEN 1 ELSE 0 END as FieldName

    The ClosedDate field sometimes is NULL and the modifieddate field has value. I will get a false 1 since the modifieddate will always be larger than the ClosedField that has a NULL.

    How can I tell it when the DateClosed field is not null?

    Thanks.

    Hope that made sense.

    UPDATE: Never mind, I was able to make it work.

  • What approach did you take?

    My option would be something like this:

    CASE WHEN ModifiedDate > ISNULL( DateClosed, 0) THEN 1 ELSE 0 END as FieldName

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • DarthBurrito (11/13/2013)

    I will get a false 1 since the modifieddate will always be larger than the ClosedField that has a NULL.

    How can I tell it when the DateClosed field is not null?

    No, you won't. The condition won't be "true" if the column is NULL, so you will get a 0 result. (If the column is NULL, the condition result will also be "NULL", which is not "true".

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Hi what was Your solution? It would be good if you can share one.

    I may have similar situation in future.

    Thanks

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

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