|
Execute the following
script in Query Editor to obtain the yearly and monthly sales revenue figures for
the AdventureWorks Cycles, a mountain, road & touring bike manufacturer:
USE AdventureWorks;
GO
SELECT
[Year] = YEAR(OrderDate),
SoldQty = SUM(OrderQty),
SalesRevenue = '$'+convert(varchar, convert(money,
SUM(OrderQty * UnitPrice * (1.0-UnitPriceDiscount))
),1)
FROM Sales.SalesOrderHeader AS H
JOIN Sales.SalesOrderDetail AS D
ON D.SalesOrderID = H.SalesOrderID
GROUP BY YEAR(OrderDate)
ORDER BY [Year];
GO
/* Results
Year SoldQty SalesRevenue
2001 11848 $11,331,808.96
2002 60918 $30,674,773.17
2003 124699 $42,011,037.16
2004 77445 $25,827,955.34
*/
-- Monthly revenue calculation
SELECT
[Year] = YEAR(OrderDate),
[Month] = Month(OrderDate),
SoldQty = SUM(OrderQty),
SalesRevenue = '$'+convert(varchar, convert(money,
SUM(OrderQty * UnitPrice * (1.0-UnitPriceDiscount))
),1)
FROM Sales.SalesOrderHeader AS H
JOIN Sales.SalesOrderDetail AS D
ON D.SalesOrderID = H.SalesOrderID
GROUP BY YEAR(OrderDate), Month(OrderDate)
ORDER BY [Year], [Month];
GO
/* Partial results
Year Month SoldQty SalesRevenue
2004 1 9227 $3,009,197.42
2004 2 10999 $4,167,855.43
2004 3 11314 $4,221,323.43
2004 4 12235 $3,819,776.73
2004 5 15656 $5,194,121.52
2004 6 15805 $5,364,840.18
2004 7 2209 $50,840.63
*/
|
