HRHISTORY > EMPLOYEE tables, Crystal.

Not sure what you mean by 'key'--the key in this instance would be COMPANY and EMPLOYEE.

Just the distinct value -

I tried linking the the Comp, and Empl, but it did not return my results.

Which fields am I pulling from HRHISTORY?

thanks!

What fields are you trying to pull? All fields in Lawson HR have a "field number". Depending on which options you have set up on HR10, you may not have any fields set up to be kept in history.

Michael, here is a simple query to copy to see if you have any data in hrhistory. It simply links the employee and hrhistory data. If you dont get a returned data set you need to start looking at hr10 to see if you capturing history as John mentioned.

select
e.employee,
e.last_name,
e.first_name,
h.fld_nbr,
h.beg_date,
h.n_value,
h.a_value,
h.d_value
from
employee e,
hrhistory h
where
h.company = e.company and
h.employee = e.employee and
ROWNUM <= 1000
order by h.beg_date

To see a list of fld_nbrs and their descriptions, take a look at table 'padict'..

A word of advice, joining HRHistory table is where you can see what data changed, but you really should have your Employee Actions and Reasons (pa52) setup in such a way that you can easily look for job, dept, salary, status changes using the Action and Reason filters (against PERSACTHST table). Unfortunately, joining persacthst to hrhistory gets more complex and then involves the obj_id as part of the key..good luck

Eric

The lawson-delivered field number for job code changes is 19.

In HR11.1 > Assignment Tab > I am drilling on the Job Code field, and see 2 jobs (for this person) w/ date stamps But whn I run this report in Crystal using the PERSCTHIST this particular person shows up with the 2 changes (new jobs) but the Job Code is the most recent on both records...??

Michael, Im not sure what logic you are using in your Crystal report (if youre taking the Jobcode from the Employee table it will always show the most recent value).

When you drill on Job Code for history in the Assignment tab it is the equivalent of running a query against the HRHistory table for that employee and fld_nbr (19). But, when you start querying against the persacthist table and looking for changes in job_code (fld_nbr 19), then you must join both persacthist and hrhistory. The key fields to join between these two tables are as follows:
1) company
2) employee
3) obj_id (be careful, because you must join the persacthst.obj_id to the hrhistory.ACT_obj_id

Also, depending on your setup, you may be able to change an employee's jobcode directly from HR11 without using an Action/Reason. And if so, then you will never see that change by going through the persacthist table. This all depends on how you have the jobcode setup in the Data Item Attributes form..

Eric

Sometimes it's necessary to see what's in a table before writing your final query. To get the clearest view, I narrow it down to just using the the HRHISTORY table, and I select the company and employee number I want to see. You can also consult the PADICT to identify a particular field and specify that in your select criteria.

The difficulty I have is that a request frequently involves pulling a population of employees that had a specific employee status at a certain time in the past. HRHISTORY does not make this easy, since it stores change events. Can any one suggest a method to do this in Crystal?

Ok. Thanks. I was able to get the data by linking the persacthst.obj_id to the hrhistory.ACT_obj_id and selecting on field_nbr "19"

Gene, if you want to get that type of result from HRHISTORY, you have to use SQL directly (called a "database command" in Crystal) rather than working at the table level. Your SQL will include a bunch of subqueries, which are joined to a main table, e.g. EMPLOYEE, in order to do what I call "as of" reporting.

John,

Do a lot of people run across this sort of problem, one which involves reporting on events in the HRHISTORY table for a specific time frame?

I'm curently trying to report on employee status at the times of specific personell actions. More specifically, I'm interested in being able to pull the employee's status at the time of a termination action. I assume that the solution will apply to other field in the HRHISTORY table as well just being dependent on obj_id.

Would SQL be the easiest way to pull this information? If so, could you provide a sample?

Otherwise what would you recommend for reporting on employee history?

Chris

Hi Christopher,
It's definitely one of the more challenging types of queries. I'm assuming you're reporting from Crystal?

If so, here's one technique I've used to pull the data, which uses subreports in Crystal:
https://www.lawsonguru.co...-Date-Reporting.aspx

However, this is a performance nightmare if you have a lot of employees and/or if you don't re-index (incidentally, I had one client who hadn't re-indexed HRHISTORY since they installed Lawson and when they did, the run time of the report went from 10 minutes to < 1 minute!)

If you do it with SQL, it's vendor-specific. Here's a sample for Oracle from Crystal (not the {?BeginDate} parameters) that returns a query promotions / transfers (see the ACTION_CODE) for a specific date range and joins to the HRHISTORY to get the current (and previous) POSITION and EMP_STATUS fields as of the date of the action.

