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 prepare a sales report by staff?

Execute the following Microsoft SQL Server T-SQL script in Query Editor to create a sales report by sales staff. The report includes 2 ranking columns.

use AdventureWorks2008;

 

select  p.LastName +', '+p.FirstName as SalesPerson,

  count(distinct soh.SalesOrderID) as NoOfOrders,

  '$'+convert(varchar,convert(money,sum(sod.LineTotal)),1) as TotalSales,

   row_number() over ( order by sum(sod.LineTotal) desc ) as SalesOrderRank,

   row_number() over ( order by count(distinct soh.SalesOrderID) desc ) as NoOfOrdersRank

from Sales.SalesOrderHeader soh

  inner join Sales.SalesOrderDetail sod

  on soh.SalesOrderID=sod.SalesOrderID

  inner join Person.Person p

  on soh.SalesPersonID =p.BusinessEntityID

group by p.BusinessEntityID , p.LastName +', '+p.FirstName

order by sum(sod.LineTotal) desc
/*
SalesPerson       NoOfOrders  TotalSales      Rank    NoOfOrdersRank
Mitchell, Linda   418         $10,367,007.43    1     4
Carson, Jillian   473         $10,065,803.54    2     1
Blythe, Michael   450         $9,293,903.00     3     2
Pak, Jae          348         $8,503,338.65     4     5
Reiter, Tsvi      429         $7,171,012.75     5     3
Ito, Shu          242         $6,427,005.55     6     7
Saraiva, José     271         $5,926,418.36     7     6
Varkey, Ranjit    175         $4,509,888.93     8     10
Campbell, David   189         $3,729,945.35     9     9
Vargas, Garrett   234         $3,609,447.21     10    8
Ansmane, Pamela   95          $3,325,102.59     11    14
Mensa-Annan, Tete 140         $2,312,545.69     12    11
Valdez, Rachel    130         $1,827,066.71     13    12
Tsoflias, Lynn    109         $1,421,810.92     14    13
Jiang, Stephen    48          $1,092,123.86     15    15
Alberts, Amy      39          $732,759.18 16    16
Abbas, Syed       16          $172,524.45 17    17

*/

 

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