Finding Max date - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Finding Max date

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-18 : 10:28:17

Dear gurus,

Here I have four dates and one prodid.
While Selecting I have to find out the maximum date from the four dates for the corresponding Prodid
(ie)

Select prodid,<If OrderDt1>OrderDt2 Then OrderDt1 if OrderDt1
>OrderDt3 then OrderDt1 if OrderDt1 >OrderDt4 then OrderDt1)

from tbl_ProductOrder (Nolock)
Where ProdID = 'AZ34589'

I have tried in case but i cant go forward Can any one help me please ?

real data
OrderDt1 OrderDt2 OrderDt3 OrderDt4 ProdID
12/12/2004 12/17/2004 10/17/2005 01/07/2006 AZ34589
12/12/2004 12/17/2004 10/17/2005 01/07/2006 AZ75691
12/12/2004 12/17/2004 10/17/2005 01/07/2006 AZ89964
12/12/2004 12/17/2004 10/17/2005 01/07/2006 AZ89998
12/12/2004 12/17/2004 10/17/2005 01/07/2006 AZ91364

Output will be:

01/07/2006 AZ34589

Thanks
Krishna

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-18 : 11:03:27

Gurus Can any one help me..?

Thanks
Krishna

bg_elliott
Starting Member

2 Posts

Posted - 2007-10-18 : 11:14:21

Do you have to do this all in one statement? If not, just put them in a temp table or table variable and select max(date) from there?

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-18 : 11:16:59

Bg, Thanks for your reply. I want to do it in single statement.

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-18 : 11:30:37

[code]
CREATE TABLE myTable99(
OrderDt1 datetime
, OrderDt2 datetime
, OrderDt3 datetime
, OrderDt4 datetime
, ProdID varchar(20)
)
GO

INSERT INTO myTable99(OrderDt1, OrderDt2, OrderDt3, OrderDt4, ProdID)
SELECT '12/12/2004', '12/17/2004', '10/17/2005', '01/07/2006', 'AZ34589' UNION ALL
SELECT '12/12/2004', '12/17/2004', '10/17/2005', '01/07/2006', 'AZ75691' UNION ALL
SELECT '12/12/2004', '12/17/2004', '10/17/2005', '01/07/2006', 'AZ89964' UNION ALL
SELECT '12/12/2004', '12/17/2004', '10/17/2005', '01/07/2006', 'AZ89998' UNION ALL
SELECT '12/12/2004', '12/17/2004', '10/17/2005', '01/07/2006', 'AZ91364'
GO

SELECT ProdID, MAX(OrderDt)
FROM (
SELECT ProdID, OrderDt1 AS OrderDt
FROM myTable99
UNION ALL
SELECT ProdID, OrderDt2 AS OrderDt
FROM myTable99
UNION ALL
SELECT ProdID, OrderDt3 AS OrderDt
FROM myTable99
UNION ALL
SELECT ProdID, OrderDt4 AS OrderDt
FROM myTable99)AS XXX
GROUP BY ProdID
GO

DROP TABLE myTable99
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-10-18 : 11:32:29

Take a look at this topic. It shows a couple of ways to do what you want.

MIN/MAX Across Multiple Columns
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

CODO ERGO SUM

tm
Posting Yak Master

160 Posts

Posted - 2007-10-18 : 11:33:41

Not sure if this is most efficient but you can use CASE statement ..

Select prodid,

CASE
WHEN OrderDt1 > OrderDt2 AND OrderDt1 > OrderDt3 AND OrderDt1 > OrderDt4 THEN OrderDt1
WHEN OrderDt2 > OrderDt3 AND OrderDt2 > OrderDt4 THEN OrderDt2
WHEN OrderDt3 > OrderDt4 THEN OrderDt3
ELSE OrderDt4
END

from tbl_ProductOrder (Nolock)
Where ProdID = 'AZ34589'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-10-18 : 11:41:38

quote:
Originally posted by tm

Not sure if this is most efficient but you can use CASE statement ..

Select prodid,

CASE
WHEN OrderDt1 > OrderDt2 AND OrderDt1 > OrderDt3 AND OrderDt1 > OrderDt4 THEN OrderDt1
WHEN OrderDt2 > OrderDt3 AND OrderDt2 > OrderDt4 THEN OrderDt2
WHEN OrderDt3 > OrderDt4 THEN OrderDt3
ELSE OrderDt4
END

from tbl_ProductOrder (Nolock)
Where ProdID = 'AZ34589'

It is quite a bit more complicated to do correctly with a case if the OrderDt columns are nullable.


select
	a.prodid,
   	[Max_of_OrderDt1_to_OrderDt4] =  
	 	case
	 	when  a.OrderDt1 is not null   and
	  		(a.OrderDt1 >= a.OrderDt2 or a.OrderDt2 is null) and
	  		(a.OrderDt1 >= a.OrderDt3 or a.OrderDt3 is null) and
	  		(a.OrderDt1 >= a.OrderDt4 or a.OrderDt4 is null)
	 	then 	a.OrderDt1
	 	when  	a.OrderDt2 is not null   and
	  		(a.OrderDt2 >= a.OrderDt1 or a.OrderDt1 is null) and
	  		(a.OrderDt2 >= a.OrderDt3 or a.OrderDt3 is null) and
	  		(a.OrderDt2 >= a.OrderDt4 or a.OrderDt4 is null)
	 	then 	a.OrderDt2
	 	when  	a.OrderDt3 is not null   and
	  		(a.OrderDt3 >= a.OrderDt1 or a.OrderDt1 is null) and
	  		(a.OrderDt3 >= a.OrderDt2 or a.OrderDt2 is null) and
	  		(a.OrderDt3 >= a.OrderDt4 or a.OrderDt4 is null)
	 	then 	a.OrderDt3
	 	when  	a.OrderDt4 is not null   and
	  		(a.OrderDt4 >= a.OrderDt1 or a.OrderDt1 is null) and
	  		(a.OrderDt4 >= a.OrderDt2 or a.OrderDt2 is null) and
	  		(a.OrderDt4 >= a.OrderDt3 or a.OrderDt3 is null)
	 	then a.OrderDt4
	 	else null
	 	end
from
	tbl_ProductOrder a
Where
	a.ProdID = 'AZ34589'

CODO ERGO SUM

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-18 : 11:41:56

Thanks gurus,I got the solution. Now I am in the right direction.

Thannks for your timely help!

Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources