This discussion is archived
3 Replies Latest reply: Nov 15, 2012 11:53 AM by AlbertoFaenza RSS

SQL help. Identify changes on a field.

744761 Newbie
Currently Being Moderated
Greetings!
PS/SQL is not an option for me. I need help to use SQL, if possible for the following scenario.
Oracle 10G.

Table : JOB_DATA

EMPLID, DATE_EFF, DEPTID, JOBCODE
100, 11/1/2012, 34567, MNG
100, 10/1/2012, 34567, SUP
100, 9/1/2012, 28967, MNG
100, 8/15/2012, 28967, SUP
100,6/30/2012,15879, MNG

I need to get the following records only, that is ,whenever changes in Department ID.

100, 10/1/2012, 34567, SUP
100, 8/15/2012, 28967, SUP
100,6/30/2012,15879, MNG

Thanks in advance.
  • 1. Re: SQL help. Identify changes on a field.
    Justin Cave Oracle ACE
    Currently Being Moderated
    It sounds like you want something like
    SELECT *
      FROM (SELECT j.*, lag(deptid) over (partition by emplid order by date_eff) prior_deptid
              FROM job_data j)
     WHERE prior_deptid IS NULL
        OR prior_deptid != deptid
    Justin
  • 2. Re: SQL help. Identify changes on a field.
    744761 Newbie
    Currently Being Moderated
    Thanks Justin. I'm learning about LAG and LEAD functions now.
  • 3. Re: SQL help. Identify changes on a field.
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Rama,

    Next time please post table structure and sample data.

    Please read SQL and PL/SQL FAQ

    Here another way to do it:
    WITH job_data AS
    (
       SELECT 100 emplid, TO_DATE('11/1/2012', 'MM/DD/YYYY') date_eff, 34567 deptid, 'MNG' jobcode FROM DUAL UNION ALL
       SELECT 100 emplid, TO_DATE('10/1/2012', 'MM/DD/YYYY') date_eff, 34567 deptid, 'SUP' jobcode FROM DUAL UNION ALL
       SELECT 100 emplid, TO_DATE('9/1/2012' , 'MM/DD/YYYY') date_eff, 28967 deptid, 'MNG' jobcode FROM DUAL UNION ALL
       SELECT 100 emplid, TO_DATE('8/15/2012', 'MM/DD/YYYY') date_eff, 28967 deptid, 'SUP' jobcode FROM DUAL UNION ALL
       SELECT 100 emplid, TO_DATE('6/30/2012', 'MM/DD/YYYY') date_eff, 15879 deptid, 'MNG' jobcode FROM DUAL
    )
    SELECT emplid, date_eff, deptid, jobcode
      FROM (SELECT j.*
                 , DENSE_RANK() OVER (PARTITION BY emplid, deptid ORDER BY date_eff) rn
             FROM job_data j
           )
     WHERE rn = 1
     ORDER BY date_eff DESC;
    
        EMPLID DATE_EFF       DEPTID JOBCODE
    ---------- ---------- ---------- -------
           100 10/01/2012      34567 SUP    
           100 08/15/2012      28967 SUP    
           100 06/30/2012      15879 MNG   
    PARTITION BY emplid, deptid means that any time this combination of value is changing the rank will start again from 1.
    The rank is ordered by date_eff.
    I assume that you don't have the same date_eff for more than one record. If you have same date_eff for more than one record you need to explain what will be the logic in this case.

    Regards.
    Al

    Edited by: Alberto Faenza on Nov 15, 2012 8:50 PM
    Clarifications added

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points