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;