Extracting the most recent date..

Question

Post reply

Extracting the most recent date..

Mark Kinnear

SSC Eights!

Points: 850
More actions
December 21, 2009 at 12:37 pm

#99977

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Mark Kinnear (12/21/2009)
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.

Based on the above, what should the result of a successful query look like?

Mark Kinnear SSC Eights! Points: 850 More actions · Answer 3

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.;-)

Mark Kinnear SSC Eights! Points: 850 More actions · Answer 4

Thanx for the reply GSquared, im not at work at the moment.

Ill try it when i get in.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Try this:

select

ID,

case when isnull(Date1,0) < isnull(Date2,0) then Date2 else Date1 end as TheDate

from

dbo.mytable;

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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! 😉