Will Smyth Will Smyth - 6 months ago 12
SQL Question

Three way join in SQL Server

I have three tables in my database Sales, SalesPeople and Appliances:

Sales

SaleDate EmployeeID AppID Qty
---------- ---------- ----- -----------
2010-01-01 1412 150 1
2010-01-05 3231 110 1
2010-01-03 2920 110 2
2010-01-13 1412 100 1
2010-01-25 1235 150 2
2010-01-22 1235 100 2
2010-01-12 2920 150 3
2010-01-14 3231 100 1
2010-01-15 1235 300 1
2010-01-03 2920 200 2
2010-01-31 2920 310 1
2010-01-05 1412 420 1
2010-01-15 3231 400 2


SalesPeople

EmployeeID EmployeeName CommRate BaseSalary SupervisorID
---------- ------------------------------ ----------- ----------- ------------
1235 Linda Smith 15 1200 1412
1412 Anne Green 12 1800 NULL
2920 Charles Brown 10 1150 1412
3231 Harry Purple 18 1700 1412


Appliances

ID AppType StoreID Cost Price
---- -------------------- ------- ------------- -------------
100 Refrigerator 22 150 250
110 Refrigerator 20 175 300
150 Television 27 225 340
200 Microwave Oven 22 120 180
300 Washer 27 200 325
310 Washer 22 280 400
400 Dryer 20 150 220
420 Dryer 22 240 360


How can I produce a ranked list of salespeople together with their average
commission on sales, excluding sales of refrigerators.

The query should display:

EmployeeName Average Commission
------------------------------ ------------------
Harry Purple 79.1999969482422
Linda Smith 75.375
Charles Brown 59.3333333333333
Anne Green 42

Answer
SELECT sp.EmployeeName,
    COALESCE(AVG(s.Qty * (sp.CommRate / 100.0) * (a.Price - a.Cost)), 0) AS rank
FROM Sales s
LEFT JOIN SalesPeople sp
    ON s.EmployeeID = sp.EmployeeID
INNER JOIN Appliances a
    ON s.AppID = a.ID
WHERE a.AppType <> 'Refrigerator'
GROUP BY sp.EmployeeName
ORDER BY rank DESC