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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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