Who's On?

	Membership:
	Latest: Jeovanni
	Past 24 Hours: 0
	Prev. 24 Hours: 0
	Overall: 5287

	People Online:
	Visitors: 422
	Members: 0
	Total: 422

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1377
Roger French	1315
mark.cook	1244

Need a report that shows previous pay rate

2 Replies

0 Subscribed to this topic

22 Subscribed to this forum

Sort:

Author

Messages

Frank Z

New Member Send Private Message

Posts: 0

6/2/2010 5:11 PM

I am building a report that shows current pay rates, effective date, reason for the change, as well as the previous rate. I have everything except the previous rate working, and I keep hitting the wall. My logic has been to use a nthLargest=2 on the PRRATEHIST.BEG_DATE field. To pull the previous pay rate I then use the formula:

if {PRRATEHIST.BEG_DATE} =
NthLargest (2, {PRRATEHIST.BEG_DATE}, {EMPLOYEE.EMPLOYEE})
then {@Pay Rate}

Even though the {NthLargest (2, {PRRATEHIST.BEG_DATE}, {EMPLOYEE.EMPLOYEE})} field returns a value that is equal to the 2nd most recent begin date, it is returning values of 0. Can anyone lead me in the right direction to get this report up and running?

TIA

John Henley

Posts: 3351

6/2/2010 6:07 PM

In order to show "previous" values on the same detail line as the "current" value, you either need to create a subreport (passing it the company, employee, and NthLargest (2, {PRRATEHIST.BEG_DATE}, {EMPLOYEE.EMPLOYEE}) ) to retrieve the correct record, or use SQL a command that includes a subquery. I have found the latter option to be much more efficient as well as reliable (remember that multiple changes could exist for the same day, so you might have multiple seq-nbr values for the same date). Here are a couple of samples that do similar things:

Shane Jones

Veteran Member Send Private Message

Posts: 460

6/2/2010 7:47 PM

I built a database view that shows each pay record with the previous value. It shows everything on one line. When the associate is a new hire it just shows a null for the the previous value. This has been used for pay rates and other HRHistory table items. However, to John's point, the seq-nbr has caused some issues with the output and I am still trying to make sure that it works correctly everytime. (garbage-in garbage-out)... *the issue is more with corrections that do not display correctly.