Feeds:
Posts
Comments

Posts Tagged ‘SQL Server 2012 – Analytic Functions – LEAD’

Today, I was developing a customer analysis report, basically trying to find out customer’s trend of purchasing. To get the trend I need to get his next row (purchasing) result set in the current row for comparison purposes.  Fortunately, we do have a solution for this problem using self join. But I will share another efficient solution, using LEAD (an analytic function shipped with SQL Server 2012).

Let me create a sample to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
 [ID] int,
 [Levels] varchar(50)
)
GO

INSERT INTO tbl_sample VALUES (1,'LEVEL 1')
INSERT INTO tbl_sample VALUES (2,'LEVEL 2')
INSERT INTO tbl_sample VALUES (3,'LEVEL 3')
INSERT INTO tbl_sample VALUES (4,'LEVEL 4')
INSERT INTO tbl_sample VALUES (5,'LEVEL 5')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

get the previous row result.1.1

Let me show you both old and new approaches.

Old Approaches :
Given below is the old approach that we generally develop using self join. This approach you can use in any version of SQL Server.

USE tempdb
GO
SELECT
 A.ID
,A.Levels As [Current Level]
,B.Levels AS [Next Level]
FROM tbl_sample A
LEFT JOIN tbl_sample B ON A.ID+1=B.ID
ORDER BY A.ID
GO
--OUTPUT

get value from next row

New Approaches :
In this approach, you do not need to do self join and make it complicated. You just need to use LEAD function and it will calculate the next result row for you automatically. This approach can be used in SQL Server 2012 and above.

USE tempdb
GO
SELECT
 A.ID
,A.Levels As [Current Level]
,LEAD(A.levels,1,0) OVER (ORDER BY A.ID) AS [Next Level]
FROM tbl_sample A
GO
--OUTPUT

get value from next row

Conclusion:
In the above approaches, you can see that the result set are same but the new approaches reduce the complexity and increase the performance.

Advertisements

Read Full Post »

In my previous article, I discussed about LAG. Today, we will discuss another analytical function namely“LEAD” in SQL SERVER 2012. This function performs exactly opposite functionality of LAG. In simple words we can say that if we need to pick up any value from the next row(s) of the current row, we can use this function and get it, instead of using self joins.

Let me explain its syntax, parameters, purpose and examples in detail.
Syntax :

    LEAD ( scalar_expression [ ,offset ] , [ default ] ) 
    OVER ( [ partition_by_clause ] order_by_clause )

Parameters :

@scalar_expression :The value to be returned based on the offset. (Mandatory)
@offset : The number of rows ahead of the rows from where we need to pick the value. Default value is 1 (Optional)
@default : If @scalar_expression is NULL, then you can define any default value here to be returned (Optional)

Purpose :
The purpose of this function is to get the next row in front of the current row in the same result without using any self joins. And once you get the next row parallel to current, you can easily compare their values and perform any analytical task.

Lets create an example to explain “LEAD” function in SQL SERVER 2012.

USE tempdb
GO
Create Table [Test_Table]
(
[S.No] int,
[Letters] varchar(50)
)
GO
Insert into [Test_Table] values (1,'LETTER A')
Insert into [Test_Table] values (2,'LETTER B')
Insert into [Test_Table] values (3,'LETTER C')
Insert into [Test_Table] values (5,'LETTER D')
Insert into [Test_Table] values (6,'LETTER E')
Insert into [Test_Table] values (7,'LETTER F')
Insert into [Test_Table] values (8,'LETTER G')
Insert into [Test_Table] values (9,'LETTER H')
Insert into [Test_Table] values (10,'LETTER I')

Let me explain this with simple examples.

Example-1 : LEAD- WITH DEFAULT VALUES

Select [S.No],[Letters]
,LEAD([Letters]) Over (Order By [Letters]) as [Next Value]
from  [Test_Table]

Example-2 : LEAD – REPLACE DEFAULT VALUE NULL WITH EMPTY STRING
In the above example, you can view that if LEAD could not find the value it gives you NULL. In order to avoid NULL, you can pass the third parameter and it will be replaced by NULL.
In the example given below, I replaced NULL with empty spaces (”).

Select [S.No],[Letters]
,LEAD([Letters],1,'') Over (Order By [Letters]) as [Next Value]
from  [Test_Table]

Example-3 : LEAD – CHANGE OFFSET VALUE FROM DEFAULT TO 2
In the above examples, you can view that LEAD function picks one row subsequent value than current row.
In this example, we will set the offset value to 2 to pick up two rows’ next value than current.

Select [S.No],[Letters]
,LEAD([Letters],2,'') Over (Order By [Letters]) as [Next Value]
from  [Test_Table]

Example-4 : LEAD – REAL WORLD – FIND Promotions of Employees
In the earlier version of SQL SERVER, if you need to find current designation with the promotion of any employee in the same result set , you need to use the self join to achieve it. But in SQL SERVER 2012, you can use the LEAD function to achieve it.

Lets create an example to explain this.

Use tempdb
Create table [tbl_Promotion]
(
[S.No] int,
[Date] datetime,
[Employee Name] varchar(50),
[Designation]  varchar(50)
)
 
Insert into [tbl_Promotion] values(1,'2009-08-20','Imran','Assistant Manager')
Insert into [tbl_Promotion] values(2,'2011-11-21','Imran','Manager')
 
Insert into [tbl_Promotion] values(3,'2010-09-05','Bob','Technical Manager')
Insert into [tbl_Promotion] values(4,'2012-10-06','Bob','Technical Director')
 
Insert into [tbl_Promotion] values(5,'2012-01-10','Robert','Junior Developer')
Insert into [tbl_Promotion] values(6,'2012-06-11','Robert','Developer')
 
GO
Select * from [tbl_Promotion]
 
GO
 
Select [S.No],[Date],[Employee Name],[Designation]
,LEAD ([Designation],1,0) Over (Partition By [Employee Name] Order By [S.No]) as [Next Promotion]
from [tbl_Promotion]
Go
Drop Table [tbl_Promotion]

Reference :MSDN

Read Full Post »