Max of date from multiple columns

Question

Max of date from multiple columns

Viewing 3 posts - 16 through 17 (of 17 total)

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

Cool. Updated query looks good. Thanks. I will try with my table. For now I have to make it for 4 since 4th source they say will be using this soon.

Added

I tried with case for 4 columns there are too many case, when and else and getting difficult to see what I am doing for another user. (May be to myself after few days). I just rolled back.

Regards,
gova

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 2

This is a more readable form

SELECT TOP 1

myVersion,

Case

WHEN lastdatea>=lastdateb AND lastdatea>=lastdatec AND lastdatea>=lastdated THEN lastdatea

WHEN lastdateb>=lastdatea AND lastdateb>=lastdatec AND lastdateb>=lastdated THEN lastdateb

WHEN lastdatec>=lastdatea AND lastdatec>=lastdateb AND lastdatec>=lastdated THEN lastdatec

ELSE lastdated

END LastDate

--,rest of columns

FROM @MyTable

ORDER BY LastDate DESC

Either way you wanna do it you have to comment ...

Kindest Regards,

Vasc

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

4th or 5th column can be easily added

SELECT TOP 1 myVersion, MAX(myDate) FROM

(

SELECT myVersion, LastDateA myDate FROM @MyTable -- WHERE CRITERIA

UNION

SELECT myVersion, LastDateB FROM @MyTable -- WHERE CRITERIA

UNION

SELECT myVersion, LastDateC FROM @MyTable -- WHERE CRITERIA

) A

GROUP BY myVersion

ORDER BY MAX(myDate) DESC

Regards,
gova