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
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
    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
      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
        Posts: 2
        New Member
        7/23/2018 4:00 PM

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