Window SUM SQL function

1. Usage of SUM to find next discount available for customers

SQL Server Query 1

            
SELECT   

    o.CustomerID,  

    o.OrderDate,  

    od.Quantity,  

    od.UnitPrice,  

    (od.Quantity * od.UnitPrice) AS OrderItemTotal,  

    SUM(od.Quantity * od.UnitPrice) OVER (PARTITION BY o.CustomerID ORDER BY o.OrderDate) AS CustomerRunningTotal,  

    SUM(od.Quantity * od.UnitPrice) OVER (PARTITION BY o.CustomerID) AS CustomerTotal,  

    CASE   

        WHEN SUM(od.Quantity * od.UnitPrice) OVER (PARTITION BY o.CustomerID) - 1000 > 3500 THEN '500 Discount on Next Order'  

        WHEN SUM(od.Quantity * od.UnitPrice) OVER (PARTITION BY o.CustomerID) - 1000 > 2500 THEN '250 Discount on Next Order'  

        ELSE 'Continue Shopping...' END AS NextOffer  

FROM Orders AS o  

JOIN [Order Details] AS od ON o.OrderID = od.OrderID  

ORDER BY o.CustomerID, o.OrderDate;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql5, parameters5) = new SqlQueryBuilder()  

    .Select()  

    .Columns("o.CustomerID", "o.OrderDate", "od.Quantity", "od.UnitPrice")  

    .Column(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice"), "OrderItemTotal")  

    .Column(new SUM(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice")).OVER(  

            new OVER().PARTITION_BY(new Column("o.CustomerID")).ORDER_BY(new OrderBy().Set(new Column("o.OrderDate")))  

        ), "CustomerRunningTotal")  

    .Column(new SUM(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice"))  

        .OVER(new OVER().PARTITION_BY(new Column("o.CustomerID")))  

    , "CustomerTotal")  

    .Column(new CASE()  

        .When(new SUM(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice"))  

            .OVER(new OVER().PARTITION_BY(new Column("CustomerID")))  

            .AddArithmatic(new Arithmatic().Subtract(1000)).GreaterThan(3500))  

        .Then("500 Discount on Next Order")  

        .When(new SUM(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice"))  

                .OVER(new OVER().PARTITION_BY(new Column("CustomerID")))  

                .AddArithmatic(new Arithmatic().Subtract(1000)).GreaterThan(2500))  

        .Then("250 Discount on Next Order")  

        .Else("Continue Shopping..."), "NextOffer")  

    .From("Orders", "o")  

    .Join(new List<IJoin>()  

    {  

        new INNERJOIN().TableName(new Table("[Order Details]", "od"))  

            .On(new Column("o.OrderID").Equale(new Column("od.OrderID")))  

    })  

    .OrderBy(new OrderBy()  

        .SetColumnAscending("o.CustomerID")  

        .SetColumnAscending("o.OrderDate"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT o.CustomerID,
       o.OrderDate,
       od.Quantity,
       od.UnitPrice,
       od.Quantity * od.UnitPrice AS OrderItemTotal,
       SUM(od.Quantity * od.UnitPrice) OVER (PARTITION BY o.CustomerID ORDER BY o.OrderDate ASC) AS CustomerRunningTotal,
       SUM(od.Quantity * od.UnitPrice) OVER (PARTITION BY o.CustomerID) AS CustomerTotal,
       CASE WHEN SUM(od.Quantity * od.UnitPrice) OVER (PARTITION BY CustomerID) - @pMAIN_2507192107352316840 > @pMAIN_2507192107352316841 THEN @pMAIN_2507192107352316842 WHEN SUM(od.Quantity * od.UnitPrice) OVER (PARTITION BY CustomerID) - @pMAIN_2507192107352316843 > @pMAIN_2507192107352316844 THEN @pMAIN_2507192107352316845 ELSE @pMAIN_2507192107352316846 END AS NextOffer
FROM Orders AS o
     INNER JOIN
     [Order Details] AS od
     ON o.OrderID = od.OrderID
ORDER BY o.CustomerID ASC, o.OrderDate ASC;


            
        

Parameters (If used)

Query Results 1: