3 Replies Latest reply: Nov 15, 2012 1:53 PM by AlbertoFaenza RSS

    SQL help. Identify changes on a field.

    744761
      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.
          JustinCave
          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
            Thanks Justin. I'm learning about LAG and LEAD functions now.
            • 3. Re: SQL help. Identify changes on a field.
              AlbertoFaenza
              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