CODEWITHSUNDEEP
sqlsql-server
KyloRen
KyloRen

Reputation: 2741

Nesting Case statements for different Fields SQL Server

I have a case statement that checks several criteria to enter data into a specific field named Column_1.

Being that you can't amend two fields within the same case statement, I feel the best choice is to nest another case statement to amend the second field.

My code is as follows.

UPDATE Staff_Manager.dbo.Staff_Time_TBL        
                SET Column_1 = CASE        
                     WHEN Column_1 != NULL AND Time_Data_1 IS NOT NULL AND Time_Data_2 IS NOT NULL THEN 'Day'        
                     WHEN Time_Data_1 IS NULL AND Time_Data_2 IS NULL THEN 'Hol'                                            
                ELSE 
                   SET Column_2 = CASE 
                     WHEN Time_Data_3 IS NULL THEN 'Something'
                   ELSE NULL
                   END         
                END               
             WHERE Staff_No = 1903 AND Date_Data BETWEEN '2016/2/1' AND '2016/3/1'

So in the above code, when the criteria for column_1 throws the ELSE , I want a different column Column_2 to be tested for new conditions.

How do you write in the second case statement?

Upvotes: 0

Views: 298

Answers (3)

yatin parab
yatin parab

Reputation: 174

Try This,

UPDATE Staff_Manager.dbo.Staff_Time_TBL        
        SET Column_1 = (CASE        
             WHEN Column_1 is not NULL AND Time_Data_1 IS NOT NULL AND Time_Data_2 IS NOT NULL THEN 'Day'        
             WHEN Time_Data_1 IS NULL AND Time_Data_2 IS NULL THEN 'Hol'                                            
             ELSE Column_1 end)
           ,Column_2 = case when (CASE        
             WHEN Column_1 is not NULL AND Time_Data_1 IS NOT NULL AND Time_Data_2 IS NOT NULL THEN 'Day'        
             WHEN Time_Data_1 IS NULL AND Time_Data_2 IS NULL THEN 'Hol' else 'Error' end)='Error' and 
             Time_Data_3 IS NULL THEN 'Something' ELSE Column_2 --(Here you have to handle else condition as per requirement)
           END                    
     WHERE Staff_No = 1903 AND Date_Data BETWEEN '2016/2/1' AND '2016/3/1'

Upvotes: 0

Esperento57
Esperento57

Reputation: 17472

You have an error in your case, try this:

           UPDATE Staff_Manager.dbo.Staff_Time_TBL        
            SET Column_1 = CASE        
                 WHEN Column_1 is not NULL AND Time_Data_1 IS NOT NULL AND  Time_Data_2 IS NOT NULL THEN 'Day'        
                 WHEN Time_Data_1 IS NULL AND Time_Data_2 IS NULL THEN 'Hol' 
                 else Column_1  end,                                           
                Column_2= CASE 
                 WHEN Column_1 is not NULL AND Time_Data_1 IS NOT NULL AND Time_Data_2 IS NOT NULL THEN Column_2       
                 WHEN Time_Data_1 IS NULL AND Time_Data_2 IS NULL THEN Column_2
                 WHEN Time_Data_3 IS NULL THEN 'Something'
                 ELSE NULL
               END         

         WHERE Staff_No = 1903 AND Date_Data BETWEEN '2016/2/1' AND '2016/3/1'

Upvotes: 2

Abecee
Abecee

Reputation: 2393

This should get you started:

UPDATE Staff_Manager.dbo.Staff_Time_TBL
  SET Column_1 = CASE
     WHEN Column_1 IS NOT NULL AND Time_Data_1 IS NOT NULL AND Time_Data_2 IS NOT NULL THEN 'Day'
     WHEN Time_Data_1 IS NULL AND Time_Data_2 IS NULL THEN 'Hol'
   ELSE Column_1
   END,
   Column_2 = CASE
     WHEN NOT (Column_1 IS NOT NULL AND Time_Data_1 IS NOT NULL AND Time_Data_2 IS NOT NULL
               AND
               Time_Data_1 IS NULL AND Time_Data_2 IS NULL)
          AND
          Time_Data_3 IS NULL
     THEN 'Something'
     END

The overall idea is:

  1. actually finish the update to Column_1,
  2. move on to Column_2:
    • repeat the test for Column_1 (or rather their respective and combined opposite) (This part might need some twisting to get the logic right.), and
    • add the additional test for Column_2.

Please comment if and as this requires adjustment / further detail.

Upvotes: 1

Related Questions