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

User · Answer

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.

User · Answer

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

User · Answer

I try to use a union to combine two queries to format the returns you want   SELECT recordid  startdate  enddate FROM tmp  Where enddate is null  UNION  SELECT recordid  MIN startdate   MAX enddate  FROM tmp GROUP BY recordid  But I have no idea if the Union would have great impact on the performance

User · Answer

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

User · Answer

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

User · Answer

Use the analytic function    select case when      max field  keep  dense rank first order by datfin desc nulls first  is null then 1      else 0 end as flag  from MYTABLE

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql