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.
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>