Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Simple sql query...

Author  Topic 

Indigo121
Starting Member

4 Posts

Posted - 2014-08-04 : 09:01:54
This is probably simple, but I'm no SQL master. I need a function that checks whether the following is true for a specific table :

Lets say I have columns A,B,C

then- if all A are non-null and all B are null, return 'A'
else- if all B are non-null and all C are null, return 'B'...

thanks

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-08-04 : 09:36:19
[code]select case when coalesce(a, b) = a then a
when coalesce(b, c) = b then b
else null
end[/code]

This has not been tested but should at least get you started.


djj
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-08-04 : 11:53:28
That's actually not that simple.



SELECT
CASE
WHEN A_NULL_Count = 0 AND B_NULL_Count = Row_Count THEN 'A'
WHEN B_NULL_Count = 0 AND C_NULL_Count = Row_Count THEN 'B'
--WHEN ...
END AS Final_Result
FROM (
SELECT
SUM(CASE WHEN A IS NULL THEN 1 ELSE 0 END) AS A_NULL_Count,
SUM(CASE WHEN B IS NULL THEN 1 ELSE 0 END) AS B_NULL_Count,
SUM(CASE WHEN C IS NULL THEN 1 ELSE 0 END) AS C_NULL_Count,
SUM(1) AS Row_Count
FROM dbo.tablename
) AS derived

Go to Top of Page

Indigo121
Starting Member

4 Posts

Posted - 2014-08-05 : 06:53:59
Thanks, it worked. Indeed not that simple it seems.
Go to Top of Page
   

- Advertisement -