Help needed on finding less than 10% revenue accounts in a table

• subahan_syed

  Right there with Babe

  Points: 721

  More actions

  March 18, 2015 at 11:20 am

  #305776

  Hello Team -

  I'm trying to find out less than 10% in revenue accounts from a table. Below is a snapshot. Basically, I want to add Revenue mix column in the table using procedure.

  ACCOUTSREVENUEREVENUEMIX

  ACCOUNT1 100 2%

  ACCOUNT2 200 4%

  ACCOUNT3 500 9%

  ACCOUNT4 1000 19%

  ACCOUNT5 1500 28%

  ACCOUNT6 2000 38%

  TOTAL 5300 100%

  Kindly help.

  Much appreciated.

  Regards,

  SS

• adaddio

  Mr or Mrs. 500

  Points: 539

  More actions

  March 18, 2015 at 12:52 pm

  #1784884

  --Create a work table

  Create table #Accounts

  (

  Accounts varchar(25)

  ,Revenue INT

  )

  --Populate the table with data:

  Insert into #Accounts

  (

  Accounts

  ,Revenue

  )

  Select

  Accounts

  ,Revenue

  from

  (

  select 'Account1' as Accounts, 100 as Revenue

  union all

  select 'Account2' as Accounts, 200 as Revenue

  union all

  select 'Account3' as Accounts, 500 as Revenue

  union all

  select 'Account4' as Accounts, 1000 as Revenue

  union all

  select 'Account5' as Accounts, 1500 as Revenue

  union all

  select 'Account6' as Accounts, 2000 as Revenue

  )sub

  --Make the select

  select

  Accounts

  ,Revenue

  ,round(cast(Revenue as float) / sum(Revenue) over (partition by null order by (select null)),2) as RevenueMix

  from

  #Accounts

  The above is an example of how you can select the data you want.

  I would recommend against a stored procedure to actually put these values on the table because that would become much more complicated as Account Revenue values change and possibly require triggers to maintain that column.

  You could create a calculated column on the accounts table or you could just create a view that calculated the RevenueMix value only when you need it, which would likely be the preferred solution here...and the easiest.