Needing not the current Benefit Plan Effdt row but the previous

[scotton]

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

 

[CharlesCook]

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

 

[scotton]

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

 

[CharlesCook]

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.

http://www.tek-tips.com/viewthread.cfm?qid=1154891

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