WITH MonthlyCategorySales AS (
SELECT
c.CategoryName,
DATEPART(month, o.OrderDate) AS SaleMonth,
SUM(od.Quantity * od.UnitPrice) AS MonthlySales
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 o.OrderDate BETWEEN '1996-01-01' AND '1996-12-31' -- Limit to one year for simplicity
GROUP BY c.CategoryName, DATEPART(month, o.OrderDate)
),
CategoryTotalSales AS (
SELECT
CategoryName,
SUM(MonthlySales) AS TotalSales
FROM MonthlyCategorySales
GROUP BY CategoryName
),
NormalizedSales AS (
SELECT
mcs.CategoryName,
mcs.SaleMonth,
-- Simplified normalization: (MonthlySales / TotalSales) * 2 -1. This isn't robust, but good for example
CAST(mcs.MonthlySales AS FLOAT) / cts.TotalSales AS NormalizedSales
FROM MonthlyCategorySales mcs
JOIN CategoryTotalSales cts ON mcs.CategoryName = cts.CategoryName
),
SalesTrendAngle AS (
SELECT
CategoryName,
-- Calculate ASIN of the normalized sales.
ASIN(AVG(NormalizedSales)) AS TrendAngleInRadians,
DEGREES(ASIN(AVG(NormalizedSales))) AS TrendAngleInDegrees
FROM NormalizedSales
GROUP BY CategoryName
)
SELECT
CategoryName,
TrendAngleInRadians,
TrendAngleInDegrees
FROM SalesTrendAngle
ORDER BY TrendAngleInDegrees;
var (sql1, parameters1) = new SqlQueryBuilder()
.WithCTETable(new Table("MonthlyCategorySales"), new SqlQueryBuilder()
.Select()
.Column("c.CategoryName", "CategoryName")
.Column(new DATEPART(SqlDateInterval.month, new Column("o.OrderDate")), "SaleMonth")
.Column(new SUM(new ColumnArithmatic().Column("od.Quantity").MULTIPLY().Column("od.UnitPrice"))
, "MonthlySales")
.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 BETWEEN(new Column("o.OrderDate"), "1996-01-01", "1996-12-31")))
.GroupBy(new GroupBy(new Column("c.CategoryName"), new DATEPART(SqlDateInterval.month, new Column("o.OrderDate"))))
)
.WithCTETable(new Table("CategoryTotalSales"), new SqlQueryBuilder()
.Select()
.Column("CategoryName", "CategoryName")
.Column(new SUM(new Column("MonthlySales")), "TotalSales")
.From("MonthlyCategorySales")
.GroupBy(new GroupBy(new Column("CategoryName")))
)
.WithCTETable(new Table("NormalizedSales"), new SqlQueryBuilder()
.Select()
.Columns("mcs.CategoryName", "mcs.SaleMonth")
.Column(new ColumnArithmatic().SqlFunction(new CAST(new Column("mcs.MonthlySales"), SqlDataType.FLOAT)).DIVIDE()
.Column("cts.TotalSales"), "NormalizedSales")
.From("MonthlyCategorySales", "mcs")
.Join(new List<IJoin>()
{
new INNERJOIN ().TableName(new Table("CategoryTotalSales","cts"))
.On(new Column("mcs.CategoryName").Equale(new Column("cts.CategoryName")))
})
)
.WithCTETable(new Table("SalesTrendAngle"), new SqlQueryBuilder()
.Select()
.Column("CategoryName", "CategoryName")
.Column(new ASIN(new AVG(new Column("NormalizedSales"))), "TrendAngleInRadians")
.Column(new DEGREES(new ASIN(new AVG(new Column("NormalizedSales")))), "TrendAngleInDegrees")
.From("NormalizedSales")
.GroupBy(new GroupBy("CategoryName"))
)
.Select()
.Columns("CategoryName", "TrendAngleInRadians", "TrendAngleInDegrees")
.From("SalesTrendAngle")
.Build();
WITH MonthlyCategorySales
AS (SELECT c.CategoryName AS CategoryName,
DATEPART(month, o.OrderDate) AS SaleMonth,
SUM(od.Quantity * od.UnitPrice) AS MonthlySales
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 o.OrderDate BETWEEN @pMAIN_2507192103021763230 AND @pMAIN_2507192103021763231
GROUP BY c.CategoryName, DATEPART(month, o.OrderDate)),
CategoryTotalSales
AS (SELECT CategoryName AS CategoryName,
SUM(MonthlySales) AS TotalSales
FROM MonthlyCategorySales
GROUP BY CategoryName),
NormalizedSales
AS (SELECT mcs.CategoryName,
mcs.SaleMonth,
CAST (mcs.MonthlySales AS FLOAT) / cts.TotalSales AS NormalizedSales
FROM MonthlyCategorySales AS mcs
INNER JOIN
CategoryTotalSales AS cts
ON mcs.CategoryName = cts.CategoryName),
SalesTrendAngle
AS (SELECT CategoryName AS CategoryName,
ASIN(AVG(NormalizedSales)) AS TrendAngleInRadians,
DEGREES(ASIN(AVG(NormalizedSales))) AS TrendAngleInDegrees
FROM NormalizedSales
GROUP BY CategoryName)
SELECT CategoryName,
TrendAngleInRadians,
TrendAngleInDegrees
FROM SalesTrendAngle;