POWER SQL function

1. Usage of POWER to find discount percentage of orders.

SQL Server Query 1

            
WITH CustomerOrderCounts AS (  

    SELECT  

        c.CustomerID,  

        c.CompanyName,  

        COUNT(o.OrderID) AS NumberOfOrders  

    FROM Customers c  

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

    GROUP BY c.CustomerID, c.CompanyName  

)  

SELECT  

    CustomerID,  

    CompanyName,  

    NumberOfOrders,  

    0.10 * POWER(0.5, NumberOfOrders) AS DiscountPercentage  

FROM CustomerOrderCounts  

ORDER BY NumberOfOrders DESC;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

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

        .Select()  

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

        .Column(new COUNT(new Column("o.OrderID")), "NumberOfOrders")  

        .From("Customers", "c")  

        .Join(new List<IJoin>()  

        {  

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

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

        })  

        .GroupBy(new GroupBy(new Column("c.CustomerID"), new Column("c.CompanyName")))  

    )  

    .Select()  

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

    .Column(new ColumnArithmatic(0.10).MULTIPLY(new POWER(0.5, new Column("NumberOfOrders"))), "DiscountPercentage")  

    .From("CustomerOrderCounts")  

    .OrderBy(new OrderBy().SetColumnDescending("NumberOfOrders"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH CustomerOrderCounts
AS (SELECT c.CustomerID,
           c.CompanyName,
           COUNT(o.OrderID) AS NumberOfOrders
    FROM Customers AS c
         LEFT OUTER JOIN
         Orders AS o
         ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerID, c.CompanyName)
SELECT CustomerID,
       CompanyName,
       NumberOfOrders,
       @pMAIN_2507192031222255270 * POWER(@pMAIN_2507192031222255271, NumberOfOrders) AS DiscountPercentage
FROM CustomerOrderCounts
ORDER BY NumberOfOrders DESC;


            
        

Parameters (If used)

Query Results 1: