Lag salary over, lead salary over : LEAD LAG « Analytical Functions « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Analytical Functions
3. LEAD LAG

SQL>
SQL> create table history
  2  ( empno      NUMBER(4)
  3  , beginyear  NUMBER(4)
  4  , begindate  DATE
  5  , enddate    DATE
  6  , deptno     NUMBER(2)
  7  , sal        NUMBER(6,2)
  8  , comments   VARCHAR2(60)
  9  , constraint H_PK         primary key          (empno,begindate)
 10  , constraint H_BEG_END    check                (begindate < enddate)
 11  ) ;

Table created.

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

Session altered.

SQL>
SQL> insert into history values (1,2000,'01-01-2000','01-02-2000',40, 950,'');

1 row created.

SQL> insert into history values (1,2000,'01-02-2000', NULL       ,20, 800,'restarted');

1 row created.

SQL> insert into history values (2,2009,'01-11-2009', NULL       ,30,1600,'just hired');

1 row created.

SQL>
SQL>
SQL> select empno, begindate, sal
  2  ,      LAG(sal) over
  3         ( partition by empno
  4           order by empno, begindate
  5         ) as prev_sal
  6  ,      LEAD(sal) over
  7         ( partition by empno
  8           order by empno, begindate
  9         ) as next_sal
 10  from   history
 11  order  by empno, begindate;

     EMPNO BEGINDATE         SAL   PREV_SAL   NEXT_SAL
---------- ---------- ---------- ---------- ----------
         1 01-01-2000        950                   800
         1 01-02-2000        800        950
         2 01-11-2009       1600

SQL>
SQL> drop table history;

Table dropped.

SQL>