MaxPD MaxPD - 7 months ago 11
SQL Question

Create new column based on values of another column

I'm new to SQL, I use R most of the times. However the requirement is I fulfil this in SQL


I have a table in MS SQL where each row is one 'Activity Type' which has a value of either 'A','B' or 'C'. I have a total of 40 columns and I need to check every row if the data is correct. A small example below has few columns.

The rule is that if the Activity Type is 'A' then all the columns that are

MonitorA_
should have a value, everything else should be NULL. If Activity type is 'B' then all the MonitorB_ columns should have a value, rest all should be NULL


In the example below, row 1 and row 2 are fine. But row 3 and row 4 have problems. That is in row 3, Activity type is 'B' but there is a value in the column 'MonitorC_ID'.

I want to be able to create a new column with a value of True or False, depending on whether it passed the test.

| Activity Type | MonitorA_ID | MonitorA_Date | MonitorA_Region | MonitorB_ID | MonitorB_Date | MonitorB_Region | MonitorC_ID | MonitorC_Date | MonitorC_Region |
|---------------|-------------|---------------|-----------------|-------------|---------------|-----------------|-------------|---------------|-----------------|
| B | NULL | NULL | NULL | 1 | 21/10/1985 | Brisbane | NULL | NULL | NULL |
| A | 45 | 25/03/1986 | Gold Coast | NULL | NULL | NULL | NULL | NULL | NULL |
| B | NULL | NULL | NULL | 67 | 13/03/1959 | Dubai | 45 | NULL | NULL |
| C | NULL | 13/08/1964 | NULL | NULL | NULL | NULL | 82 | 13/08/1964 | Dubai |

Answer

You can create a check variable for each type of column A, B, or C. This would give you 3 new fields A_Chk, B_Chk, and C_Chk. If they are 0, the row is fine but if they are a 1 then the row is bad.

To get to one field, you would just check for the SUM of the 3 chk fields to be zero.

Select
    CASE
        WHEN [Activity Type] = 'A' THEN
            CASE WHEN MonitorA_ID is NULL
              OR MonitorA_Date is NULL
              OR MonitorA_Region is NULL
            THEN 1 ELSE 0 END
    END as 'A_Chk'

From Table