CODEWITHSUNDEEP
sql-server
Lisa Rose
Lisa Rose

Reputation: 67

Nested If Statement Using Case Statement in SQL Server

I inherited a spreadsheet from a co-worker who used a nested If statement as shown below:

  =IF(AND([@[Pred Type]]="PR_FS",[@[ST Finish]]="A"),
      0,
      IF(AND([@[Pred Type]]="PR_SS", OR([@[ACT_START_DATE]]<>0,[@[ST Start]]="A")),
         0,
         IF(AND([@[Pred Type]]="PR_SF",OR([@[ACT_START_DATE]]<>0,[@[ST Start]]="A")),
            0,
            IF(AND([@[Pred Type]]="PR_FF",[@[STFinish]]="A"),0,1)
           )
        )
     )

I know I can write a CASE statement that would mimic this but can't figure out the syntax for it. I tried:

,CASE   WHEN #Preds.Pred_Type = 'PR_FS' and #Tbl_Sched_Act.Expected_Finish = 'A' THEN 0 
    WHEN #Preds.Pred_Type = 'PR_SS' OR ACT_START_DATE IS NOT NULL OR Start_RAW IS NOT NULL THEN 0
    WHEN #Preds.Pred_Type = 'PR_SF'OR ACT_START_DATE IS NOT NULL OR Start_RAW IS NOT NULL THEN 0
    WHEN #Preds.Pred_Type = 'PR_FF' and #Tbl_Sched_Act.Expected_Finish = 'A' THEN 0 
    ELSE 1
    END as PRED_MET

However, the CASE statement does not evaluate any of the items to be equal to 1. They all evaluate to 0 which isn't correct.

Does anyone have any idea on how I could write my CASE statement correctly?

Upvotes: 0

Views: 6253

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

You need to group the OR inside parenthesis () to force operator precedence.

CASE   
    WHEN #Preds.Pred_Type = 'PR_FS' and #Tbl_Sched_Act.Expected_Finish = 'A' 
    THEN 0 
    WHEN #Preds.Pred_Type = 'PR_SS' 
     AND (ACT_START_DATE <> 0 OR Start_RAW <> 0) 
    THEN 0
    WHEN #Preds.Pred_Type = 'PR_SF'
     AND (ACT_START_DATE <> 0 OR Start_RAW <> 0)
    THEN 0
    WHEN #Preds.Pred_Type = 'PR_FF' and #Tbl_Sched_Act.Expected_Finish = 'A' 
    THEN 0           
    ELSE 1
END as PRED_MET

NOTE:: If a number is <> 0 then also is NOT NULL DEMO

But that can also be simplify to:

CASE   
    WHEN #Tbl_Sched_Act.Expected_Finish = 'A' 
     AND #Preds.Pred_Type  IN ('PR_FS', 'PR_FF')
    THEN 0 
    WHEN (ACT_START_DATE <> 0 OR Start_RAW <> 0) 
     AND (#Preds.Pred_Type IN ('PR_SS','PR_SF')
    THEN 0
    ELSE 1
END as PRED_MET

Upvotes: 1

Related Questions