How to use LEAD and LAG analytical functions in SQL Server?

Problem: How to use LEAD and LAG analytical functions in SQL Server?

Solution: Analytical functions are helpful to compute moving averages, running totals, percentages or top ‘n’ number of rows within a group. Starting with SQL server 2012 or higher, these analytical functions are available to use.

The following two analytical functions are useful to see values in the same table and get the next rows instead of using a self-join.

LEAD (Transact-SQL) – used for comparing the current row with the next row.

Syntax:

LAG (scalar_expression [,offset] [,default])  

    OVER ( [ partition_by_clause ] order_by_clause )  

LAG (Transact-SQL) – used for comparing the current row with the previous row.

Syntax:

LAG (scalar_expression [,offset] [,default]) 

    OVER ( [ partition_by_clause ] order_by_clause ) 

This type of analytical functions will be helpful in the following scenarios:

·         Comparing the orders on the next day, analyzing

·         Checking the gaps in between start dates and end dates of the current row and next row.

·         Finding differences, calculating running balances. For example, the transactions are in a column and you want to add the first row and the second row of the same column to be a running total.

Example:

Example 1: I have a doctor appointment table, where there are patients appointment and how to check what was his last appointment and future appointment.

-          This only works on SQL Server 2012 version or higher.

IF EXISTS ( SELECT *

      FROM sys.tables

      WHERE name = 'Doctorappointment'

                  and SCHEMA_NAME(schema_id)='dbo')

   DROP TABLE dbo.Doctorappointment;

  GO

CREATE TABLE dbo.Doctorappointment

   (

   AppointmentID int IDENTITY(1,1) PRIMARY KEY,

                PatientID INT,

    AppointmentDate  DATE ,

    AppointmentTime TIME,

                TypeofAppointment VARCHAR(256)

   );

 GO

INSERT INTO dbo.Doctorappointment

   (PatientID, AppointmentDate, AppointmentTime,TypeofAppointment )

VALUES

   ( 4464, '2017-01-07', '08:00','Phyiscal Checkup'),

   ( 79879, '2017-01-07', '08:10', 'consulting'),

   (1231 , '2017-01-07', '11:30','Phyiscal Checkup'),

   (1124 , '2017-01-07', '11:35','dental Checkup'),

   (16546 , '2017-01-07', '12:45','Follow up Visit'),

   ( 4646, '2017-01-07', '16:45','General Checkup'),

   (1124 , '2017-01-07', '17:30','Follow up Visit'),

   (79879 , '2017-01-07', '1:00','General Checkup'),

   ( 7987, '2017-01-07', '08:33','General Checkup'),

   (789 , '2017-01-07', '17:33','General Checkup'),

   (3366 , '2017-01-07', '17:35','General Checkup');

GO

-- This will show a patient's last appointment date and which type of appointment did he comefor last time.

   SELECT Patientid ,

       TypeofAppointment ,

       CAST(AppointmentDate AS DATETIME) + CAST(AppointmentTime AS DATETIME) AS EventDateTime ,

       LAG(TypeofAppointment, 1, 'No Previous Appointment') OVER ( PARTITION BY PatientID ORDER BY AppointmentDate, AppointmentTime ) AS PreviousAppointment ,

       LEAD(TypeofAppointment, 1, 'No Future Appointment') OVER ( PARTITION BY PatientID ORDER BY AppointmentDate, AppointmentTime ) AS FutureAppointment

FROM dbo.Doctorappointment;

GO

OUTPUT:

Patientid	TypeofAppointment	EventDateTime	PreviousAppointment	FutureAppointment
789	General Checkup	2017-01-07 17:33:00.000	No Previous Appointment	No Future Appointment
1124	dental Checkup	2017-01-07 11:35:00.000	No Previous Appointment	Follow up Visit
1124	Follow up Visit	2017-01-07 17:30:00.000	dental Checkup	No Future Appointment
1231	Phyiscal Checkup	2017-01-07 11:30:00.000	No Previous Appointment	No Future Appointment
3366	General Checkup	2017-01-07 17:35:00.000	No Previous Appointment	No Future Appointment
4464	Phyiscal Checkup	2017-01-07 08:00:00.000	No Previous Appointment	No Future Appointment
4646	General Checkup	2017-01-07 16:45:00.000	No Previous Appointment	No Future Appointment
7987	General Checkup	2017-01-07 08:33:00.000	No Previous Appointment	No Future Appointment
16546	Follow up Visit	2017-01-07 12:45:00.000	No Previous Appointment	No Future Appointment
79879	General Checkup	2017-01-07 01:00:00.000	No Previous Appointment	consulting
79879	consulting	2017-01-07 08:10:00.000	General Checkup	No Future Appointment