December 21, 2009 at 12:37 pm
Hi guys
Im trying to find the fastest way to determine the most recent date between 2 fields and extract that one. Here's an example.
mytable
ID Date1 Date2
A 2009/12/01 2008/01/01
B 2007/03/12 NULL
C 2009/12/21 2009/12/21
D 2009/10/15 2009/11/02
E NULL 2008/09/04
F 2007/12/03 2009/01/04
The most recent date may be in either field, both date fields will never be NULL at the same time. Every attempt i make i keep getting stuck with long drawn out queries. The table im drawing the data from has about 45 million records in it.
Thanx in advance.
December 21, 2009 at 12:41 pm
Does this do what you need?
;with Dates (Date) as
(select Date1
from dbo.MyTable
union all
select Date2
from dbo.MyTable)
select max(Date)
from Dates;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 21, 2009 at 12:43 pm
Mark Kinnear (12/21/2009)
Hi guysIm trying to find the fastest way to determine the most recent date between 2 fields and extract that one. Here's an example.
mytableID Date1 Date2
A 2009/12/01 2008/01/01
B 2007/03/12 NULL
C 2009/12/21 2009/12/21
D 2009/10/15 2009/11/02
E NULL 2008/09/04
F 2007/12/03 2009/01/04
The most recent date may be in either field, both date fields will never be NULL at the same time. Every attempt i make i keep getting stuck with long drawn out queries. The table im drawing the data from has about 45 million records in it.
Thanx in advance.
Based on the above, what should the result of a successful query look like?
December 21, 2009 at 12:53 pm
MyDreamResultSet
ID Date
A 2009/12/01
B 2007/03/12
C 2009/12/21
D 2009/11/02
E 2008/09/04
F 2009/01/04
Thanx.;-)
December 21, 2009 at 12:57 pm
Thanx for the reply GSquared, im not at work at the moment.
Ill try it when i get in.
December 21, 2009 at 1:00 pm
Try this:
select
ID,
case when isnull(Date1,0) < isnull(Date2,0) then Date2 else Date1 end as TheDate
from
dbo.mytable;
December 21, 2009 at 1:09 pm
Given the updated information, the solution provided by Lynn is what you need.
Curse you Lynn for beating me to the punch again 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2009 at 1:20 pm
CirquedeSQLeil (12/21/2009)
Given the updated information, the solution provided by Lynn is what you need.Curse you Lynn for beating me to the punch again 😉
Join me you may, comfortable in the tent out in the desert! 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply