Oracle SQL - Listing Employee Current and Previous Salaries with LAG

Introduction

The following code shows how to reference data in rows other than the current row.

SELECT     empno
      ,    begindate
      ,    enddate
      ,    msal
      ,    LAG(msal) OVER (PARTITION BY empno
                           ORDER BY begindate) prev_sal
FROM       history
ORDER BY   empno, begindate;

Here is an example of using the LAG function to calculate the raise someone received.

Demo

SQL>
SQL>-- from   w  ww . ja v a  2s .c  o m
SQL> drop table history;

Table dropped.

SQL> alter session  set NLS_DATE_FORMAT='DD-MM-YYYY';

Session altered.

SQL> create table history(
  2  empno      NUMBER(4)  ,
  3  beginyear  NUMBER(4)  ,
  4  begindate  DATE       ,
  5  enddate    DATE       ,
  6  deptno     NUMBER(2)  ,
  7  msal       NUMBER(6,2),
  8  comments   VARCHAR2(60)) ;
SQL>
SQL> insert into history values (7001,2000,'01-01-2000','01-02-2000',40, 950,'');
SQL> insert into history values (7370,2000,'01-02-2000', NULL       ,20, 800,'');
SQL> insert into history values (7002,1988,'01-06-1988','01-07-1989',30,1000,'');
SQL> insert into history values (7500,1989,'01-07-1989','01-12-1993',30,1300,'');
SQL> insert into history values (7501,1993,'01-12-1993','01-10-1995',30,1500,'');
SQL> insert into history values (7401,1995,'01-10-1995','01-11-1999',30,1700,'');
SQL>
SQL>
SQL> SELECT     empno
  2        ,    begindate
  3        ,    enddate
  4        ,    msal
  5        ,    LAG(msal) OVER (PARTITION BY empno
  6                             ORDER BY begindate) prev_sal
  7  FROM       history
  8  ORDER BY   empno, begindate;

    EMPNO | BEGINDATE  | ENDDATE    |      MSAL |  PREV_SAL
--------- | ---------- | ---------- | --------- | ---------
 07001.00 | 01-01-2000 | 01-02-2000 |  00950.00 |  [N/A]
 07002.00 | 01-06-1988 | 01-07-1989 |  01000.00 |  [N/A]
 07370.00 | 01-02-2000 |  [N/A]     |  00800.00 |  [N/A]
 07401.00 | 01-10-1995 | 01-11-1999 |  01700.00 |  [N/A]
 07500.00 | 01-07-1989 | 01-12-1993 |  01300.00 |  [N/A]
 07501.00 | 01-12-1993 | 01-10-1995 |  01500.00 |  [N/A]

6 rows selected.

SQL>


SQL>

Related Topic