Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 MIN date without displaying the NULL value

Author  Topic 

Petronas
Posting Yak Master

134 Posts

Posted - 2013-09-24 : 10:30:28
Hello,

I have 3 dates one of them could be NULL . I do not want my min to display it as NULL.Below is my script. Is there a way I could do it? Appreciate any help

Thanks,
petronas

select Order_id, customer_id, date_1, date_2, date_3
into #temp1
from ##Temp_dates (nolock)


Select Case when (date_1 < date_2 and date_1< date_3) then date_1
when (date_2< date_1 and date_2< date_3) then date_2
when (date_3< date_1 and date_3< date_2) then date_3
end as min_date
from #temp1
where date_1 is not NULL
and date_2 is not NULL
and date_3 is NOT NULL


James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-24 : 11:20:38
Use ISNULL or COALESCE with a large value out in the future like shown below:
Select Case 
when (COALESCE(date_1,'20990101') < COALESCE(date_2,'20990101')
and COALESCE(date_1,'20990101')< COALESCE(date_3,'20990101')) then date_1
when (COALESCE(date_2,'20990101')< COALESCE(date_1,'20990101')
and COALESCE(date_2,'20990101')< COALESCE(date_3,'20990101')) then date_2
when (COALESCE(date_3,'20990101')< COALESCE(date_1,'20990101')
and COALESCE(date_3,'20990101')< COALESCE(date_2,'20990101')) then date_3
end as min_date
from #temp1
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-24 : 15:23:59
[code]SELECT t1.*
f.minDate
FROM #Temp1 AS t1
CROSS APPLY (
SELECT MIN(theDate)
FROM (
VALUES (date_1),
(date_2),
(date_3)
) AS d(theDate)
WHERE theDate IS NOT NULL
) AS f(minDate);[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-24 : 18:22:52
The preceding post is good enough but If you need a solution that work for any SQL Server version you can use this alternative code:

Select (select min(v) from (select date_1 union select date_2 union select date_3)d(v)) as min_date
from #temp1
where date_1 is not NULL
and date_2 is not NULL
and date_3 is NOT NULL
Go to Top of Page

Petronas
Posting Yak Master

134 Posts

Posted - 2013-10-01 : 14:32:22
Thank you so much for the solutions. I tried all the above and they worked wonderfully! I apologize for the late response . I was out sick for the past week . Appreciate your time and help .
Go to Top of Page
   

- Advertisement -