HRHISTORY - SQL current & prior

Hoping one of the SQL coders out there can help us. We are trying to report on process level transfers and needing to create a select statement to pull the employee's current & prior process level from the HRHISTORY table. We would also like to use this similar code to pull dept number and job code. We are using the following SQL select to pull the most recent process level change:

SELECT A.*

FROM HRHISTORY AS A

LEFT OUTER JOIN HRHISTORY AS B ON B.EMPLOYEE = A.EMPLOYEE

AND B.FLD_NBR = 14 AND B.FLD_NBR = A.FLD_NBR AND

B.BEG_DATE > A.BEG_DATE

WHERE B.BEG_DATE IS NULL AND A.FLD_NBR = 14;

Would anyone be willing to share their SQL code for obtaining the prior process level record from HRHISTORY? Does not have to be specific to process level field number, just need to know how to obtain a prior value from history.

Thank you in advance!

Deanna

Find more posts tagged with

Infor Lawson Human Resources Group - Discussion

Accepted answers

All comments

deanna-plant

Actually I don't think our SQL query to find the current record is correct either. Interested in a query for both current and previous record.

unknown

You can use mine. Change out the FLD_NBR and remove the "and CX.EMPLOYEE in (123456, 456789, 987654, 654321)".

select *

from (select row_number() over (partition by h.EMPLOYEE order by h.EMPLOYEE, h.BEG_DATE desc, h.SEQ_NBR desc) as row_nbr, *

from LSLMDB.ls_apps.HRHISTORY h

where h.FLD_NBR = 899) CX

where CX.row_nbr <= 2

and CX.EMPLOYEE in (123456, 456789, 987654, 654321)

order by CX.EMPLOYEE, CX.BEG_DATE, CX.SEQ_NBR

Tammy

wilsont

SELECT

A.EMPLOYEE,

A.FLD_NBR,

CONVERT(VARCHAR, A.BEG_DATE, 110) AS BEG_DATE,

A.A_VALUE,

CONVERT(VARCHAR, B.BEG_DATE, 110) AS PRIOR_BEG_DATE,

B.A_VALUE AS PRIOR_VALUE

FROM ls_apps.HRHISTORY A

LEFT OUTER JOIN ls_apps.HRHISTORY B

ON A.COMPANY = B.COMPANY

AND A.EMPLOYEE = B.EMPLOYEE

AND A.FLD_NBR = B.FLD_NBR

AND B.BEG_DATE = (SELECT MAX(B1.BEG_DATE)

FROM ls_apps.HRHISTORY B1

WHERE B1.COMPANY = B.COMPANY

AND B1.EMPLOYEE = B.EMPLOYEE

AND B1.FLD_NBR = B.FLD_NBR

AND B1.BEG_DATE < A.BEG_DATE)

WHERE A.FLD_NBR = 14

AND A.BEG_DATE = (SELECT MAX(A1.BEG_DATE)

FROM ls_apps.HRHISTORY A1

WHERE A1.COMPANY = A.COMPANY

AND A1.EMPLOYEE = A.EMPLOYEE

AND A1.FLD_NBR = A.FLD_NBR

AND A1.BEG_DATE <= GETDATE())

AND A.EMPLOYEE = 19127

0911060553270289.pdf

unknown

Here is another example with all the data on one row:

with

HRHX as

(

select *, row_number() over (partition by h.EMPLOYEE order by h.EMPLOYEE, h.BEG_DATE desc) as rownum

from LSLMDB.ls_apps.HRHISTORY h

where h.FLD_NBR = 899

)

select HRHX.EMPLOYEE,

format(max(HRHX.N_VALUE), '##,###,###.00') as currSalary,

format(min(HRHX.N_VALUE), '##,###,###.00') as prevSalary

from HRHX

where rownum <= 2

group by HRHX.EMPLOYEE

deanna-plant

This is perfect! Thank you so much Tammy!

Copyright © 2025 Infor. All rights reserved.