I am doing the Nth Largest pay rate for our Exempt employees and it gives me their highest pay rate - HOWEVER (DUH!) some people have had a pay decrease - and this is their most recent pay rate.
I am using the Pay Rate Hist table - 'cause our EMPLOYEE pay rate field is funky and doesnt show the value in Crystal.
How can I just get their most recent pay rate to display?
PAYRATE_DATE = NthMostRecent.... ?!!!
:angry: :banghead: :cof:
There are many ways you can accomplish this. Here's a couple of different ways that I might approach this:
#1
Group the report on PRRATEHIST.EMPLOYEE and PRRATEHIST.BEG_DATE (ascending) in your report, suppress your detail section, and then put your detail fields in the group footer of the beg date group.
#2
Create a database view with the following logic:
| CODE |
select distinct h1.company, h1.employee,
(select max(h2.beg_date)
from law_bld.prratehist h2
where h2.company = h1.company
and h2.employee = h1.employee) as max_date
from law_bld.prratehist h1 |
This will identify the most recent (current) pay rate for each employee.
Join this view to another view (or sql command) that includes your sql required for the report (ie EMPLOYEE, PRRATEHIST, etc). Join on company, employee and max_date (to company/employee/beg_date in prratehist) to include only the current pay rate records.
Fyi - Utilizing your db's procedural language (ie pl/slq, t-sql) would also work, but I'm pretty sure you are hosted by Siemen's, so this won't be an option for you. Come to think of it, database views might not be an option for you either.