SQLUSA

Microsoft SQL Server 2008 Best Practices

How to prepare a sales report by staff?

 

Execute the following 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

SQLUSA - The Best SQL Server 2008 Training in the World
 
 
SQLUSA.com Home Page