Max Date By Training code - Crystal XI

swhiteside · Sep 23, 2014

I have a report that has a column for a Training Code, I then have three date columns: Start Date, Req Date, and Exp Date. I have the report set so it will select distinct records only, however, the employee may have taken the training years ago and the code and date show up. I am getting duplicate training codes because they are in date order, I would like to have the Training Code to show up once with the most recent date.

PHV · Sep 24, 2014

A starting point:
SELECT A.*
FROM yourTable A INNER JOIN (
SELECT TrainingCode, MAX(StartDate) AS MaxDate FROM yourTable GROUP BY TrainingCode
) B ON A.TrainingCode = B.TrainingCode AND A.StartDate = B.MaxDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

swhiteside · Sep 25, 2014

I am fairly new to crystal and not very tech language savvy. Currently I have one Group on my report, and it is by employee. So while the info provided above looks like it would work, I am still not sure how to accomplish the coding provided. It seems to me that I need to create a group based on the training code and go from there. I am not really wanting to create any more groups because they like the way the report looks now.

Thanks for your help!

PHV · Sep 25, 2014

You asked in the ANSI SQL forum, so I provided you an SQL code that does what you want without any Crystal stuff.
What is your actual SQL code used as the data source of your report ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Max Date By Training code - Crystal XI

swhiteside

Technical User

PHV

MIS

swhiteside

Technical User

PHV

MIS

Similar threads

Part and Inventory Search

Sponsor