Oracle SQL - Using LAG to Calculate a Raise

Introduction

The LAG function returns the same data type as the expression.

The following code illustrates how to use the current and previous salaries to calculate the raise in pay.

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

The LAG(msal) does not look backward when the EMPNO changes from 7001 to 7002.

Demo

SQL>
SQL> drop table history;

Table dropped.-- from   w  ww .  ja va2s . c o  m

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> SELECT     empno
  2        ,    begindate
  3        ,    enddate
  4        ,    msal
  5        ,    LAG(msal) OVER (PARTITION BY empno
  6                             ORDER BY begindate) prev_sal
  7        ,    msal - LAG(msal) OVER (PARTITION BY empno
  8                                    ORDER BY begindate) raise
  9  FROM       history
 10  ORDER BY   empno, begindate;

     EMPNO BEGINDATE  ENDDATE          MSAL   PREV_SAL      RAISE
---------- ---------- ---------- ---------- ---------- ----------
      7001 01-01-2000 01-02-2000        950
      7002 01-06-1988 01-07-1989       1000
      7370 01-02-2000                   800
      7401 01-10-1995 01-11-1999       1700
      7500 01-07-1989 01-12-1993       1300
      7501 01-12-1993 01-10-1995       1500

6 rows selected.

SQL>

Oracle SQL - Using LAG to Calculate a Raise

Introduction

Demo

Related Topics