Friday, August 26, 2016

Using Lag() in SQL Server 2012 to retrieve previous row value

I have this old T-SQL script that retrieves previous row value of a certain order year which uses a combination of derived tables, left join and group by to achieve the desired result.
Upon visiting this script and doing some research on new T-SQL functions, I encountered an article from SQL Authority on How To Access Previous Row and Next Row in Select statement. So, given the time I quickly converted my script using Lag() function and was surprised to see that my new script looks clean and orderly compared to the old one.
Old T-SQL Script
SELECT 
       Curr_Data.Year_Order,
       Curr_Data.Customer_Count AS Customer_Count,
       Prev_Data.Customer_Count AS Previous_Customer_Count,
       Curr_Data.Customer_Count - Prev_Data.Customer_Count AS Growth
FROM
  (SELECT YEAR(Orderdate) AS Year_Order,
          COUNT(DISTINCT CustomerID) AS Customer_Count
   FROM CustomerOrders
   GROUP BY YEAR(Orderdate)) AS Curr_Data
LEFT OUTER JOIN
  (SELECT YEAR(Orderdate) AS Year_Order,
          COUNT(DISTINCT CustomerID) AS Customer_Count
   FROM CustomerOrders
   GROUP BY YEAR(Orderdate)) AS Prev_Data ON Curr_Data.Year_Order = Prev_Data.Year_Order + 1;

Updated T-SQL Script
Select 
 Year(Orderdate) as Year_Order,
 Count(distinct CustomerID) as Customer_Count,
 LAG(Count(distinct CustomerID)) OVER (ORDER BY Year(Orderdate)) 
  As Previous_Customer_Count,
 Count(distinct CustomerID) - (LAG(Count(distinct CustomerID)) OVER (ORDER BY Year(Orderdate))) 
  as Growth
From CustomerOrders
Group By 
 Year(Orderdate);

0 comments:

Post a Comment