• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Human Capital Management Lawson S3 HR/Payroll/Benefits

Get list of Employees with Address changed

 3 Replies
 1 Subscribed to this topic
 68 Subscribed to this forum
Sort:
Author
Messages
viet
New Member Send Private Message
Posts: 2
New Member
6/28/2018 2:40 PM

I'm digging through HRHISTORY to query for USER_ID / EMPLOYEE ID to create a list of EMPLOYEE that have changed their ADDRESS recently. Do you have any experience with that?

So the query should look something like this:

select FIRST_NAME, LAST_NAME, ADDR1, ADDR2, ADDR3, ADDR4, CITY, STATE, ZIP, convert(datetime,DATE_STAMP,101) AS DATE_STAMP from HRHISTORY right join EMPLOYEE on EMPLOYEE.EMPLOYEE = HRHISTORY.EMPLOYEE where convert(datetime,DATE_STAMP,101) > {current time - 14 days}

JudeBac
Veteran Member Send Private Message
Posts: 129
Veteran Member
7/3/2018 2:08 PM
You will need to join PADICT.FLD_NBR. I recommend that you change yours on your test environment and see which FLD_NBR were used.
Dave Curtis
Veteran Member Send Private Message
Posts: 136
Veteran Member
7/23/2018 3:54 PM
You probably already worked this out but here is what I would use to pull address changes (with previous value).

We are on Oracle database, looking at your SQL example I am thinking you are using MS SQL so some of this might not work exactly as it is written for you if you are not using Oracle.

The following pulls anyone with changes to the E8 topic (address fields) in the last 14 days and it provides the current employee address info, the field changed, the new value and previous value.

SELECT hrs.employee
,trim(emp.last_name) as last_name
,trim(emp.first_name) as first_name
,trim(emp.addr1) as address_1
,trim(emp.addr2) as address_2
,trim(emp.city) as city
,trim(emp.state) as state
,trim(emp.zip) as zip
,hrs.fld_nbr
,pad.item_name
,trim(hrs.a_value) as new_value
,trim(hrs.prev_value) as prev_value
,hrs.seq_nbr
,hrs.date_stamp
FROM (SELECT hrs.*
,LAG(hrs.a_value, 1) OVER(PARTITION BY hrs.employee, hrs.fld_nbr ORDER BY hrs.date_stamp, hrs.seq_nbr) as prev_value
FROM hrhistory hrs
WHERE fld_nbr IN (SELECT fld_nbr
FROM padict
WHERE topic = 'E8')
AND date_stamp BETWEEN SYSDATE - 14 and SYSDATE
ORDER BY hrs.employee
,hrs.fld_nbr
,hrs.date_stamp
,hrs.fld_nbr) hrs
JOIN employee emp ON (hrs.company = emp.company and hrs.employee = emp.employee)
JOIN (SELECT *
FROM padict
WHERE topic = 'E8') pad ON (hrs.fld_nbr = pad.fld_nbr)
WHERE trim(hrs.prev_value) IS NOT NULL
ORDER BY hrs.employee
,hrs.date_stamp
,hrs.fld_nbr
,hrs.seq_nbr
viet
New Member Send Private Message
Posts: 2
New Member
7/23/2018 4:00 PM

Thanks, Dave. Yes, I've figured it out.