Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    42

    Unanswered: Can somebody tell me what is wrong with this??

    I am trying to created a view and have a need for conditional logic:

    Here is what I presently have (not working):
    ----------------------------------------------------------------------------
    IF (ISDATE(COMPLETIONDATE) = 1)
    BEGIN
    CASE
    WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
    WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
    WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
    END AS THRESHOLDSTATUS
    END
    ELSE
    IF (ISDATE(COMPLETIONDATE) = 0)
    BEGIN
    CASE
    WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
    WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
    WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
    END AS THRESHOLDSTATUS
    END
    --------------------------------------------------------------------------

    Can someone tell me what I am doing wrong?

    Basically I am trying to test to see if "completiondate" is a date and if it is then perform a case operation using it, if it is not a date then I want to perform the case operation using "targetcompletiondate".

    Thanks...

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select THRESHOLDSTATUS =
    CASE
    WHEN DATEDIFF(d, case when ISDATE(COMPLETIONDATE) = 1 then COMPLETIONDATE else TARGETCOMPLETIONDATE end, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
    WHEN DATEDIFF(d, case when ISDATE(COMPLETIONDATE) = 1 then COMPLETIONDATE else TARGETCOMPLETIONDATE end, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, case when ISDATE(COMPLETIONDATE) = 1 then COMPLETIONDATE else TARGETCOMPLETIONDATE end, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
    WHEN DATEDIFF(d, case when ISDATE(COMPLETIONDATE) = 1 then COMPLETIONDATE else TARGETCOMPLETIONDATE end, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
    END
    from ...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First problem: You have a column called "CompletionDate" which may contain something that is NOT A DATE?!?! That's a design issue right there...

    Second problem: You are putting this in a VIEW? Complex logic such as this is better off in a stored procedure. You didn't provide your entire CREATE VIEW statement, but depending upon what you are trying to do, your task may not be possible.

    You may be able to use nested CASE statements:
    CASE WHEN ISDATE(COMPLETIONDATE) = 1
    CASE WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
    WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
    WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
    END
    ELSE
    CASE WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
    WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
    WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
    END
    END AS THRESHOLDSTATUS

    ...or a single CASE statement with more complex criteria:
    CASE
    WHEN ISDATE(COMPLETIONDATE) = 1 AND (DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE')) THEN 'GREEN'
    WHEN ISDATE(COMPLETIONDATE) = 1 AND (DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE')) AND (DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0) THEN 'YELLOW'
    WHEN ISDATE(COMPLETIONDATE) = 1 AND (DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
    WHEN ISDATE(COMPLETIONDATE) = 0 AND (DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE')) THEN 'GREEN'
    WHEN ISDATE(COMPLETIONDATE) = 0 AND (DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE')) AND (DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0) THEN 'YELLOW'
    WHEN ISDATE(COMPLETIONDATE) = 0 AND (DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
    END AS THRESHOLDSTATUS
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I thought that that's what I gave the poster...???
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, you sniped me by 10 minutes. So you get kudos for being first, but I get points for verbosity!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •