Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: Compare current row record with previous row record until mismatch

    We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:
    Code:
    emp_salary
    ----------
    emp_id         NUMBER(15) NOT NULL
    effective_date DATE NOT NULL
    salary         NUMBER(15) NOT NULL
    
    Primary key - emp_id, effective_date
    This table is yearly partitioned
    I have to find out how long the salary is not changed for an employee from given date, and last salary. I am using below query to do this:
    Code:
    WITH salary_tab AS
         (SELECT   effective_date, salary,
                   (CASE
                       WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
                                                                            salary
                            )
                          THEN 0
                       ELSE 1
                    END
                   ) changed_ind
              FROM emp_salary
             WHERE emp_id = 12345
               AND effective_date <= TO_DATE ('07/10/2011', 'mm/dd/yyyy')
          ORDER BY effective_date DESC)
    SELECT COUNT (1) not_changed_count, MIN (salary) last_salary
      FROM salary_tab
     WHERE effective_date > (SELECT MAX (effective_date)
                               FROM salary_tab
                              WHERE changed_ind = 1);
    The cost of this query is 1677 and it is taking around 60 msec to complete. When I run this query for around 2000 employees in a loop it is taking around 3 minutes to complete.

    Is there any way we can optimize this query?
    The main bottleneck of this query is in the with clause where I am processing the entire history instead of stopping after first change.

  2. #2
    Join Date
    Nov 2011
    Posts
    2
    Below are the create table and insert scripts:
    Code:
    CREATE TABLE emp_salary
    (
      emp_id          NUMBER(15) CONSTRAINT emp_salary_01 NOT NULL,
      effective_date  DATE CONSTRAINT emp_salary_02 NOT NULL,
      salary          NUMBER(15)
    );
    
    ALTER TABLE emp_salary ADD (
      CONSTRAINT emp_salary_pk
     PRIMARY KEY
     (emp_id, effective_date));
    
    INSERT INTO emp_salary VALUES (1, TO_DATE ('10/31/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (1, TO_DATE ('10/28/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (1, TO_DATE ('10/27/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (1, TO_DATE ('10/26/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (1, TO_DATE ('10/25/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (1, TO_DATE ('10/24/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (1, TO_DATE ('10/21/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (1, TO_DATE ('10/20/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (1, TO_DATE ('10/19/2011', 'mm/dd/yyyy'), 240);
    INSERT INTO emp_salary VALUES (1, TO_DATE ('10/18/2011', 'mm/dd/yyyy'), 240);
    INSERT INTO emp_salary VALUES (1, TO_DATE ('10/17/2011', 'mm/dd/yyyy'), 230);
    
    INSERT INTO emp_salary VALUES (2, TO_DATE ('10/31/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (2, TO_DATE ('10/28/2011', 'mm/dd/yyyy'), 250);			
    INSERT INTO emp_salary VALUES (2, TO_DATE ('10/27/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (2, TO_DATE ('10/26/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (2, TO_DATE ('10/25/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (2, TO_DATE ('10/24/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (2, TO_DATE ('10/21/2011', 'mm/dd/yyyy'), 245);
    INSERT INTO emp_salary VALUES (2, TO_DATE ('10/20/2011', 'mm/dd/yyyy'), 244);
    INSERT INTO emp_salary VALUES (2, TO_DATE ('10/19/2011', 'mm/dd/yyyy'), 242);
    INSERT INTO emp_salary VALUES (2, TO_DATE ('10/18/2011', 'mm/dd/yyyy'), 240);
    INSERT INTO emp_salary VALUES (2, TO_DATE ('10/17/2011', 'mm/dd/yyyy'), 240);
    
    INSERT INTO emp_salary VALUES (3, TO_DATE ('10/31/2011', 'mm/dd/yyyy'), 250);
    INSERT INTO emp_salary VALUES (3, TO_DATE ('10/28/2011', 'mm/dd/yyyy'), 240);			
    INSERT INTO emp_salary VALUES (3, TO_DATE ('10/27/2011', 'mm/dd/yyyy'), 240);
    INSERT INTO emp_salary VALUES (3, TO_DATE ('10/26/2011', 'mm/dd/yyyy'), 240);
    INSERT INTO emp_salary VALUES (3, TO_DATE ('10/25/2011', 'mm/dd/yyyy'), 240);
    INSERT INTO emp_salary VALUES (3, TO_DATE ('10/24/2011', 'mm/dd/yyyy'), 240);
    INSERT INTO emp_salary VALUES (3, TO_DATE ('10/21/2011', 'mm/dd/yyyy'), 240);
    INSERT INTO emp_salary VALUES (3, TO_DATE ('10/20/2011', 'mm/dd/yyyy'), 240);
    INSERT INTO emp_salary VALUES (3, TO_DATE ('10/19/2011', 'mm/dd/yyyy'), 240);
    INSERT INTO emp_salary VALUES (3, TO_DATE ('10/18/2011', 'mm/dd/yyyy'), 240);
    INSERT INTO emp_salary VALUES (3, TO_DATE ('10/17/2011', 'mm/dd/yyyy'), 240);
    For emp_id 1, if we ran this query for 10/31/2011, then it has to compare the 10/31 salary with 10/29 and do the same until the salary mismatches. In this case, salary salary mismatch occurs on 10/20, so the stale salary period is from 10/31 to 10/21 which is 7 days.
    Below query will give that result:
    Code:
    WITH salary_tab AS
         (SELECT   effective_date, salary,
                   (CASE
                       WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
                                                                            salary
                            )
                          THEN 0
                       ELSE 1
                    END
                   ) changed_ind
              FROM emp_salary
             WHERE emp_id = 1
               AND effective_date <= TO_DATE ('10/31/2011', 'mm/dd/yyyy')
          ORDER BY effective_date DESC)
    SELECT COUNT (1) not_changed_count, MIN (salary) last_salary,
           MIN (effective_date) stale_start_date,
           MAX (effective_date) stale_end_date
      FROM salary_tab
     WHERE effective_date > (SELECT MAX (effective_date)
                               FROM salary_tab
                              WHERE changed_ind = 1);
    
    NOT_CHANGED_COUNT LAST_SALARY STALE_STA STALE_END
    ----------------- ----------- --------- ---------
                    7         250 21-OCT-11 31-OCT-11

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •