# Customer Ranking

 0 I have 10,000 customers and have been trying (for some time) to calculate a ranking based on how long each customer has been with the business. Calculating the top 10% is easy but does anyone have any idea how to calculate those customers that are between 11% - 20%, 21% -30% and so on? StuPS … I need to be able to do this ‘on the fly’ more ▼ asked Aug 03, 2012 at 08:28 PM in Default Stuart_Sydney 10 ● 1 ● 1 ● 2 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 ``````SELECT * FROM (SELECT CustomerName, FirstOrderDate, NTILE(10) OVER (ORDER BY FirstOrderDate, CustomerID) [Range] FROM [dbo].[Customers])d WHERE [Range] = 2 -- for 11-20% range `````` more ▼ answered Aug 04, 2012 at 08:38 PM Scot Hauder 6.1k ● 13 ● 15 ● 18 THanks Scot ... will give it a try and get back with the results Aug 06, 2012 at 04:40 AM Stuart_Sydney Scot ... Getting there, just working out how to set the parameters for each range. THanks for pointing in the right direction.Stu Aug 07, 2012 at 08:14 PM Stuart_Sydney add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 If your group percentages are set try this: ``````SELECT CustomerName, CASE WHEN [Range] = 1 THEN 'Group1' -- 1-5% WHEN [Range] = 2 THEN 'Group2' -- 6-10% WHEN [Range] BETWEEN 3 AND 4 THEN 'Group3' --11-20% WHEN [Range] BETWEEN 5 AND 10 THEN 'Group4' --21-50% ELSE 'Group5' END FROM (SELECT CustomerName, FirstOrderDate, NTILE(20) OVER (ORDER BY FirstOrderDate, CustomerID) [Range] FROM [dbo].[Customers])d `````` more ▼ answered Aug 21, 2012 at 04:12 AM Scot Hauder 6.1k ● 13 ● 15 ● 18 Scot NTILE will not work in this case as NTILE divides the rowset into equal groups. I need to divide the rowset into 5 unequal groups for example: group 1 = top 5%, group 2 = 6 to 10%, group 3 = 11 to 20%, group 4 = 21 to 50%, group 5 = whatever is left (its an 80/20 Pareto principle thing where the top 20% yield 80% of revenue) The only way forward I can see is to build a custom function Any thoughts? Thanks Stu Aug 21, 2012 at 02:22 AM Stuart_Sydney @Stuart_sidney - Scot solution here does that by identifying the common denominator for the groups you want you then partition the records using the CASE test on the value that NTile in the inner SELECT has provided. You could make this cover any range by using NTILE(100) and then accounting for every value of [range] in the CASE. Aug 21, 2012 at 09:36 AM Fatherjack ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 Scot / Fatherjack, Thanks for the samples ... in the end I took somewhat of a shortcut. I created a query on the customer file using RANK. Then wrote a module to allow the user to set whatever levels they want and run a table update. Thanks for the help. more ▼ answered Aug 27, 2012 at 01:35 AM Stuart_Sydney 10 ● 1 ● 1 ● 2 That sounds like a risky solution. If you persist the levels into the table, it will be invalid/incorrect as soon as new customers arrive. Aug 27, 2012 at 07:27 AM Magnus Ahlkvist add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x11

asked: Aug 03, 2012 at 08:28 PM

Seen: 717 times

Last Updated: Aug 27, 2012 at 07:27 AM