Simple sql query... - SQL Server Forums

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

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

Indigo121
Starting Member

4 Posts

Posted - 2014-08-05 : 06:53:59

Thanks, it worked. Indeed not that simple it seems.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources