Using Lag() In SQL Server 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
Updated T-SQL Script
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);
Comments
Post a Comment