Window LAST_VALUE SQL function

1. Usage of LAST_VALUE to find the Last Order Date for Each Customer

SQL Server Query 1

            
 SELECT  

     c.CustomerID,  

     c.CompanyName,  

     o.OrderID,  

     o.OrderDate,  

     LAST_VALUE(o.OrderDate) OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate ASC  

                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastOrderDate  

 FROM Customers c  

 JOIN Orders o ON c.CustomerID = o.CustomerID  

 ORDER BY c.CustomerID, o.OrderDate;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("c.CustomerID", "c.CompanyName", "o.OrderID", "o.OrderDate")  

     .Column(new LAST_VALUE(new Column("o.OrderDate")).PARTITION_BY(new Column("c.CustomerID"))                      

         .ORDER_BY(new OrderBy().SetWithUnbound(new Column("o.OrderDate"), OrderByDirection.ASCENDING))  

         , "LastOrderDate")  

     .From("Customers", "c")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("Orders","o")  

             .On(new Column("c.CustomerID").Equale(new Column("o.CustomerID")))  

     })  

     .OrderBy(new OrderBy()  

         .SetColumnAscending("c.CustomerID")  

         .SetColumnAscending("o.OrderDate")  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT c.CustomerID,
       c.CompanyName,
       o.OrderID,
       o.OrderDate,
       LAST_VALUE(o.OrderDate) OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastOrderDate
FROM Customers AS c
     INNER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
ORDER BY c.CustomerID ASC, o.OrderDate ASC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of LAST_VALUE to compare Current Order Date to the Customer's Last Order Date

SQL Server Query 2

            
 SELECT  

     c.CustomerID,  

     c.CompanyName,  

     o.OrderID,  

     o.OrderDate,  

     LAST_VALUE(o.OrderDate) OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate ASC  

                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastOrderDate,  

     CASE  

         WHEN o.OrderDate = LAST_VALUE(o.OrderDate) OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate ASC  

                                                           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  

         THEN 'Last Order'  

         ELSE ''  

     END AS OrderType  

 FROM Customers c  

 JOIN Orders o ON c.CustomerID = o.CustomerID  

 ORDER BY c.CustomerID, o.OrderDate;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Columns("c.CustomerID", "c.CompanyName", "o.OrderID", "o.OrderDate")  

     .Column(new LAST_VALUE(new Column("o.OrderDate")).PARTITION_BY(new Column("c.CustomerID"))  

         .ORDER_BY(new OrderBy().SetWithUnbound(new Column("o.OrderDate"), OrderByDirection.ASCENDING))  

     , "LastOrderDate")  

     .Column(new CASE()  

         .When(new Column("o.OrderDate").Equale(new LAST_VALUE(new Column("o.OrderDate")).PARTITION_BY(new Column("c.CustomerID"))  

             .ORDER_BY(new OrderBy().SetWithUnbound(new Column("o.OrderDate"), OrderByDirection.ASCENDING))))  

         .Then("LAST Order").Else(new SPACE(0))  

     , "OrderType")  

     .From("Customers", "c")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName(new Table("Orders", "o"))  

             .On(new Column("c.CustomerID").Equale(new Column("o.CustomerID")))  

     })  

     .OrderBy(new OrderBy()  

         .SetColumnAscending("c.CustomerID")  

         .SetColumnAscending("o.OrderDate")  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT c.CustomerID,
       c.CompanyName,
       o.OrderID,
       o.OrderDate,
       LAST_VALUE(o.OrderDate) OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastOrderDate,
       CASE WHEN o.OrderDate = LAST_VALUE(o.OrderDate) OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) THEN @pMAIN_2507200144083036690 ELSE SPACE(@pMAIN_2507200144083036691) END AS OrderType
FROM Customers AS c
     INNER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
ORDER BY c.CustomerID ASC, o.OrderDate ASC;


            
        

Parameters (If used)

Query Results 2: