|
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
*/ |