SQL Server Return Null if exists

Question

In SQL Server 2008 I am looking to create a query that will return a NULL in an 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

The result I'm looking for is:

    CO  Term_Dt
    1   NULL
    2   6/1/2015

because technically the Company is still open if at least one location has not yet been terminated.

Thanks

Answer 1

Just use aggregation and a case statement:

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 doesn't match all the rows, then at least one is NULL. No else is needed for the case, because the default is NULL.

Answer 2

Generate a sub set of data with companies having null term dates and left join your super set to it. Any records in 2nd table which are not null you want to display as null so use a case statement.

This works because our outer table (A) returns

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

But then our LEFT join on 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 by saying we want to display NULL when B.CO is not null instead of the max(TERM_DT) will yield the desired results. This is accomplished using a 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