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!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Needing not the current Benefit Plan Effdt row but the previous

Status
Not open for further replies.

scotton

Technical User
Jun 20, 2005
27
US
Okay, when an employee's status changes from FT to PT their
Benefit Program changes. Their most current benefit plan row is not what I need; I need the row right before their status change, what is the best way to get this?

Any suggestions are appreciated,
Thank you,

Sarah
 
Does the benefit plan row that you need have a code or something you can look for. For example would the max dated row with code ?? get what you are looking for?

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining
 
No, there's nothing I can think of with regard to the benefit plan row. The effdt field has an effseq field but it's zero for almost every row unless two transactions occur on the same day giving the same effdt so your effseq for one of the rows would be 1.
That's why I don't know how else to do this; there's really nothing to pull out as an identifier.

TIA,
Sarah
 
I think the way I have done this in the past was to tweek the effective dating SQL to get the max dated record with a date less then the max dated record. Basicly doing a select max in the select max SQL code. (say what ?). So anyway the long and short of it is it requires you to edit the effective dating SQL and is not for the faint of heart.

There may be a plan b but I have never tryed it before.


This is the line in the article to focus on:
lag(jobcode) over (partition by effseq order by effseq)

You would do this as an SQL Derived Field



CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top