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;
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();
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;