Supervisor History report

Is history tracking enabled on HR10 for the supervisor?

Yes History is enabled on HR 10 for the supervisor fields – I have records that seem to be the supervisor history but can’t find how they join back to the employee. The Employee field is 0 for all of the records where the field number is 721

Doens't fld-nbr 18 give you what you need ? company + employee + fld_nbr = 18 ?

Standard Input Line:1 Pct:0%
File - padict.

FldNbr ItemName

1 Employee
2 Name - Last
3 Name - First
4 Name - Middle Init
5 Address Line 1
6 Address Line 2
7 City
8 State or Prov
9 Postal Code
10 Country
11 Telephone - Work
12 Telephone - Work Ext
13 Telephone - Home
14 Process Level
15 Department
16 User Level
17 Location
18 Supervisor
19 Job Code
20 Status

Fld_Nbr 18 is what you need, which is the SUPERVISOR code for the employee.
Fld_Nbr 721 is the employee associated with the SUPERVISOR code.

So, as an example:
EMPLOYEE 2 has SUPERVISOR code CEO.
SUPERVISOR CEO is assigned to EMPLOYEE 1.

In other words, EMPLOYEE 2 works for EMPLOYEE 1.
EMPLOYEE 1 SUPERVISES EMPLOYEE 2.

HRHISTORY records are stored for the EMPLOYEE,
so you would see a record for EMPLOYEE 2 with Fld_Nbr 18 and A_VALUE = 'CEO'

What I'm Looking for is something like this:

Employee1
- current Supervisor - 10001 John
- previous Supervisor - 10002 Joe
- previous Supervisor - 10003 Kate

if using the Supervisor Code History from Field 18 how would I get to the actual employee who was in the Supervisor code for a point in time? - I wouldn't want the person who is currently in the supervisor code - does that make any sense?
Thanks For your Help!

This is how I would go about in 4GL:

Employee 1
- use current supervisor from HR11 - lookup HRSUPER to get HSU.employee, HSU.Name

- walk thru hrhistory for fld_nbr 18, descending order of Beg_date
----relate HRHISTORY.a_value to HRSUPER.code
---------- relate HRSHUPER.obj_id to HRHISTORY.obj_id, where type = S and fld_nbr = 721 and beg_date is not later than one in fld_nbr 18
---------------------relate HRHISTORY.n_value to EMPLOYEE.employee to get Emp# and Name

CherylB-

Did you get this to work for you? I have not had any luck in pulling any history other than the current employee attached to a supervisor code. I run monthly lists and have to refer to them to see who populated a supervisor code before the current employee assigned to the code.

select
EE.EMPLOYEE
, EE.NICK_NAME
, EE.LAST_NAME
, EE2.NICK_NAME AS SUPERFIRST
, EE2.LAST_NAME AS SUPERLAST
, EE3.NICK_NAME AS INSPRFIRST
, EE3.LAST_NAME AS INSPRLAST
from EMPLOYEE as EE
LEFT JOIN EMPLOYEE EE2 ON EE.SUPERVISOR=EE2.POSITION
LEFT JOIN EMPLOYEE EE3 ON EE.SUPERVISOR_IND=EE3.POSITION
where
EE.emp_status<'TA'
and
EE2.emp_status<'TA'
and
EE3.emp_status<'TA'

Separate and apart from the employee-related data stored in HRHISTORY, the HRHISTORY table also records the fact that the supervisor record itself was changed via HR07, and when/who changed it, but ironically does not record what data changed on the supervisor record (i.e. who the employee is that is filling that supervisor slot).

Below is the Access Query I came up with from Ragu's reply - I still don't think I'm getting all the expected results maybe because of records missing from HRHistory or maybe something is incorerct in the query...- I haven't had a chance look at it again and do a data Audit on the results -


SELECT HRHISTORY.EMPLOYEE, employee_1.FIRST_NAME, employee_1.LAST_NAME, HRHISTORY.FLD_NBR, HRHISTORY.BEG_DATE, HRHISTORY.N_VALUE, HRHISTORY.A_VALUE, employee_1.EMP_STATUS, HRHISTORY_1.FLD_NBR, HRHISTORY_1.DATA_TYPE, HRHISTORY_1.BEG_DATE, HRHISTORY_1.N_VALUE, employee.NICK_NAME AS SupName, employee.LAST_NAME AS SupLastName
FROM employee AS employee_1 INNER JOIN (((HRHISTORY INNER JOIN dbo_HRSUPER ON HRHISTORY.A_VALUE = dbo_HRSUPER.CODE) INNER JOIN HRHISTORY AS HRHISTORY_1 ON dbo_HRSUPER.OBJ_ID = HRHISTORY_1.OBJ_ID) INNER JOIN employee ON HRHISTORY_1.N_VALUE = employee.EMPLOYEE) ON (employee_1.EMPLOYEE = HRHISTORY.EMPLOYEE) AND (employee_1.COMPANY = HRHISTORY.COMPANY)
WHERE (((HRHISTORY.FLD_NBR)=18) AND ((employee_1.EMP_STATUS)<"L3") AND ((HRHISTORY_1.FLD_NBR)=721) AND ((HRHISTORY_1.DATA_TYPE)="S") AND ((HRHISTORY_1.BEG_DATE)>[HRHISTORY].[BEG_DATE]))
ORDER BY HRHISTORY.EMPLOYEE, HRHISTORY.BEG_DATE DESC;