Oldest effective date for the most current Job

[FireGeek21]

I am missing something here and have been looking at this far too long to see what I am missing. I need the oldest effective date for the most current job. The bulk of the report is working just fine except for a few key people who have information such as the following:

EMPLOYEE EFF_DATE JOBCODE
00000001 01/01/2010 123456
00000001 03/30/2008 123456 (need this effective date)
00000001 05/02/2005 456789

00000002 12/18/2009 963852
00000002 04/13/2008 963852 (need this effective date)
00000002 04/04/2004 321654

Everyone else, I need the Maximum(EFF_DATE) - which is working fine.

I am using Selection Formula -> Groups as {HRHISTORY.BEG_DATE} = Maximum({HRHISTORY.BEG_DATE}, {HRHISTORY.EMPLOYEE})

I also have a Formula Field EFF_DT as IF {HRHISTORY.A_VALUE} = Previous({HRHISTORY.A_VALUE}) THEN Previous({@BEG_DT}) ELSE Date({HRHISTORY.BEG_DATE})

I have also tried the EFF_DT Formula Field with the Next function.

@BEG_DT is a Formula Field as Date({HRHISTORY.BEG_DATE})

Grouping on EMPLOYEE and Sorting on EMPLOYEE and then EFF_DT.

Any thoughts? Let me know if you need more information.

THANKS!!

rsgeek



RSGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[lbass]

You need to show sample data that includes the hrhistory.Avalue and hrhistory.beg_date along with the fields you are already showing, and maybe explain the relevance of the hrhistory fields to the issue.

-LB

 

[FireGeek21]

LB -

Thank you for the reply. I am showing the sample data. Forgive me for not showing the titles as they are in the HRHISTORY table.

EMPLOYEE BEG_DT A_VALUE
00000001 01/01/2010 123456
00000001 03/30/2008 123456 (need this effective date)
00000001 05/02/2005 456789

00000002 12/18/2009 963852
00000002 04/13/2008 963852 (need this effective date)
00000002 04/04/2004 321654

The reason I need the older date of the two A_VALUES that are the same is because the A_VALUES are actually job codes. I need to show how long a person has been in their current position - thus needing to show the oldest of the two dates.

More details of the report include:

Selection Formula Record = {EMPLOYEE.EMP_STATUS} = "A" (for active employees only)

Selection Formula Groups = {HRHISTORY.BEG_DATE} = Maximum({HRHISTORY.BEG_DATE}, {HRHISTORY.EMPLOYEE})

Formula Field (TIME_IN_POS) = DateDiff("m", {@EFFDT}, CurrentDate) / 12

Formula Field (EFF_DT) = IF {HRHISTORY.A_VALUE} = Previous({HRHISTORY.A_VALUE}) THEN Previous({@BEG_DT}) ELSE Date({HRHISTORY.BEG_DATE})

Formula Field (BEG_DT) = Date({HRHISTORY.BEG_DATE})

Grouping on EMPLOYEE

Sorting on EMPLOYEE and BEG_DATE

I hope all this helps. THANK YOU!

RSGeek



RSGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[GJParker]

If all of these fields exist in the same table in your database then a simpler solution could be to add this table in to your report again and Left Outer Join back to the original table on
TableA,Employee = TableB.Employee and
TableA.a_value = TableB.a_value

this would then give you data like

A.EMPLOYEE A.BEG_DT A.A_VALUE B.EMPLOYEE B.BEG_DT B.A_VALUE
00000001 01/01/2010 123456 00000001 03/30/2008 123456
00000001 03/30/2008 123456 00000001 01/01/2008 123456
00000001 05/02/2005 456789 NULL NULL NULL
00000002 12/18/2009 963852 00000002 04/13/2008 963852
00000002 04/13/2008 963852 00000002 12/18/2009 963852
00000002 04/04/2004 321654 NULL NULL NULL


once you have the data in this form add a group on A.EMPLOYEE and add a group selection formula

{A.BEG_DT} = Maximum({A.BEG_DT}, {A.EMPLOYEE}

Then your {@BEG_DT} fromula would be

If IsNull({B.BEG_DT}} Then
{A.BEG_DT}
Else
Minimum({B.BEG_DT}, {A.EMPLOYEE}

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

I think Gary's on the right track. Just add the history table a second time, and link it to the original history table on Employee and A_Value. I think it can be an inner join. Then use a group selection formula like this:

{HRHISTORY.BEG_DATE} = Maximum({HRHISTORY.BEG_DATE}, {HRHISTORY.EMPLOYEE}) and
{HRHISTORY_1.BEG_DATE} = Minimum({HRHISTORY_1.BEG_DATE}, {HRHISTORY.EMPLOYEE})

When there is only one record, the max/min will be the same. When there are multiple records, they will repeat for each instance of the date in the main report, so I think this should work. The HrHistory_1. Beg_Date will be the effective date you are looking for.

-LB