CODEWITHSUNDEEP
sqlsql-serveraggregate-functionscoalesceisnull
shockwave
shockwave

Reputation: 3272

SQL MIN of multiple columns handle null values

I'm trying to use MIN() aggregate function and fetch the minimum date from two columns and I was able to write the SQL query for this. But If one of the columns is having NULL values my query below is taking default date as '1900-01-01T00:00:00Z'. It should take the date from either Column1 or Column2 whichever has a value.

Here is the schema and the data SQLFiddle

+----+--------------+---------------+
| ID | ObservedDate | SubmittedDate |
+----+--------------+---------------+
|  1 | '2017-02-14' | '2017-02-15'  |
|  1 | '2017-01-21' | '2017-01-22'  |
|  2 | '2017-01-21' |               |
+----+--------------+---------------+

Query

SELECT [ID],
        CASE WHEN MIN(ObservedDate)<=MIN(SubmittedDate) 
             THEN COALESCE(MIN(ObservedDate),MIN(SubmittedDate))
        ELSE COALESCE(MIN(SubmittedDate),MIN(ObservedDate)) end as RiskReferenceDate
FROM Measurements
group by ID

The reason I used COALESCE is because I want my query to consider the data from the column which has the value and ignore the column which has null value

Expected Result

+----+-------------------+
| ID | RiskReferenceDate |
+----+-------------------+
|  1 | '2017-01-21'      |
|  2 | '2017-01-21'      |
+----+-------------------+

Upvotes: 2

Views: 3662

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Your problem is not NULL values. Your problem is empty strings. This is inserted as date 0.

The simplest solution is to fix your code to insert the correct value, as shown in this SQL Fiddle.

You can enforce this by adding a check constraint:

alter table Measurements add constraint chk_measurements_ObservedDate check (ObservedDate > '2000-01-01');  -- or whatever date

alter table Measurements add constraint chk_measurements_SubmittedDate check (SubmittedDate > '2000-01-01');  -- or whatever date

If you have existing data in the table, you can do:

update Measurements
    set ObservedDate = NULLIF(ObservedDate, 0),
        SubmittedDate = NULLIF(SubmittedDate, 0)
    where ObservedDate = 0 or SubmittedDate = 0;

You can fix this in place with a bit more complexity in the query:

SELECT [ID],
       (CASE WHEN MIN(NULLIF(ObservedDate, 0)) <= MIN(NULLIF(SubmittedDate, 0)) 
             THEN COALESCE(MIN(NULLIF(ObservedDate, 0)), MIN(NULLIF(SubmittedDate, 0)))
             ELSE COALESCE(MIN(NULLIF(SubmittedDate, 0)), MIN(NULLIF(ObservedDate, 0)))
        END) as RiskReferenceDate
FROM Measurements
GROUP BY ID;

But I strongly urge you to fix the data.

Upvotes: 1

MJH
MJH

Reputation: 1750

I think the problem is being caused by the empty string you have inserted into one of your date columns, you should fix that really.

Anyway, this seems to work:

with a as (
  select   ObservedDate Dt
  from     Measurements
  where ObservedDate <> ''
  union all
  select   SubmittedDate
  from     Measurements
  where SubmittedDate <> ''
)
select min(Dt)
from a

Upvotes: 0

Related Questions