How to check if column has value if another column is populated in SQL Server?

I am trying to find a way to see if I can write a query to check a table to return any row that is missing a dependency if that makes sense. For example let’s say I have the following columns:

EffectiveDate    Change1    Change2    Change3

If EffectiveDate has a value, then either Change1, Change2, or Change3 must also have a value since EffectiveDate has a value. Also vice versa, if columns Change1, Change2, or Change3 have a value, then EffectiveDate must have a value.

I need the query to return any rows where it doesn’t meet the criteria above and show the columns as NULL so that I know which records to go in to fix any missing values.

So far I’ve only got the below, it’s not much but I can’t seem to put the logic together from here. I assume I will need nested CASE statements?:

SELECT employee, 
    EffectiveDate, 
    Change1, 
    Change2, 
    Change3, 
    CASE WHEN EffectiveDate IS NOT NULL OR EffectiveDate != '' 
    THEN ...
FROM table1

Answer

To directly answer your question I think this is a clean way to get the results you want.

SELECT EffectiveDate,
   Change1,
   Change2,
   Change3 
FROM dbo.Table1
WHERE (EffectiveDate IS NULL AND (Change1 IS NOT NULL OR Change2 IS NOT NULL OR 
Change3 IS NOT NULL))
OR (EffectiveDate IS NOT NULL AND (Change1 IS NULL AND Change2 IS NULL AND Change3 IS 
NULL))

Advertisement

Answer