LEAD AND LAG FUNCTION [ For more : Comment your mail address to send as example DOC]

The functions access data from a subsequent row (lead) and previous row (lag) in the same result set without the use of a self-join. The syntax for the Lead and Lag functions is: LAG|LEAD (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause ) Let me explain using this example which creates table Test_table in database TestDB and inserts some data. CREATE DATABASE [TestDB] --Create testable to hold some data CREATE TABLE [dbo].[Test_table]( [id] [int] IDENTITY(1,1) NOT NULL, [Department] [nchar](10) NOT NULL, [Code] [int] NOT NULL, CONSTRAINT [PK_Test_table] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO --Insert some test data insert into Test_table values('A',1) insert into Test_table values('B',29) insert into Test_table values('C',258) insert into Test_table values('D',3) insert into Test_table values('E',15) insert into Test_table values('F',449) insert into Test_table values('G',419) insert into Test_table values('H',5) insert into Test_table values('I',524) insert into Test_table values('J',698) insert into Test_table values('K',715) insert into Test_table values('L',799) insert into Test_table values('M',139) insert into Test_table values('N',219) insert into Test_table values('O',869) Our table data will look like this: Now the query for lead value and lag value will be: SELECT id,department,Code, LEAD(Code,1) OVER (ORDER BY Code ) LeadValue, LAG(Code,1) OVER (ORDER BY Code ) LagValue FROM test_table In the above example, for the first row the Lead value is the value of the next row because the offset is set to1. The Lag value is NULL because there were no previous rows. Now if we change the Lead offset to2 and Lag offset to3 the output will be as follows: