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

Solution 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).

Solution 2

The effect you want is to treat the NULL as the largest possible date then replace it with NULL again upon completion:

SELECT RecordId, MIN(StartDate), NULLIF(MAX(COALESCE(EndDate,'9999-12-31')),'9999-12-31') 
  FROM tmp GROUP BY RecordId

Per your fiddle this will return the exact results you specify under all conditions.

Solution 3

In my expression, count(enddate) counts how many rows where the enddate column is not null. The count(*) expression counts total rows. By comparing, you can easily tell if any value in the enddate column contains null. If they are identical, then max(enddate) is the result. Otherwise the case will default to returning null which is also the answer. This is a very popular way to do this exact check.

SELECT recordid, 
MIN(startdate), 
case when count(enddate) = count(*) then max(enddate) end
FROM tmp 
GROUP BY recordid

Solution 4

Use IsNull

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

I've modified MIN in the second instruction to MAX

Solution 5

Assuming you have only one record with null in EndDate column for a given RecordID, something like this should give you desired output :

WITH cte1 AS
(
SELECT recordid, MIN(startdate) as min_start , MAX(enddate) as max_end
FROM tmp 
GROUP BY recordid
)

SELECT a.recordid, a.min_start , 
CASE 
   WHEN b.recordid IS  NULL THEN a.max_end
END as max_end
FROM cte1 a
LEFT JOIN tmp b ON (b.recordid = a.recordid AND b.enddate IS NULL)

View more solutions

Comments

[whytheq]

don't understand - if I run SELECT recordid, MIN(startdate), Max(enddate) FROM tmp GROUP BY recordid then the null is preserved!

[whytheq]

- the result you show in OP doesn't seem to relate to the sqlfiddle schema?

[Ant Swift]

Take a look at sqlfiddle.com/#!3/565dc/29 for sample data that is valid. If only one record exists for a grouping, the NULL will be preserved, otherwise it will be overridden by any date.

[Ant Swift]

@whytheq, you right it does not. It is an example result.

[Ant Swift]

I've updated the question so the results are valid for the sample data.

[whytheq]

You also have a typo in the script MIN(startdate), MIN(enddate) should read MIN(startdate), MAX(enddate)

[t-clausen.dk]

I may have posted late, but please take a look at my answer anyway

[TPAKTOPA]

or even better, Coalesce, which is ansi-sql compatible

[Gordon Linoff]

+1 . . . But the OP really wants the maximum end date, despite the SQL ("so the minimum StartDate and maximum EndDate").

[Ant Swift]

Unfortunately I need to maintain the null as the high value. I've edited my question to be clearer on this.

[Ant Swift]

@GordonLinoff I actually want to maintain the NULL as the highest value. I have edited the question to clarify, thanks.

[t-clausen.dk]

the question is asking for max/min value of enddate. I fail to see how getdate can replace the lowest or the highest value in an unknown table it could be a calender table. I also fail to see the advanage of returning getdate instead of null.

[Ant Swift]

The sample was incorrect, stating MIN rather than MAX for the EndDate column.

[Ant Swift]

This does in fact work. It would be interesting to look if there was any performance difference over Damien_The_Unbeliever's answer.

[t-clausen.dk]

Not really a great answer. As you mention it require only 1 row with null column and the other answers are shorter and doesn't have prerequisites

[a1ex07]

@t-clausen.dk : 1 row with null restriction is easy to overcome - LEFT JOIN (SELECT DISTINCT recordid WHERE enddate is NULL)b. I wasn't sure I got the question right, so I didn't go that far... I agree, it's not the best solution, just wanted to show a bit different approach

[Ant Swift]

Passed my tests, including the new multiple nulls for a group even if this should never happen. sqlfiddle.com/#!3/2c45b/2

[jbg]

This is cleaner than the accepted answer IMO. I use a slight variation: NULLIF(MAX(COALESCE(EndDate, 'infinity'::timestamp)), 'infinity'::timestamp)

[Echilon]

No idea what dbms that's for, but the question was for SQL server.

[Devin Lamothe]

Note that in 8,000 years we'll need to review this code... but yeah, a lot cleaner than the accepted answer.

[J.Warren]

I believe @jbg's variation is PostgreSQL specific

[Tanckom]

Anyone looking for smallest date (i.e. start_date or similar), the smallest supported date in MySQL is 1000-01-01 Source