Max Function

Question

Post reply

Max Function

et2brutuss

SSChasing Mays

Points: 652
More actions
August 5, 2014 at 10:19 am

#289577

I am a newbie. I am trying to get the most recent date in a list of dates. Here is the script:
MAX(CASE EP.ReviewType WHEN 'RES' THEN(CONVERT(CHAR(11),EP.ReviewDate,106)) END) AS RespitePR
I am getting dates but not the most recent.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Hello and welcome to the forums!

If all you're after is to get the most recent date from ep.ReviewDate, this should suffice:

SELECT MAX(ep.ReviewDate)

FROM table_name ep;

If you want to get the most recent date from ep.RevieDate where the ReviewType is 'RES', this should work:

SELECT MAX(ep.ReviewDate)

FROM table_name ep

WHERE ep.ReviewType = 'RES';

If you want to get the max date between ReviewDate and another column, that'll be different and we'll need to see the DDL for the table.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

You're converting to style 106 (dd mon yy) before applying the MAX, so the MAX is based on string comparison, e.g. '12 Aug 2014' would be less than '30 Jul 2014' etc.

Try the convert after the MAX, like this:

CONVERT(CHAR(11), MAX(CASE EP.ReviewType WHEN 'RES' THEN EP.ReviewDate END), 106) AS RespitePR

et2brutuss SSChasing Mays Points: 652 More actions · Answer 3

That works just by itself but I have two other elements - converting the date format and a second review date. I don't need to get the review date between the two columns, I need to get the most recent review date for type A review and the most recent date for type b review. Report will be:

Employee, Review A expiry date, Review B expiry date.

et2brutuss SSChasing Mays Points: 652 More actions · Answer 4

Your explanation made sense, tried your suggested fix but am still not getting the most recent date. eg) emp 998 has a review date of July 31, 2014 but I'm getting Jan 1, 2001 as next review date.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Can you please post the DDL for the table?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

et2brutuss SSChasing Mays Points: 652 More actions · Answer 6

et2brutuss

SSChasing Mays

Points: 652

August 5, 2014 at 11:04 am

#1735323

I'm afraid I don't know what you are asking me for.

et2brutuss SSChasing Mays Points: 652 More actions · Answer 7

Using this line: CONVERT(CHAR(11), MAX(CASE EP.ReviewType WHEN 'RES' THEN EP.ReviewDate END), 106) AS RespitePR

and removing the following line from the query: WHERE EP.NextReviewDate <'2014-11-30'

The query is now generating the correct data.

Thanks for your help.

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

et2brutuss (8/5/2014)
I'm afraid I don't know what you are asking me for.

DDL (Data Definition Language), in this case the CREATE TABLE statement(s) for the table(s) involved in your query. It would also help if you would post your entire query not just one little portion. Please realize we can't see what you see.

With that in mind, I would highly recommend that you read the first article I link to below in my signature block. It will walk you through everything you need to post and how to post it to get the best possible responses to your question.

et2brutuss SSChasing Mays Points: 652 More actions · Answer 9

I didn't realize there was a forum specifically for newbies until after I posted my question in this one.

Thanks for the links, they are quite helpful.