CODEWITHSUNDEEP
sql
jsmabbas
jsmabbas

Reputation: 35

Multiple Case Conditions

I am using the following query to get the credit points, how will I add two more conditions if the ID_Points =2 then 0.5 and ID_points = 3 then 0.25 

SELECT     
    Attend_ID, 
    Attend_Date, 
    ID_Points, 
    Employee_ID, 
    First_Name, 
    Last_Name, 
    NextDate, 
    NEXT123, 
    Difference, 
    DAY90CREDIT, 
    CREDITDATE, 
    CASE 
        WHEN (day90Credit = 0 AND CreditDate < Getdate()) 
          OR DateAdd(DAY, 90, attend_date) < COALESCE (NextDate, GETDATE()) 
         AND ID_Points = 1 THEN 1 
        ELSE 0 END AS TOTALCREDIT                  
FROM
     dbo.Test_DiffNintyDays

Upvotes: 0

Views: 39

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56725

Good formatting makes this a lot easier and more obvious:

Assuming your description was literal:

 SELECT     Attend_ID, 
            Attend_Date, 
            ID_Points, 
            Employee_ID, 
            First_Name, 
            Last_Name, 
            NextDate, 
            NEXT123, 
            Difference, 
            DAY90CREDIT, 
            CREDITDATE, 
            CASE 
                WHEN (day90Credit = 0 AND CreditDate < Getdate()) 
                  OR DateAdd(DAY, 90, attend_date) < COALESCE (NextDate, GETDATE()) 
                 AND ID_Points = 1 THEN 1
                WHEN ID_Points = 2 THEN 0.5 
                WHEN ID_Points = 3 THEN 0.25
                ELSE 0 END AS TOTALCREDIT                  
  FROM     dbo.Test_DiffNintyDays

However, I suspect you really meant this:

 SELECT     Attend_ID, 
            Attend_Date, 
            ID_Points, 
            Employee_ID, 
            First_Name, 
            Last_Name, 
            NextDate, 
            NEXT123, 
            Difference, 
            DAY90CREDIT, 
            CREDITDATE, 
            CASE 
                WHEN (day90Credit = 0 AND CreditDate < Getdate()) 
                  OR DateAdd(DAY, 90, attend_date) < COALESCE (NextDate, GETDATE()) 
                 AND ID_Points = 1 THEN 1
                WHEN (day90Credit = 0 AND CreditDate < Getdate()) 
                  OR DateAdd(DAY, 90, attend_date) < COALESCE (NextDate, GETDATE()) 
                 AND ID_Points = 2 THEN 0.5 
                WHEN (day90Credit = 0 AND CreditDate < Getdate()) 
                  OR DateAdd(DAY, 90, attend_date) < COALESCE (NextDate, GETDATE()) 
                 AND ID_Points = 3 THEN 0.25
                ELSE 0 END AS TOTALCREDIT                  
  FROM     dbo.Test_DiffNintyDays

Upvotes: 3

Related Questions