Query Problem - 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 2000 Forums

SQL Server Development (2000)

Query Problem

Author

Topic

jdelarco
Starting Member

8 Posts

Posted - 2007-12-05 : 14:22:07

I need to select the greatest date from six different fields in each record and identify from which column was selected

date_1 date_2 date_3 date_4 date_5
------ ------ ------ ------ ------
010190 010207 010190 010190 010190
010190 010190 010305 010190 010190
010190 010504 010190 010190 010190

1)if greatest is in date_1 then S = 1
2)if greatest is in date_2 then S = 2
3)if greatest is in date_3 then S = 3
4)if greatest is in date_4 then S = 4
5)if greatest is in date_5 then S = 5

Do you have any idea on how to acomplish this?

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-05 : 14:41:34

Here is onw way (which assumes your dates are datetime datatype):


create table #yourTable
	(rowid int identity
	,date_1 datetime
	,date_2 datetime
	,date_3 datetime
	,date_4 datetime
	,date_5 datetime)

insert #yourTable 
	(date_1
	,date_2 
	,date_3 
	,date_4 
	,date_5 )
select	'2007-01-02', '2007-01-01', '2007-01-05', '2007-01-04', '2007-01-03' union all
select	'2007-01-05', '2007-01-03', '2007-01-01', '2007-01-04', '2007-01-02' 

select	rowid
	,right(max( convert(varchar(23), dt, 121) + col), 6) source
	,max(dt) maxDate
from	(
	select	rowid
		,col
		,case 
			when cr.col = 'date_1' then date_1
			when cr.col = 'date_2' then date_2
			when cr.col = 'date_3' then date_3
			when cr.col = 'date_4' then date_4
			when cr.col = 'date_5' then date_5
			end as dt
	from	(select	'date_1' col 
		union	select 'date_2' 
		union	select 'date_3'
		union	select 'date_4'
		union	select 'date_5') cr
	cross join #yourTable
	) a
group by rowid

drop table #yourTable

output:
rowid       source maxDate                                                
----------- ------ ------------------------------------------------------ 
1           date_3 2007-01-05 00:00:00.000
2           date_1 2007-01-05 00:00:00.000

EDIT:
another (better) way is to normalize your design

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 14:42:05

[code]DECLARE @Sample TABLE (date_1 DATETIME, date_2 DATETIME, date_3 DATETIME, date_4 DATETIME, date_5 DATETIME)

INSERT @Sample
SELECT '19900101', '20070201', '19900101', '19900101', '19900101' UNION ALL
SELECT '19900101', '19900101', '20050301', '19900101', '19900101' UNION ALL
SELECT '19900101', '20040501', '19900101', '19900101', '19900101'

SELECT date_1, date_2, date_3, date_4, date_5,
(
SELECT TOP 1 col
FROM (
SELECT 1 AS col, date_1 AS value UNION ALL
SELECT 2, date_2 UNION ALL
SELECT 3, date_3 UNION ALL
SELECT 4, date_4 UNION ALL
SELECT 5, date_5
) AS d
ORDER BY value DESC
) AS LatestDateCol
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 14:50:20

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources