CEILING SQL function

1. Usage of CEILING to number of containers for total units.

SQL Server Query 1

            
WITH OrderUnits AS (  

    SELECT  

        o.OrderID,  

        c.CompanyName AS CustomerName,  

        SUM(od.Quantity) AS TotalUnits  

    FROM Orders o  

    JOIN Customers c ON o.CustomerID = c.CustomerID  

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

    GROUP BY o.OrderID, c.CompanyName  

)  

SELECT  

    OrderID,  

    CustomerName,  

    TotalUnits,  

    CEILING(CAST(TotalUnits AS FLOAT) / 250) AS NumberOfContainers  

FROM OrderUnits  

ORDER BY OrderID;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

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

        .Select()  

        .Column("o.OrderID", "OrderID")  

        .Column("c.CompanyName", "CustomerName")  

        .Column(new SUM(new Column("od.Quantity")), "TotalUnits")  

        .From("Orders", "o")  

        .Join(new List<IJoin>()  

        {  

            new INNERJOIN().TableName(new Table("Customers","c"))  

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

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

                .On(new Column("o.OrderID"), SQLComparisonOperators.EQUALE_TO, new Column("od.OrderID"))  

        })  

        .GroupBy(new GroupBy(new Column("o.OrderID"), new Column("c.CompanyName")))  

    )  

    .Select()  

    .Columns("OrderID", "CustomerName", "TotalUnits")  

    .Column(new CEILING(new CAST(new Column("TotalUnits"), SqlDataType.FLOAT).AddArithmatic(new Arithmatic().Divide(250))), "NumberOfContainers")  

    .From("OrderUnits")  

    .OrderBy(new OrderBy().SetColumnAscending("OrderID"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH OrderUnits
AS (SELECT o.OrderID AS OrderID,
           c.CompanyName AS CustomerName,
           SUM(od.Quantity) AS TotalUnits
    FROM Orders AS o
         INNER JOIN
         Customers AS c
         ON o.CustomerID = c.CustomerID
         INNER JOIN
         [Order Details] AS od
         ON o.OrderID = od.OrderID
    GROUP BY o.OrderID, c.CompanyName)
SELECT OrderID,
       CustomerName,
       TotalUnits,
       CEILING(CAST (TotalUnits AS FLOAT) / @pMAIN_2507192021015725420) AS NumberOfContainers
FROM OrderUnits
ORDER BY OrderID ASC;


            
        

Parameters (If used)

Query Results 1: