CODEWITHSUNDEEP
sqlsql-serverdatabase
jwalls91
jwalls91

Reputation: 351

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

Upvotes: 0

Views: 3669

Answers (4)

superztnt
superztnt

Reputation: 51

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))

Upvotes: 1

Andrew HB
Andrew HB

Reputation: 412

Ok just read it again, sorry on my phone but something like this...

Select * From #DataTable Where EffectiveDate Is Not Null And (Change1 Is Null Or Change2 Is Null Or Change3 Is Null) Or (Change1 Is Not Null And EffectiveDate Is Null) Or (Change2 Is Not Null And EffectiveDate Is Null) Or (Change3 Is Not Null And EffectiveDate Is Null)

Upvotes: 1

Andrew HB
Andrew HB

Reputation: 412

Not quite sure what you're asking for to be honest could you supply some example datasets this may make things clearer on what you're trying to do?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You seem to want a check constraint:

alter table table1 add constraint chk_effectivedate_changes
    check ( (EffectiveDate is null and Change1 is null and   Change2 is null and Change3 is null) or
            (EffectiveDate is not null and (Change1 is not null or Change2 is not null or Change3 is not null)
            )
          );

Upvotes: 1

Related Questions