Using top x percent

[schusser]

I have a stored procedure where I'm trying to gather top 10 percent, top 20 percent, top 30 percent, etc. of activity in one year to compare with same grouping in another year. I'm using this to populate charts that have previously been populated manually.

How can I make sure that my top 10 percent companies are those bringing in only up to the top 10.0 percent of our revenue, not 10.1 percent as is happening now?

Thanks in advance for your help!

 

[jbenson001]

We can't help with out seeing some code and sample data and desired results.

 

[schusser]

Sure here goes...based on 542 companies
Company As % of running grand total running %
542 revenue total of total
1 .2 883,243 15,898,502 5.56
2 .4 1,377,281 15,898,502 8.66
3 .6 1,852,106 15,898,502 11.65
.
.
53 9.8 9,066,010 15,898,502 57.02
54 10.0 9,140,432 15,898,502 57.49
55 10.1 9,214,303 15,898,502 57.96
So I want to get the company 54 data which represents the top 10 percent of the companies bringing in 57.49% of the revenue.

 

[RiverGuy]

Here's the only thing I could come up with to do what you want to do. There could be an easier way to get that 10 pct to truncate to an integer--I don't know.

--Generate 542 rows
DECLARE @t1 TABLE
(T1ID INT)

DECLARE @i INT
SELECT @i = 1
WHILE @i < 543
BEGIN
  INSERT INTO @T1 SELECT @i
  SELECT @i = @i + 1
END

--Get the 10 pct. value
DECLARE @TenPct INTEGER
SELECT @TenPct = (SELECT COUNT(*) FROM @t1)/10

SELECT TOP (@TenPct) * FROM @t1