SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 MIN date without displaying the NULL value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Petronas
Posting Yak Master

133 Posts

Posted - 09/24/2013 :  10:30:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 09/24/2013 :  11:20:38  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 09/24/2013 :  15:23:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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);



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

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/24/2013 :  18:22:52  Show Profile  Reply with Quote
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

Edited by - sigmas on 09/24/2013 18:24:43
Go to Top of Page

Petronas
Posting Yak Master

133 Posts

Posted - 10/01/2013 :  14:32:22  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000