Customer Rankings - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Customer Rankings

Author

Topic

STOTZEQ
Starting Member

2 Posts

Posted - 2013-08-30 : 12:46:48

Need a little help on a project. We need to rank our customers based on revenue.

Here are my requirements:

Largest volume customers making up the first 40% of revenue are “A”, next 30% are “B”, next 20% are “C”, remaining 10% are “D”.

Here is what I have so far:

declare @ARankPercent int
declare @BRankPercent int
declare @CRankPercent int
declare @DRankPercent int

SET @ARankPercent = 40
SET @BRankPercent = 30
SET @CRankPercent = 20
SET @DRankPercent = 10

select
CU_NME as CustomerName
, Sum(CF_TOT) as Total
, TotalRank = CASE
when ntile(100) over (order by Sum(CF_TOT) DESC) <= @ARankPercent then
'A'
when ntile(100) over (order by Sum(CF_TOT) DESC) BETWEEN (@ARankPercent+1) AND @BRankPercent then
'B'
when ntile(100) over (order by Sum(CF_TOT) DESC) BETWEEN (@BRankPercent+1) AND @CRankPercent then
'C'
when ntile(100) over (order by Sum(CF_TOT) DESC) BETWEEN (@CRankPercent + 1) AND @DRankPercent then
'D'
else
'Z'
end
from
pfw_CMFIS
join pfw_CMASTR on pfw_CMASTR.CU_CUS = pfw_CMFIS.CF_CUS
WHERE CF_YR =2013 and CF_TOT > 0 and CU_BR = '11'
GROUP BY CU_NME
ORDER BY Total desc

..but the above is giving me invalid results and here's why:

Let's say we did a total of $10,000,000 in revenue. First 40% would be $4,000,000. The top two customers make up $4,000,000, so only they should be ranked as 'A'. But the above is giving me everyone in the 40 percentile.

Any thoughts?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-30 : 13:37:13

You should calculate a running total like shown below and then base the ranking on the Fraction. If the Fraction is < 1-@ARankPercent then it is category A etc.


select
	cu_name as CustomerName,
	SUM(sum(CF_TOT)) over (order by SUM(CF_TOT) desc) as RunningTotal,
	100.0*sum(cf_tot)/sum(sum(CF_TOT)) over() as Fraction
from
	YourTablesAndJoinsHere
group by
	cu_name

STOTZEQ
Starting Member

2 Posts

Posted - 2013-09-03 : 14:12:40

Thanks James -- the "SUM(sum(CF_TOT)) over (order by SUM(CF_TOT) desc) as RunningTotal," was exactly what I was looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources