Adventureworks query about sales

Question

I would like to find out a few sales-related questions.

- the number of orders and the turnover per shop over the years

- the 10 biggest customers

- the turnover per productcategory and model over the years

Even a hint in the right direction would be very helpful.

Thank you very much!

Answer 1

Hi,

Which one database do you want?

http://msftdbprodsamples.codeplex.com/

---

Pedro Martins, Portugal, https://www.linkedin.com/in/rechousa

Answer 2

10 biggest customers with DENSE_RANK().

USE AdventureWorks2012;

WITH CTE AS (SELECT CustomerID, TotSales=SUM(Subtotal),
                    DR=DENSE_RANK() OVER ( ORDER BY SUM(Subtotal) DESC)
             FROM Sales.SalesOrderHeader 
			 GROUP BY CustomerID)
SELECT * FROM CTE WHERE DR <= 10
ORDER BY DR, CustomerID;
/*
CustomerID	TotSales	DR
29818	877107.1923	1
29715	853849.1795	2
29722	841908.7707	3
30117	816755.5763	4
29614	799277.895	5
29639	787773.0438	6
29701	746317.5293	7
29617	740985.8338	8
29994	730798.7139	9
29646	727272.6493	10
*/

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 3

Dear Kalman Toth, thank you very much for your reply! I am amazed - wouldn't have come up with this one myself...

Answer 4

Dear Reshousa,

thanks for your reply!

I use Adventureworks 2008.

Answer 5

Dear Kalman Toth,

thanks again for your help!

I am still struggling with 'sales per shop'. I thought I found a solution, but it seems to be incorrect (many the same turnover numbers for different shops)

Maybe I am thinking too complicated.

I need Store and SalesOrderDetail - which I can only join via SalesOrderHeader. (Is this correct?)

Eventually I came up with the following:

 

Select s.Name as Shop, SUM(SubTotal) AS SubTotal
from Sales.Store AS s left outer join Sales.SalesOrderHeader as soh on s.SalesPersonID = soh.SalesPersonID 
left outer join Sales.SalesOrderDetail AS sod on soh.SalesOrderID = sod.SalesOrderID
group by s.Name 
order by SUM(LineTotal) desc;

This is rubbish... Could you please give me a hint where to start?

Thank you very much!

 

Answer 6

Short update:

for each Store there seems to be only one salesperson,

select BusinessEntityID, Name, SalesPersonID
from sales.Store
;

but one salesperson has more stores.

select SalesPersonID, Name, BusinessEntityID
from sales.Store
order by SalesPersonID;

So if I link SalesOrderHeader (or -detail) to Store, the sales are attributed to every store related to the SalesPerson.

I see no other way of linking Store to SalesOrderHeader or -Detail than via SalesPerson.

In the widespread (but apparently incorrect) datamodel, there is a field 'BusinessEntityID' in SalesOrderHeader and in SalesOrderDetail, which would make things much easier. I don't find that field in my 2008 Database though.

Please, has someone a hint how to link Orders to Sales.Store?

And is there an image of the datamodel which reflects reality?

Thank you very much!