SELECT
PAH.COMPANY,
EMP.PROCESS_LEVEL,
PAH.EMPLOYEE,
PAH.EFFECT_DATE,
PAH.ACTION_CODE,
PAH.REASON_01,
PAH.REASON_02,
EMP.LAST_NAME,
EMP.FIRST_NAME,
EMP.MIDDLE_INIT,
PEM.SEX,
PEM.EEO_CLASS,
EMP.POSITION
,
(SELECT
A_VALUE
FROM
(
SELECT
COMPANY,
EMPLOYEE,
BEG_DATE,
A_VALUE
FROM LAWPROD.HRHISTORY HRH
WHERE
HRH.FLD_NBR = 126
ORDER BY BEG_DATE DESC
) CUR_POS
WHERE CUR_POS.COMPANY=PAH.COMPANY
AND CUR_POS.EMPLOYEE=PAH.EMPLOYEE
AND CUR_POS.BEG_DATE <= PAH.EFFECT_DATE
AND rownum = 1 ) AS CUR_POSITION
,
(SELECT
A_VALUE
FROM
(
SELECT
COMPANY,
EMPLOYEE,
BEG_DATE,
A_VALUE
FROM LAWPROD.HRHISTORY HRH
WHERE
HRH.FLD_NBR = 126
ORDER BY BEG_DATE DESC
) PREV_POSITION
WHERE PREV_POSITION.COMPANY=PAH.COMPANY
AND PREV_POSITION.EMPLOYEE=PAH.EMPLOYEE
AND PREV_POSITION.BEG_DATE < PAH.EFFECT_DATE
AND rownum = 1 ) AS PREV_POSITION
,
(SELECT
A_VALUE
FROM
(
SELECT
COMPANY,
EMPLOYEE,
BEG_DATE,
A_VALUE
FROM LAWPROD.HRHISTORY HRH
WHERE
HRH.FLD_NBR = 20
ORDER BY BEG_DATE DESC
) CUR_STATUS
WHERE CUR_STATUS.COMPANY=PAH.COMPANY
AND CUR_STATUS.EMPLOYEE=PAH.EMPLOYEE
AND CUR_STATUS.BEG_DATE <= PAH.EFFECT_DATE
AND rownum = 1 ) AS CUR_STATUS
,
(SELECT
A_VALUE
FROM
(
SELECT
COMPANY,
EMPLOYEE,
BEG_DATE,
A_VALUE
FROM LAWPROD.HRHISTORY HRH
WHERE
HRH.FLD_NBR = 20
ORDER BY BEG_DATE DESC
) PREV_STATUS
WHERE PREV_STATUS.COMPANY=PAH.COMPANY
AND PREV_STATUS.EMPLOYEE=PAH.EMPLOYEE
AND PREV_STATUS.BEG_DATE < PAH.EFFECT_DATE
AND rownum = 1 ) AS PREV_STATUS
FROM
lawprod.PERSACTHST PAH
LEFT OUTER JOIN lawprod.EMPLOYEE EMP
ON (EMP.COMPANY = PAH.COMPANY)
AND (EMP.EMPLOYEE = PAH.EMPLOYEE)
LEFT OUTER JOIN lawprod.PAEMPLOYEE PEM
ON (PEM.COMPANY = PAH.COMPANY)
AND (PEM.EMPLOYEE = PAH.EMPLOYEE)
WHERE (PAH.COMPANY = 4321)
AND (PAH.ACTION_CODE IN ('PR','TR'))
AND (PAH.EFFECT_DATE BETWEEN {?BeginDate} AND {?EndDate})

John,
This is PERFECT as an answer to something I've been trying to do around tracking position changes. Follow-up question though: How do you add an AND to the WHERE clause that will make the query only show where the old and new position are different? I tried adding the following:
AND CUR_POSITION <> PREV_POSITION but that generates an Invalid identifer message. Thanks very much!

