Monday, May 26, 2008

Lead / Lag


This example is a little silly, but I'm trying to use the tables that come with the Oracle database so anyone can paste them in and run them. This is showing how many days between hirings within each department. The lead statement Lead(Hiredate) Over (Partition By Deptno Order By Hiredate) basically creates a field in the current record based on a field in the next record. It is broken down (or partitioned by Deptno), and is in the Hiredate order. Lag would work in the opposite direction. Obviously, the last one in the department wouldn't have a value since there is no record behind it in that 'partition.' This can work well for calculating time if there are separate records for when something is started and stopped.
SQL> Set Pagesize 60
Break On Deptno Dup Skip 1
Select EmpNo,
EName,
Job,
DeptNo,
HireDate,
----------------------------------------------------------------
Lead(HireDate) Over (Partition By DeptNo
Order By HireDate)
- Hiredate As
Days_Between_Hirings
----------------------------------------------------------------
From Emp
Where DeptNo <> 30 -- just to limit output
Order By DeptNo,
HireDate;

No comments:

Post a Comment