KyloRen KyloRen - 4 months ago 16
SQL Question

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?

Answer

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.

Comments