Other COT SQL function


1. Usage of COT to show Sales Ratio and Cotangent Of Ratio

SQL Server Query 1

            
 WITH CategorySales AS (  
SELECT
c.CategoryName,
SUM(od.Quantity * od.UnitPrice) AS TotalSales
FROM Categories c
JOIN Products p ON c.CategoryID = p.CategoryID
JOIN [Order Details] od ON p.ProductID = od.ProductID
JOIN Orders o ON od.OrderID = o.OrderID
WHERE c.CategoryName IN ('Beverages', 'Condiments') -- Limiting to two categories for the example
GROUP BY c.CategoryName
),
SalesRatioAnalysis AS (
SELECT
b.CategoryName AS Category1,
b.TotalSales AS Sales1,
c.CategoryName AS Category2,
c.TotalSales AS Sales2,
CAST(b.TotalSales AS FLOAT) / CAST(c.TotalSales AS FLOAT) AS SalesRatio,
-- Calculate the cotangent of the sales ratio. Handle potential division by zero.
CASE
WHEN c.TotalSales = 0 THEN NULL -- Or some other appropriate value, like a very large number
ELSE COT(CAST(b.TotalSales AS FLOAT) / CAST(c.TotalSales AS FLOAT))
END AS CotangentOfRatio
FROM CategorySales b
CROSS JOIN CategorySales c -- Simplest way to get the ratio between the two.
WHERE b.CategoryName = 'Beverages' AND c.CategoryName = 'Condiments'
)
SELECT
Category1,
Sales1,
Category2,
Sales2,
SalesRatio,
CotangentOfRatio
FROM SalesRatioAnalysis;

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  
.WithCTETable(new Table("CategorySales"), new SqlQueryBuilder()
.Select()
.Column("c.CategoryName", "CategoryName")
.Column(new SUM(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice")), "TotalSales")
.From("Categories", "c")
.Join(new List<IJoin>()
{
new INNERJOIN().TableName(new Table("Products","p"))
.On(new Column("c.CategoryID").Equale(new Column("p.CategoryID"))),
new INNERJOIN().TableName(new Table("[Order Details]", "od"))
.On(new Column("p.ProductID").Equale(new Column("od.ProductID"))),
new INNERJOIN ().TableName(new Table("Orders","o"))
.On(new Column("od.OrderID").Equale(new Column("o.OrderID")))
})
.Where(new Where(new IN(new Column("c.CategoryName"), "Beverages", "Condiments")))
.GroupBy(new GroupBy(new Column("c.CategoryName")))
)
.WithCTETable(new Table("SalesRatioAnalysis"), new SqlQueryBuilder()
.Select()
.Column("b.CategoryName", "Category1")
.Column("b.TotalSales", "Sales1")
.Column("c.CategoryName", "Category2")
.Column("c.TotalSales", "Sales2")
.Column(new ColumnArithmatic(new CAST(new Column("b.TotalSales"), SqlDataType.FLOAT))
.DIVIDE(new CAST(new Column("c.TotalSales"), SqlDataType.FLOAT)), "SalesRatio")
.Column(new CASE()
.When(new Column("c.TotalSales").Equale(0))
.Then(new Column("NULL"))
.Else(new COT(new ColumnArithmatic(new CAST(new Column("b.TotalSales"), SqlDataType.FLOAT))
.DIVIDE(new CAST(new Column("c.TotalSales"), SqlDataType.FLOAT))
)), "CotangentOfRatio")
.From("CategorySales", "b")
.Join(new List<IJoin>()
{
new CROSSJOIN().TableName(new Table("CategorySales","c"))
})
.Where(new Where(new Column("b.CategoryName").Equale("Beverages"))
.AND(new Column("c.CategoryName").Equale("Condiments")))
)
.Select()
.Columns("Category1", "Sales1", "Category2", "Sales2", "SalesRatio", "CotangentOfRatio")
.From("SalesRatioAnalysis")
.Build();

Query build by SqlQueryBuilder 1

            
WITH CategorySales
AS (SELECT c.CategoryName AS CategoryName,
           SUM(od.Quantity * od.UnitPrice) AS TotalSales
    FROM Categories AS c
         INNER JOIN
         Products AS p
         ON c.CategoryID = p.CategoryID
         INNER JOIN
         [Order Details] AS od
         ON p.ProductID = od.ProductID
         INNER JOIN
         Orders AS o
         ON od.OrderID = o.OrderID
    WHERE c.CategoryName IN (@pMAIN_2507200211126196170, @pMAIN_2507200211126196171)
    GROUP BY c.CategoryName),
 SalesRatioAnalysis
AS (SELECT b.CategoryName AS Category1,
           b.TotalSales AS Sales1,
           c.CategoryName AS Category2,
           c.TotalSales AS Sales2,
           CAST (b.TotalSales AS FLOAT) / CAST (c.TotalSales AS FLOAT) AS SalesRatio,
           CASE WHEN c.TotalSales = @pMAIN_2507200211126196172 THEN NULL ELSE COT(CAST (b.TotalSales AS FLOAT) / CAST (c.TotalSales AS FLOAT)) END AS CotangentOfRatio
    FROM CategorySales AS b CROSS JOIN CategorySales AS c
    WHERE b.CategoryName = @pMAIN_2507200211126196173
          AND c.CategoryName = @pMAIN_2507200211126196174)
SELECT Category1,
       Sales1,
       Category2,
       Sales2,
       SalesRatio,
       CotangentOfRatio
FROM SalesRatioAnalysis;


            
        

Parameters (If used)

Name Value
@pMAIN_2507200211126196170 Beverages
@pMAIN_2507200211126196171 Condiments
@pMAIN_2507200211126196172 0
@pMAIN_2507200211126196173 Beverages
@pMAIN_2507200211126196174 Condiments

Query Results 1:

  Category1 Sales1 Category2 Sales2 SalesRatio CotangentOfRatio
1 Beverages 286526.9500 Condiments 113694.7500 2.5201423108806695 -1.3964514282411693