You can try this one, which wraps the previous query inside another SELECT with the WHERE (and uses some COALESCEs to avoid NULLs in the WHERE:

SELECT * FROM (
SELECT
PAH.COMPANY,
EMP.PROCESS_LEVEL,
PAH.EMPLOYEE,
PAH.EFFECT_DATE,
PAH.ACTION_CODE,
PAH.REASON_01,
PAH.REASON_02,
EMP.LAST_NAME,
EMP.FIRST_NAME,
EMP.MIDDLE_INIT,
PEM.SEX,
PEM.EEO_CLASS,
EMP.POSITION
,
COALESCE(
(SELECT
A_VALUE
FROM
(
SELECT
COMPANY,
EMPLOYEE,
BEG_DATE,
A_VALUE
FROM HRHISTORY HRH
WHERE
HRH.FLD_NBR = 126
ORDER BY BEG_DATE DESC
) CUR_POS
WHERE CUR_POS.COMPANY=PAH.COMPANY
AND CUR_POS.EMPLOYEE=PAH.EMPLOYEE
AND CUR_POS.BEG_DATE <= PAH.EFFECT_DATE
AND rownum = 1 )
,' ')
AS CUR_POSITION

,
COALESCE(
(SELECT
A_VALUE
FROM
(
SELECT
COMPANY,
EMPLOYEE,
BEG_DATE,
A_VALUE
FROM HRHISTORY HRH
WHERE
HRH.FLD_NBR = 126
ORDER BY BEG_DATE DESC
) PREV_POSITION
WHERE PREV_POSITION.COMPANY=PAH.COMPANY
AND PREV_POSITION.EMPLOYEE=PAH.EMPLOYEE
AND PREV_POSITION.BEG_DATE < PAH.EFFECT_DATE
AND rownum = 1 )
,' ') AS PREV_POSITION
,
COALESCE(
(SELECT
A_VALUE
FROM
(
SELECT
COMPANY,
EMPLOYEE,
BEG_DATE,
A_VALUE
FROM HRHISTORY HRH
WHERE
HRH.FLD_NBR = 20
ORDER BY BEG_DATE DESC
) CUR_STATUS
WHERE CUR_STATUS.COMPANY=PAH.COMPANY
AND CUR_STATUS.EMPLOYEE=PAH.EMPLOYEE
AND CUR_STATUS.BEG_DATE <= PAH.EFFECT_DATE
AND rownum = 1 ),' ') AS CUR_STATUS
,
COALESCE(
(SELECT
A_VALUE
FROM
(
SELECT
COMPANY,
EMPLOYEE,
BEG_DATE,
A_VALUE
FROM HRHISTORY HRH
WHERE
HRH.FLD_NBR = 20
ORDER BY BEG_DATE DESC
) PREV_STATUS
WHERE PREV_STATUS.COMPANY=PAH.COMPANY
AND PREV_STATUS.EMPLOYEE=PAH.EMPLOYEE
AND PREV_STATUS.BEG_DATE < PAH.EFFECT_DATE
AND rownum = 1 ),' ') AS PREV_STATUS
FROM
PERSACTHST PAH
LEFT OUTER JOIN EMPLOYEE EMP
ON (EMP.COMPANY = PAH.COMPANY)
AND (EMP.EMPLOYEE = PAH.EMPLOYEE)
LEFT OUTER JOIN PAEMPLOYEE PEM
ON (PEM.COMPANY = PAH.COMPANY)
AND (PEM.EMPLOYEE = PAH.EMPLOYEE)
WHERE (PAH.COMPANY = 4321)
AND (PAH.ACTION_CODE IN ('PR','TR'))
AND (PAH.EFFECT_DATE BETWEEN to_date('2000-01-01','yyyy-mm-dd') AND to_date('2008-12-31','yyyy-mm-dd'))
)
WHERE CUR_POSITION <> PREV_POSITION
OR CUR_STATUS <> PREV_STATUS

Hello,

I am trying to mimic your query above using SQL and an MS Access database. I've downloaded some content from the Lawson tables and put them into an Access db. Then I'm using a SQL tool to query the MS Access db.

The problem...it does not like rownum. Do you have any workarounds I could use? I read something about select (*) as a work around but cannot figure out how to fit it into my subquery below.

SELECT
PEP.COMPANY AS company_id,
PEP.EMPLOYEE AS employee_id,
PEM.SEX AS sex_code,
PEM.EEO_CLASS AS race_code,
PEM.BIRTHDATE AS birthdate,
PEM.TRUE_MAR_STAT AS marital_status,
PEM.SENIOR_DATE AS seniority_date,
EMP.FULL_NAME AS person_name,
EMP.ADDR1 AS person_address_street,
EMP.ADDR2 AS personal_address_number,
EMP.CITY AS personal_address_city,
EMP.STATE AS personal_address_state,
EMP.ZIP AS personal_address_zip,
EMP.DATE_HIRED AS hire_date,
EMP.ADJ_HIRE_DATE AS rehire_date,
EMP.FST_DAY_WORKED AS recent_hire_date,
EMP.TERM_DATE AS termination_date,
EMP.LAST_DAY_PAID AS last_pay_date,
PEP.EFFECT_DATE AS effective_date,
PEP.END_DATE AS end_date,
PEP.POS_LEVEL AS position_level,

(SELECT A_VALUE
FROM (SELECT COMPANY, EMPLOYEE, BEG_DATE, A_VALUE, SEQ_NBR, POS_LEVEL
FROM HRHISTORY HRH
WHERE HRH.FLD_NBR = 14
ORDER BY BEG_DATE DESC, SEQ_NBR DESC) process_level_id
WHERE process_level_id.COMPANY=PEP.COMPANY
AND process_level_id.EMPLOYEE=PEP.EMPLOYEE
AND process_level_id.POS_LEVEL=PEP.POS_LEVEL
AND process_level_id.BEG_DATE <= :ENTERDATE
AND rownum = 1 ) AS process_level_id


FROM ((PAEMPPOS PEP LEFT OUTER JOIN EMPLOYEE EMP ON EMP.COMPANY = PEP.COMPANY AND EMP.EMPLOYEE = PEP.EMPLOYEE)
LEFT OUTER JOIN PAEMPLOYEE PEM ON PEM.COMPANY = PEP.COMPANY AND PEM.EMPLOYEE = PEP.EMPLOYEE)
LEFT OUTER JOIN HRHISTORY HRH2 ON HRH2.COMPANY = PEP.COMPANY AND HRH2.EMPLOYEE = PEP.EMPLOYEE


WHERE 1=1
AND PEP.COMPANY = 55
AND PEP.EMPLOYEE = '283227'


AND HRH2.BEG_DATE = (SELECT MAX(HRH3.BEG_DATE)
FROM HRHISTORY HRH3
WHERE HRH3.COMPANY = HRH2.COMPANY
AND HRH3.EMPLOYEE = HRH2.EMPLOYEE
AND HRH3.FLD_NBR = HRH2.FLD_NBR
AND HRH3.BEG_DATE <= :ENTERDATE)

AND HRH2.FLD_NBR = 20
AND HRH2.A_VALUE IN ('A1','A2','A3','A4','A5','A6','A8','L1','L2','L3','ZA')

AND ((PEP.EFFECT_DATE)<=:ENTERDATE)
AND ((PEP.END_DATE)>:ENTERDATE Or (PEP.END_DATE) IS NULL);

The 'rownum' syntax is just for Oracle.  Just quickly glancing at the query what it looks like it is trying to do is get the latest value for something out of HRHISTORY.   You may try replacing that subquery with this on as it may work.  You will need to test it. If you can get away with it it may be better to leave this data on the main database and query it there.  Having seperate access databases can be problematic.  Most IT organizations really do not enjoy running across access databases.  They tend to get corrupted and all sorts of other issues.   Another thing that I find business users can really get value out of is having a query like this created as a view. 

(

SELECT hrh.A_VALUE

FROM HRHISTORY hrh

WHERE PEM.COMPANY = hrh.COMPANY

AND PEM.EMPLOYEE = hrh.EMPLOYEE

AND hrh.FLD_NBR = 14

AND hrh.BEG_DATE =

(

SELECT MAX(hrh2.BEG_DATE)

FROM HRHISTORY hrh2

WHERE hrh.COMPANY = hrh2.COMPANY

AND hrh.EMPLOYEE = hrh2.EMPLOYEE

AND hrh.FLD_NBR = hrh2.FLD_NBR

)

AND hrh.SEQ_NBR =

(

SELECT MAX(hrh3.SEQ_NBR)

FROM HRHISTORY hrh3

WHERE hrh.COMPANY = hrh3.COMPANY

AND hrh.EMPLOYEE = hrh3.EMPLOYEE

AND hrh.FLD_NBR = hrh3.FLD_NBR

AND hrh.BEG_DATE = hrh3.BEG_DATE

)

) AS process_level_id

The other thing is the ':ENTERDATE' looks like Oracle syntax as well. In SQL Server and prossibly Access the syntax would be something like @ENTER_DATE. Here is probably a closer fit for what the subquery was intending.

(
SELECT hrh.A_VALUE
FROM HRHISTORY hrh
WHERE PEM.COMPANY = hrh.COMPANY
AND PEM.EMPLOYEE = hrh.EMPLOYEE
AND hrh.FLD_NBR = 14
AND hrh.BEG_DATE =
(
SELECT MAX(hrh2.BEG_DATE)
FROM HRHISTORY hrh2
WHERE hrh.COMPANY = hrh2.COMPANY
AND hrh.EMPLOYEE = hrh2.EMPLOYEE
AND hrh.FLD_NBR = hrh2.FLD_NBR
AND hrh2.BEG_DATE <= @ENTER_DATE
)
AND hrh.SEQ_NBR =
(
SELECT MAX(hrh3.SEQ_NBR)
FROM HRHISTORY hrh3
WHERE hrh.COMPANY = hrh3.COMPANY
AND hrh.EMPLOYEE = hrh3.EMPLOYEE
AND hrh.FLD_NBR = hrh3.FLD_NBR
AND hrh.BEG_DATE = hrh3.BEG_DATE
)
) AS process_level_id

I needed to design a similar solution, but I couldn't use HRHISTORY because that doesn't help track changes in multiple position levels. I needed to be able to track department, job code, or position changes for each position level that an employee has. Out goes HRHISTORY, in comes PAEMPPOS. This is for "Internal Turnover" measurements, as opposed to "External Turnover" from terminations.

That sure is a lot of work done in SQL that could be easier done in an actual programming language IMHO.

Jeremy Zerr
St Lukes Health System, Boise, ID