Max of date from multiple columns

Question

Post reply

Max of date from multiple columns

Roy Rogers

SSCertifiable

Points: 7712
More actions
June 14, 2005 at 10:01 am

#66615

DECLARE @MyTable TABLE
(
myVersion    VARCHAR(10),
LastDateA     DATETIME,
LastDateB     DATETIME,
LastDateC     DATETIME
)
INSERT @MyTable VALUES ('VERSION A', '01/01/2004', '02/01/2004', '01/03/2004')
INSERT @MyTable VALUES ('VERSION B', '03/01/2004', '02/04/2004', '01/09/2004')
INSERT @MyTable VALUES ('VERSION C', '04/01/2004', '04/09/2004', '01/31/2004')
The task is to get the latest myVersion. This should be for the date which is latest / maximum of all available 9 dates. In actual case there will be more or less rows.
Thanks friends
Added
I need the myVersion in the table which is for the lates of all 9 dates.
like this case lates date is 04/09/2004
So I need VERSION C as result
Regards,
gova

Viewing 15 posts - 1 through 15 (of 17 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

But more or less columns?

select max(mydate)

from (

select max(lastdatea) 'mydate'

from MyTable

union

select max(lastdateb) 'mydate'

from MyTable

union

select max(lastdatec) 'mydate'

from MyTable

) a

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 2

Thanks Steve. That is simple.

But how do I get my Version for that date.

Regards,
gova

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

As always, can you provide the desired out from this input data?

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 4

I need the myVersion in the table which is for the lates of all 9 dates.

like this case lates date is 04/09/2004

So I need VERSION C as result

Regards,
gova

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 5

Let me see if I understand the data you have.

You have an object and at least three versions of This object

Version A, Version B, Version C

Each Version was modified on more that one occasion

for example a software program that received minor updates

So there is a table like

Table Software

(

Version varchar(10)

DateModified datetime

...other stuff )

Data for software would look like

Version A, '1/1/00'

Version A, '1/1/01'

Version B, '2/1/01'--new version was released

Version A, '1/1/02 --old version (version A ) was updated after the release of version b

Mike

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 6

I modified Steeve's query and got the result. Is there any better way to do it

Added - I am not sure this gives the correct value all the times

select myVersion, max(mydate)

from (

select myVersion, max(lastdatea) 'mydate'

from @MyTable group by myVersion

union

select myVersion, max(lastdateb) 'mydate'

from @MyTable group by myVersion

union

select myVersion, max(lastdatec) 'mydate'

from @MyTable group by myVersion) a

group by myVersion

having max(mydate) = (

select max(mydate)

from (

select myVersion, max(lastdatea) 'mydate'

from @MyTable group by myVersion

union

select myVersion, max(lastdateb) 'mydate'

from @MyTable group by myVersion

union

select myVersion, max(lastdatec) 'mydate'

from @MyTable group by myVersion) b)

Regards,
gova

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 7

Thanks Mike.

We have a table with versions and updates from various sources. Each source will update the version and their date. Example if Source A updates the version it will update the version and myDateA and so.

If they leave the rest of the dates null that would be great. But they try up date MyDateB and myDatec with some(Latest dates from previous rows) dates and they are not correct. (or atlease they could have provided a sourceID so that a CASE function can be used) We can't touch that process.

This case we can assume

Version A updated bmy SourceB on 02/01/2004

Version B updated bmy SourceA on 03/01/2004

Version C updated bmy SourceB on 04/09/2004

The logic is get the latest version based on all of these dates. Get the lates of all the dates in 3 columns and match the version with that date.

Regards,
gova

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 8

Would it be possible to touch the person who developed the process. I would suggest a very heavy rock droped from a height of 10 feet to touch him on his foot.

At this time I do not see a way to do this other than the way you are doing it. And much to my dismay the people who provide me with the little things in life like a paycheck actually want me to do something for it and I do not have time to try and find a better answer. But never fear thare are some very good people on this list and they may be able to find a better way.

Mike

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 9

Yes I would love to do that.

I made the query little better.

select TOP 1 myVersion, max(mydate)

from (

select myVersion, max(lastdatea) 'mydate'

from @MyTable group by myVersion

union

select myVersion, max(lastdateb) 'mydate'

from @MyTable group by myVersion

union

select myVersion, max(lastdatec) 'mydate'

from @MyTable group by myVersion) a

group by myVersion

ORDER BY max(mydate) DESC

Regards,
gova

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 10

A little more better...

DECLARE @MyTable TABLE

(

myVersion VARCHAR(10),

LastDateA DATETIME,

LastDateB DATETIME,

LastDateC DATETIME

)

INSERT @MyTable VALUES ('VERSION A', '01/01/2004', '02/01/2004', '01/03/2004')

INSERT @MyTable VALUES ('VERSION A', '03/01/2004', '02/04/2004', '01/09/2004')

INSERT @MyTable VALUES ('VERSION A', '04/01/2004', '04/09/2004', '01/31/2004')

SELECT TOP 1

myVersion,

Case

WHEN a>b THEN

CASE

WHEN a>c THEN a

ELSE c

END

ELSE

CASE

WHEN b>c THEN b

ELSE c

END

END LastDate

FROM

(SELECT myVersion, MAX(lastdatea) a, MAX(lastdateb) b, MAX(lastdatec) c

FROM @MyTable GROUP BY myVersion) AA

ORDER BY LastDate DESC

Kindest Regards,

Vasc

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 11

Thanks vasc. Sure it is liitle more better.

But in case if I am asked to add another column to check(There is another field in the table) the case statement will get little more complicted.

Regards,
gova

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 12

1 Extra column can change the aproach totally...

But I would say to stick to a more complicated CASE than 3 table scans...

SELECT TOP 1

myVersion,

Case

WHEN lastdatea>lastdateb THEN

CASE

WHEN lastdatea>lastdatec THEN lastdatea

ELSE lastdatec

END

ELSE

CASE

WHEN lastdateb>lastdatec THEN lastdateb

ELSE lastdatec

END

END LastDate

--,rest of columns

FROM @MyTable

ORDER BY LastDate DESC

Kindest Regards,

Vasc

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 13

I agree. But in this case any criteria will not give more than few rows. I checked the entire table and maximum rows now is 3. It is like each time they update the version they update the version and their date. So maximum it will be 5 rows for 5 sources(2 sources are not updating now). so table scans are not that expensive here. Case will be nested too much.

If we have a rowwise max this will be a piece of cake. How about a function that returns max(rowwise date) if we pass the primary key.

Thanks

Regards,
gova

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 14

For 5 rows... you can pick wich plan you want...

PS: the prev plan returns the full row from your table

Kindest Regards,

Vasc