Other COS SQL function

1. Usage of COS to show Sales Difference

SQL Server Query 1

            
 WITH Months AS (  

     SELECT 1 AS Month  

     UNION ALL  

     SELECT Month + 1  

     FROM Months  

     WHERE Month < 12  

 ),  

 MonthlySales AS (  

     SELECT  

         DATEPART(month, o.OrderDate) AS SaleMonth,  

         SUM(od.Quantity * od.UnitPrice) AS TotalSales  

     FROM Orders o  

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

     WHERE YEAR(o.OrderDate) = 1996  --  Limit to a single year for simplicity  

     GROUP BY DATEPART(month, o.OrderDate)  

 ),  

 SalesAnalysis AS (  

     SELECT  

         m.Month AS SaleMonth,  

         COALESCE(ms.TotalSales, 0) AS ActualSales, -- Use COALESCE to handle months with no sales  

         -- Calculate a cosine wave.  Adjust amplitude and frequency as needed.  

         10000 * COS((m.Month * 2 * PI()) / 12) + 15000 AS ExpectedSales  -- Example: Amplitude=10000, Period = 12 months, Vertical Shift = 15000  

     FROM Months m  

     LEFT JOIN MonthlySales ms ON m.Month = ms.SaleMonth  

 )  

 SELECT  

     SaleMonth,  

     ActualSales,  

     ExpectedSales,  

     (ActualSales - ExpectedSales) AS SalesDifference  

 FROM SalesAnalysis  

 ORDER BY SaleMonth;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .WithCTETable(new Table("Months"), new SqlQueryBuilder().Select()  

         .Column(1, "Month")  

         .UnionAll(new SqlQueryBuilder().Select().Column(new ColumnArithmatic().Column("Month").ADD().Value(1), "Month"))  

         .From("Months")  

         .Where(new Where(new Column("Month").LessThan(12)))  

     )  

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

         .Select()  

         .Column(new DATEPART(SqlDateInterval.month, new Column("o.OrderDate")), "SaleMonth")  

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

         .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")))  

         })  

         .Where(new Where(new YEAR(new Column("o.OrderDate")).Equale(1996)))  

         .GroupBy(new GroupBy(new DATEPART(SqlDateInterval.month, new Column("o.OrderDate"))))  

     )  

     .WithCTETable(new Table("SalesAnalysis"), new SqlQueryBuilder().Select()  

         .Column("m.Month", "SaleMonth")  

         .Column(new COALESCE(new Column("ms.TotalSales"), 0), "ActualSales")  

         .Column(new ColumnArithmatic(10000).MULTIPLY(new COS(new ColumnArithmatic().StartBracket("m.Month")  

             .MULTIPLY(2).MULTIPLY(new PI()).EndBracket().DIVIDE(12))).ADD(15000), "ExpectedSales")  

         .From("Months", "m")  

         .Join(new List<IJoin>()  

         {  

             new LEFTJOIN().TableName(new Table("MonthlySales","ms"))  

                 .On(new Column("m.Month").Equale(new Column("ms.SaleMonth")))  

         })  

     )  

     .Select()  

     .Columns("SaleMonth", "ActualSales", "ExpectedSales")  

     .Column(new ColumnArithmatic("ActualSales").SUBTRACT("ExpectedSales"), "SalesDifference")  

     .From("SalesAnalysis")  

     .OrderBy(new OrderBy().SetColumnAscending("SaleMonth"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH Months
AS (SELECT @pMAIN_2507200150422152920 AS Month
    UNION ALL
    SELECT Month + @pMAIN_2507200150422152921 AS Month
    FROM Months
    WHERE Month < @pMAIN_2507200150422152922),
 MonthlySales
AS (SELECT DATEPART(month, o.OrderDate) AS SaleMonth,
           SUM(od.Quantity * od.UnitPrice) AS TotalSales
    FROM Orders AS o
         INNER JOIN
         [Order Details] AS od
         ON o.OrderID = od.OrderID
    WHERE YEAR(o.OrderDate) = @pMAIN_2507200150422152923
    GROUP BY DATEPART(month, o.OrderDate)),
 SalesAnalysis
AS (SELECT m.Month AS SaleMonth,
           COALESCE (ms.TotalSales, @pMAIN_2507200150422152924) AS ActualSales,
           @pMAIN_2507200150422152925 * COS((m.Month * @pMAIN_2507200150422152926 * PI()) / @pMAIN_2507200150422152927) + @pMAIN_2507200150422152928 AS ExpectedSales
    FROM Months AS m
         LEFT OUTER JOIN
         MonthlySales AS ms
         ON m.Month = ms.SaleMonth)
SELECT SaleMonth,
       ActualSales,
       ExpectedSales,
       ActualSales - ExpectedSales AS SalesDifference
FROM SalesAnalysis
ORDER BY SaleMonth ASC;


            
        

Parameters (If used)

Query Results 1: