Window LAG SQL function

1. Usage of LAG to show year wise order details

SQL Server Query 1

            
 WITH MonthlyOrders AS (  

     SELECT  

         YEAR(OrderDate) AS OrderYear,  

         MONTH(OrderDate) AS OrderMonth,  

         COUNT(*) AS TotalOrders  

     FROM  

         Orders  

     GROUP BY  

         YEAR(OrderDate),  

         MONTH(OrderDate)  

 ),  

 LaggedOrders AS (  

     SELECT  

         OrderYear,  

         OrderMonth,  

         TotalOrders,  

         LAG(TotalOrders, 1, 0) OVER (ORDER BY OrderYear, OrderMonth) AS PreviousMonthOrders,  

         LAG(TotalOrders, 3, 0) OVER (ORDER BY OrderYear, OrderMonth) AS ThreeMonthsAgoOrders  

     FROM  

         MonthlyOrders  

 )  

 SELECT  

     OrderYear,  

     OrderMonth,  

     TotalOrders,  

     PreviousMonthOrders,  

     (TotalOrders - PreviousMonthOrders) AS MonthOverMonthDifference,  

     ThreeMonthsAgoOrders,  

     (TotalOrders - ThreeMonthsAgoOrders) AS ThreeMonthDifference  

 FROM  

     LaggedOrders  

 ORDER BY  

     OrderYear,  

     OrderMonth;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .WithCTETable(new Table("MonthlyOrders"), new SqlQueryBuilder()  

         .Select()  

         .Column(new YEAR(new Column("OrderDate")), "OrderYear")  

         .Column(new MONTH(new Column("OrderDate")), "OrderMonth")  

         .Column(new COUNT(new Column("OrderDate")), "TotalOrders")  

         .From("Orders")  

         .GroupBy(new GroupBy(new YEAR(new Column("OrderDate")), new MONTH(new Column("OrderDate"))))  

     )  

     .WithCTETable(new Table("LaggedOrders"), new SqlQueryBuilder()  

         .Select()  

         .Columns("OrderYear", "OrderMonth", "TotalOrders")  

         .Column(new LAG(new Column("TotalOrders"), 1, 0)  

             .ORDER_BY(new OrderBy().SetColumnAscending("OrderYear").SetColumnAscending("OrderMonth"))  

         , "PreviousMonthOrders")  

         .Column(new LAG(new Column("TotalOrders"), 3, 0)  

             .ORDER_BY(new OrderBy().SetColumnAscending("OrderYear").SetColumnAscending("OrderMonth"))  

         , "ThreeMonthsAgoOrders")  

         .From("MonthlyOrders")  

     )  

     .Select()  

     .Columns("OrderYear", "OrderMonth", "TotalOrders", "PreviousMonthOrders")  

     .Column(new ColumnArithmatic().StartBracket().Column("TotalOrders").ADD().Column("PreviousMonthOrders").EndBracket(), "MonthOverMonthDifference")  

     .Column("ThreeMonthsAgoOrders", "ThreeMonthsAgoOrders")  

     .Column(new ColumnArithmatic().StartBracket().Column("TotalOrders").ADD().Column("ThreeMonthsAgoOrders").EndBracket(), "ThreeMonthDifference")  

     .From("LaggedOrders")  

     .OrderBy(new OrderBy().SetColumnAscending("OrderYear").SetColumnAscending("OrderMonth"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH MonthlyOrders
AS (SELECT YEAR(OrderDate) AS OrderYear,
           MONTH(OrderDate) AS OrderMonth,
           COUNT(OrderDate) AS TotalOrders
    FROM Orders
    GROUP BY YEAR(OrderDate), MONTH(OrderDate)),
 LaggedOrders
AS (SELECT OrderYear,
           OrderMonth,
           TotalOrders,
           LAG(TotalOrders, @pMAIN_2507200154055425500, @pMAIN_2507200154055425501) OVER (ORDER BY OrderYear ASC, OrderMonth ASC) AS PreviousMonthOrders,
           LAG(TotalOrders, @pMAIN_2507200154055425502, @pMAIN_2507200154055425503) OVER (ORDER BY OrderYear ASC, OrderMonth ASC) AS ThreeMonthsAgoOrders
    FROM MonthlyOrders)
SELECT OrderYear,
       OrderMonth,
       TotalOrders,
       PreviousMonthOrders,
       (TotalOrders + PreviousMonthOrders) AS MonthOverMonthDifference,
       ThreeMonthsAgoOrders AS ThreeMonthsAgoOrders,
       (TotalOrders + ThreeMonthsAgoOrders) AS ThreeMonthDifference
FROM LaggedOrders
ORDER BY OrderYear ASC, OrderMonth ASC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of LAG to show year wise order details for a company

SQL Server Query 2

            
 WITH  CustomerMonthlyOrders  AS  (   

 	SELECT   

 		c.CustomerID, c.CompanyName,   

 		YEAR(o.OrderDate) AS OrderYear,   

 		MONTH(o.OrderDate) AS OrderMonth,   

 		COUNT(o.OrderDate) AS TotalOrders   

 	FROM Customers c    

 		LEFT JOIN Orders  o  ON c.CustomerID = o.CustomerID    

 	GROUP BY c.CustomerID, c.CompanyName, YEAR(o.OrderDate), MONTH(o.OrderDate)),     

 LaggedCustomerOrders  AS  (   

 	SELECT CustomerID, CompanyName, OrderYear, OrderMonth, TotalOrders,   

 		LAG(TotalOrders,  1,  0) OVER (PARTITION BY CustomerID ORDER BY OrderYear ASC,OrderMonth ASC ) AS PreviousMonthOrders   

 	FROM CustomerMonthlyOrders    

 )    

 SELECT CustomerID, CompanyName, OrderYear, OrderMonth, TotalOrders, PreviousMonthOrders,   

 	(TotalOrders - PreviousMonthOrders) MonthOverMonthDifference  FROM LaggedCustomerOrders   

 ORDER BY CustomerID ASC,OrderYear ASC,OrderMonth ASC;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .WithCTETable(new Table("CustomerMonthlyOrders"), new SqlQueryBuilder()  

         .Select()  

         .Columns("c.CustomerID", "c.CompanyName")  

         .Column(new YEAR(new Column("o.OrderDate")), "OrderYear")  

         .Column(new MONTH(new Column("o.OrderDate")), "OrderMonth")  

         .Column(new COUNT(new Column("o.OrderDate")), "TotalOrders")  

         .From("Customers", "c")  

         .Join(new List<IJoin>()  

         {  

             new Join(SQLJoin.LEFTJOIN).TableName(new Table("Orders"," o"))  

                 .On(new Column("c.CustomerID"), SQLComparisonOperators.EQUALE_TO, new Column("o.CustomerID"))  

         })  

         .GroupBy(new GroupBy(  

             new Column("c.CustomerID"),  

             new Column("c.CompanyName"),  

             new YEAR(new Column("o.OrderDate")),  

             new MONTH(new Column("o.OrderDate"))  

         ))  

     )  

     .WithCTETable(new Table("LaggedCustomerOrders"), new SqlQueryBuilder()  

         .Select()  

         .Columns("CustomerID", "CompanyName", "OrderYear", "OrderMonth", "TotalOrders")  

         .Column(new LAG(new Column("TotalOrders"), 1, 0)  

             .PARTITION_BY(new Column("CustomerID"))  

             .ORDER_BY(new OrderBy().SetColumnAscending("OrderYear").Set(new Column("OrderMonth")))  

             , "PreviousMonthOrders")  

         .From("CustomerMonthlyOrders")  

     )  

     .Select()  

     .Columns("CustomerID", "CompanyName", "OrderYear", "OrderMonth", "TotalOrders", "PreviousMonthOrders")  

     .Column(new ColumnArithmatic().StartBracket().Column("TotalOrders").SUBTRACT().Column("PreviousMonthOrders").EndBracket(), "MonthOverMonthDifference")  

     .From("LaggedCustomerOrders")  

     .OrderBy(new OrderBy()  

         .SetColumnAscending("CustomerID")  

         .SetColumnAscending("OrderYear")  

         .SetColumnAscending("OrderMonth")  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
WITH CustomerMonthlyOrders
AS (SELECT c.CustomerID,
           c.CompanyName,
           YEAR(o.OrderDate) AS OrderYear,
           MONTH(o.OrderDate) AS OrderMonth,
           COUNT(o.OrderDate) AS TotalOrders
    FROM Customers AS c
         LEFT OUTER JOIN
         Orders AS o
         ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerID, c.CompanyName, YEAR(o.OrderDate), MONTH(o.OrderDate)),
 LaggedCustomerOrders
AS (SELECT CustomerID,
           CompanyName,
           OrderYear,
           OrderMonth,
           TotalOrders,
           LAG(TotalOrders, @pMAIN_2507200154055679590, @pMAIN_2507200154055679591) OVER (PARTITION BY CustomerID ORDER BY OrderYear ASC, OrderMonth ASC) AS PreviousMonthOrders
    FROM CustomerMonthlyOrders)
SELECT CustomerID,
       CompanyName,
       OrderYear,
       OrderMonth,
       TotalOrders,
       PreviousMonthOrders,
       (TotalOrders - PreviousMonthOrders) AS MonthOverMonthDifference
FROM LaggedCustomerOrders
ORDER BY CustomerID ASC, OrderYear ASC, OrderMonth ASC;


            
        

Parameters (If used)

Query Results 2: