|
Execute the following
SQL Server T-SQL example script in Management Studio Query Editor to create a quintile ranking of the sales staff of AdventureWorks Cycles.
-- SQL quintile - ntile - NTILE(5) - SQL ranking functions - SQL window functions
-- SQL inner join - SQL format money
USE AdventureWorks;
SELECT SalesStaff = c.LastName + ', ' + c.FirstName,
NTILE(5)
OVER(ORDER BY SalesYTD DESC) AS 'Quintile',
YTDSalesAmount = '$' + convert(VARCHAR,s.SalesYTD,1),
a.City,
State = sp.StateProvinceCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
INNER JOIN Person.StateProvince sp
ON sp.StateProvinceID = a.StateProvinceID
WHERE s.TerritoryID IS NOT NULL
AND SalesYTD > 0;
GO
/* Results
SalesStaff Quintile YTDSalesAmount City State
Dyck, Shelley 1 $2,293,796.65 Issaquah WA
Erickson, Gail 1 $2,181,995.61 Renton WA
Dusza, Maciej 1 $1,858,626.93 Issaquah WA
Erickson, Mark 2 $1,658,518.07 Renton WA
Ecoffey, Linda 2 $1,651,313.92 Issaquah WA
Emanuel, Michael 2 $1,491,540.76 Renton WA
Elliott, Carol 3 $1,308,947.33 Issaquah WA
Elson, Jauna 3 $1,293,532.93 Renton WA
Esteves, Janeth 3 $1,015,291.20 Renton WA
Espinoza, Martha 4 $1,008,397.17 Renton WA
Evans, Twanna 4 $864,535.37 Renton WA
Eminhizer, Terry 4 $815,513.35 Renton WA
Elliott, Shannon 5 $788,700.12 Issaquah WA
Eldridge, Carla 5 $683,794.84 Issaquah WA
*/ |
