Max Date By Training code - Crystal XI

[swhiteside]

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]

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]

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]

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