How can I include null values in a MIN or MAX?

Question

I have a table where I am storing timespan data. the table has a schema similar to:

ID INT NOT NULL IDENTITY(1,1)    RecordID INT NOT NULL   StartDate DATE NOT NULL   EndDate DATE NULL   

And I am trying to work out the start and end dates for each record id, so the minimum StartDate and maximum EndDate. StartDate is not nullable so I don't need to worry about this but I need the MAX(EndDate) to signify that this is currently a running timespan.

It is important that I maintain the NULL value of the EndDate and treat this as the maximum value.

The most simple attempt (below) doesn't work highlighting the problem that MIN and MAX will ignore NULLS (source: http://technet.microsoft.com/en-us/library/ms179916.aspx).

SELECT recordid, MIN(startdate), MAX(enddate) FROM tmp GROUP BY recordid 

I have created an SQL Fiddle with the basic setup done.

http://sqlfiddle.com/#!3/b0a75

How can I bend SQL Server 2008 to my will to produce the following result from the data given in the SQLFiddle?

RecordId  Start       End   1         2009-06-19  NULL 2         2012-05-06  NULL 3         2013-01-25  NULL 4         2004-05-06  2009-12-01 

Answer 1

It's a bit ugly but because the NULLs have a special meaning to you, this is the cleanest way I can think to do it:

SELECT recordid, MIN(startdate),    CASE WHEN MAX(CASE WHEN enddate IS NULL THEN 1 ELSE 0 END) = 0         THEN MAX(enddate)    END FROM tmp GROUP BY recordid 

That is, if any row has a NULL, we want to force that to be the answer. Only if no rows contain a NULL should we return the MIN (or MAX).