Geek Answers Handbook

SQL Server Returns Null if Exists

In SQL Server 2008, I'm looking to create a query that will return NULL in the aggregate, if one exists, otherwise I'm looking for the maximum. This is a simplified example ...

I have the following data:

CO Loc Term_Dt 1 A 7/15/2013 1 B 1 C 10/30/2000 2 A 8/10/2008 2 B 6/1/2015 2 C 4/30/2010

As a result, I am looking for:

  CO Term_Dt 1 NULL 2 6/1/2015

since technically the Company still remains open if at least one place has not yet been terminated.

thanks

+5
source share
2 answers

Just use aggregation and case :

 select co, (case when count(term_dt) = count(*) then max(term_dt) end) as term_dt from table t group by co;

count(<column>) counts the number of non- NULL values. If this does not match all lines, then at least one of them is NULL . case does not require else , because the default is NULL .

+5
source

Create a subset of the data with companies with zero deadlines and leaving them in your super set. Any entries in the 2nd table that are not null, you want to display as null, so use the case statement.

This works because our external table (A) returns

 CO TERM_DT 1 7/15/2013 2 6/1/2015

But then our LEFT connection in our inline view also adds B.Co ...

 CO TERM_DT B.CO 1 7/15/2013 1 2 6/1/2015 NULL

So you can see that we want to display NULL when B.CO is not null and not max (TERM_DT), will give the desired results. This is done using the case statement.

 SELECT A.Co, Case when B.CO is not null then Max(A.Term_DT) else NULL end as Term_DT FROM tableName A LEFT JOIN (SELECT Distinct CO from tableName where Term_dt is null) B on A.Co = B.CO GROUP BY CO
+1
source

More articles:


All Articles