Search

Loading...

Friday, February 24, 2012

How to generate Quartile ranking

Execute the following SQL Server T-SQL example script in Management Studio Query Editor to create a Quartile ranking of the sales staff of AdventureWorks Cycles.


-- SQL Quartile - ntile - NTILE(4) - SQL ranking functions
-- SQL inner join - SQL format money
USE AdventureWorks2008; 

SELECT SalesStaff = p.LastName + ', ' + p.FirstName, 
       NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile', 
       YTDSalesAmount = '$' + convert(VARCHAR,s.SalesYTD,1), 
       a.City, 
       State = sp.StateProvinceCode 
FROM   Sales.SalesPerson s 
       INNER JOIN Person.Person p 
         ON s.BusinessEntityID = p.BusinessEntityID 
       INNER JOIN Person.Address a 
         ON a.AddressID = p.BusinessEntityID 
       INNER JOIN Person.StateProvince sp 
         ON sp.StateProvinceID = a.StateProvinceID 
WHERE  s.TerritoryID IS NOT NULL 
       AND SalesYTD > 0; 
GO 


/*
SalesStaff     Quartile YTDSalesAmount    City       State
Mitchell, Linda   1     $5,200,475.23     Issaquah    WA 
Pak, Jae          1     $5,015,682.38     Renton      WA 
Blythe, Michael   1     $4,557,045.05     Issaquah    WA 
Carson, Jillian   1     $3,857,163.63     Issaquah    WA 
Varkey, Ranjit    2     $3,827,950.24     Renton      WA 
Campbell, David   2     $3,587,378.43     Renton      WA 
Saraiva, José     2     $3,189,356.25     Renton      WA 
Ito, Shu          3     $3,018,725.49     Renton      WA 
Reiter, Tsvi      3     $2,811,012.72     Issaquah    WA 
Valdez, Rachel    3     $2,241,204.04     Renton      WA 
Mensa-Annan, Tete 4     $1,931,620.18     Renton      WA 
Vargas, Garrett   4     $1,764,938.99     Issaquah    WA 
Tsoflias, Lynn    4     $1,758,385.93     Renton      WA
*/


Ref: http://www.sqlusa.com/

No comments:

Post a Comment