|
Execute the following
Microsoft SQL Server T-SQL script in Query Editor to obtain the yearly and monthly sales revenue figures for
the AdventureWorks Cycles company, a fictional mountain, road & touring bike manufacturer:
-- How to calculate revenue - How to calculate gross margin - SQL calculate revenue
SELECT [Year] = YEAR(OrderDate),
SoldQty = left(convert(VARCHAR,convert(MONEY, SUM(OrderQty)), 1), -3 +
len (convert(VARCHAR,convert(MONEY, SUM(OrderQty)), 1))),
SalesRevenue = '$' + convert(VARCHAR,convert(MONEY,
SUM(OrderQty * UnitPrice * (1.0 - UnitPriceDiscount))), 1),
GrossMargin = '$' + convert(VARCHAR,convert(MONEY,
SUM(OrderQty * (UnitPrice * (1.0 - UnitPriceDiscount))
- StandardCost)), 1)
FROM Sales.SalesOrderHeader AS H
INNER JOIN Sales.SalesOrderDetail AS D
ON D.SalesOrderID = H.SalesOrderID
INNER JOIN Production.Product P
ON P.ProductID = D.ProductID
GROUP BY YEAR(OrderDate)
ORDER BY [Year];
GO
/* Results
Year SoldQty SalesRevenue GrossMargin
2001 11848 $11,331,808.96 $6,352,004.60
2002 60918 $30,674,773.17 $18,241,066.63
2003 124699 $42,011,037.16 $25,499,399.18
2004 77449 $25,828,752.10 $14,419,422.30
*/
------------
USE AdventureWorks;
GO
-- How to calculate sales revenue by quarter
SELECT [Quarter] = CONVERT(VARCHAR,YEAR(OrderDate)) + 'Q' +
CONVERT(VARCHAR,(MONTH(OrderDate) - 1) / 3 + 1),
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 CONVERT(VARCHAR,YEAR(OrderDate)) + 'Q' +
CONVERT(VARCHAR,(MONTH(OrderDate) - 1) / 3 + 1)
ORDER BY [Quarter];
GO
/* Results
Quarter SoldQty SalesRevenue
2001Q3 4833 $4,647,156.85
2001Q4 7015 $6,684,652.11
2002Q1 5184 $5,860,884.49
2002Q2 7064 $6,167,832.56
2002Q3 28146 $10,277,073.05
2002Q4 20524 $8,368,983.08
2003Q1 13095 $6,679,873.81
2003Q2 20416 $8,357,874.88
2003Q3 48955 $13,681,907.05
2003Q4 42233 $13,291,381.43
2004Q1 31540 $11,398,376.28
2004Q2 43700 $14,379,535.19
2004Q3 2209 $50,840.63
*/
------------
-- How to calculate revenue
-- 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
*/
|
