DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to calculate yearly sales revenue of mountain bikes?

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

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE