Query to get max date from different fields

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 2008 Forums

Transact-SQL (2008)

Query to get max date from different fields

Author

Topic

PeeJay2583
Starting Member

40 Posts

Posted - 2012-07-17 : 03:28:49

Hi,

I have a table where there are 6 fields.

ID fld1 fld2 fld3 fld4 fld5
1 04/10/10 05/12/12 NULL NULL NULL
2 03/1/09 NULL NULL 12/01/12 NULL

The result I am looking for is max date from all five fields
ID Max Date
1 05/12/12
2 12/01/12

Any help will be appreciated..Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-17 : 04:21:47

;with cte as
(
select id, fld1 from #MaxDate
union all
select id, fld2 from #MaxDate
union all
select id, fld3 from #MaxDate
union all
select id, fld4 from #MaxDate
union all
select id, fld5 from #MaxDate
)
select id, max(fld1) from cte group by id

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PeeJay2583
Starting Member

40 Posts

Posted - 2012-07-17 : 05:00:53

This query is giving the date of fld 2 where fld2 date is greater than fld 2 and not checking dates with fld 3,fld 4 and fld 5 where in fld 5 date is the max

PeeJay2583
Starting Member

40 Posts

Posted - 2012-07-17 : 05:11:33

Nigel,

Your query is giving the result i was looking for.

Thank you both Lion and Nigel.

Great help!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-17 : 05:35:04

lol :).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources