SQL2012 rank into predefine number

• mario17

  SSCertifiable

  Points: 6078

  More actions

  July 13, 2015 at 3:54 pm

  #398737

  Hi, I have data like below, I'm trying to assign/rank my agents into let say 4 rank categories, with sql below I have ranks 1-12 for each of them, what I would like to have group in first Quartiles (0-25%), Group2 (26-50%) and so on. So I will have Rank 1-4.

  I'm pretty sure MS have something like this , it's very handy for stats.

  I can go surely thru series of subq, to get this. Tx much

  WITH tt AS (

  SELECT 'DON, GELDON'AGENT_NAME,10030AGENT_ID,0.46PERC UNION

  SELECT 'IVAN, MO'AGENT_NAME,33330AGENT_ID,0.83PERC UNION

  SELECT 'JOHN, DOW'AGENT_NAME,12222AGENT_ID,0.47PERC UNION

  SELECT 'LINDA, CHEN'AGENT_NAME,20011AGENT_ID,0.36PERC UNION

  SELECT 'MICHELLE, BENTLEY'AGENT_NAME,23400AGENT_ID,0.52PERC UNION

  SELECT 'MIKE, BLACK'AGENT_NAME,22222AGENT_ID,0.69PERC UNION

  SELECT 'NATALY, BROWN'AGENT_NAME,23498AGENT_ID,0.58PERC UNION

  SELECT 'PAUL, MCCOY'AGENT_NAME,10077AGENT_ID,0.98PERC UNION

  SELECT 'PETE, SAMPRAS'AGENT_NAME,23000AGENT_ID,0.49PERC UNION

  SELECT 'RITA, DAVIS'AGENT_NAME,10088AGENT_ID,0.45PERC UNION

  SELECT 'STELLA, ARTOUS'AGENT_NAME,32000AGENT_ID,0.61PERC UNION

  SELECT 'WILLIAM, CROW'AGENT_NAME,25430AGENT_ID,0.77PERC

  FROM ClarHSD.dbo.AidCOde )

  SELECT

  agent_id, agent_name, perc,

  RANK() OVER ( ORDER BY perc) RN

  FROM tt

• Jacob Wilkins

  One Orange Chip

  Points: 27972

  More actions

  July 13, 2015 at 4:09 pm

  #1812168

  Indeed. NTILE() sounds like what you're looking for. See https://msdn.microsoft.com/en-us/library/ms175126.aspx for the documentation.

  Then your code becomes something like this:

  WITH tt AS (

  SELECT 'DON, GELDON'AGENT_NAME,10030AGENT_ID,0.46PERC UNION

  SELECT 'IVAN, MO'AGENT_NAME,33330AGENT_ID,0.83PERC UNION

  SELECT 'JOHN, DOW'AGENT_NAME,12222AGENT_ID,0.47PERC UNION

  SELECT 'LINDA, CHEN'AGENT_NAME,20011AGENT_ID,0.36PERC UNION

  SELECT 'MICHELLE, BENTLEY'AGENT_NAME,23400AGENT_ID,0.52PERC UNION

  SELECT 'MIKE, BLACK'AGENT_NAME,22222AGENT_ID,0.69PERC UNION

  SELECT 'NATALY, BROWN'AGENT_NAME,23498AGENT_ID,0.58PERC UNION

  SELECT 'PAUL, MCCOY'AGENT_NAME,10077AGENT_ID,0.98PERC UNION

  SELECT 'PETE, SAMPRAS'AGENT_NAME,23000AGENT_ID,0.49PERC UNION

  SELECT 'RITA, DAVIS'AGENT_NAME,10088AGENT_ID,0.45PERC UNION

  SELECT 'STELLA, ARTOUS'AGENT_NAME,32000AGENT_ID,0.61PERC UNION

  SELECT 'WILLIAM, CROW'AGENT_NAME,25430AGENT_ID,0.77PERC

  )-- I eliminated the FROM clause since it was unnecessary and caused an error if we didn't have that schema and table in our database

  SELECT

  agent_id, agent_name, perc,

  NTILE(4) OVER ( ORDER BY perc desc) RN

  FROM tt

  Cheers!

• mario17

  SSCertifiable

  Points: 6078

  More actions

  July 13, 2015 at 5:00 pm

  #1812178

  NTILE(4) OVER ( ORDER BY perc desc) RN

  Bingo !!! thanks much Jacob !!!

  M

• Jacob Wilkins

  One Orange Chip

  Points: 27972

  More actions

  July 13, 2015 at 5:21 pm

  #1812180

  No problem. I'm glad I could help!