June 15, 2009 at 2:46 pm
I have a dataset of data, and would like to calculate where the 90th percentile falls. Essentially, my dataset looks like this:
0 15
1 5
2 60
3 40
4 20
5 10
If I say my total of the second column is 150, say I want to find which value of my first column (adding all the previous columns) gets me to x percent of the total. So to reach 90 percent the value would be 4.
Sorry if this is confusing....I'm sort of going off the top of my head. I'm thinking some sort of percentile calculation would be great - can SSRS even do that? Is there any way in the custom code to parse through a dataset or something?
Any ideas? Thanks!
June 18, 2009 at 2:25 pm
Can't be done, huh?
June 18, 2009 at 3:29 pm
Hi,
following please find a solution based on Jeff Moden's article[/url]
IMPORTANT NOTE: As per my understanding this code will only work if the order of the columns to be ranked is based on the order of the clustered index!! I strongly recommend to carefully follow this discussion.
CREATE TABLE #t (col1 INT, col2 INT, RunTotal INT)
INSERT INTO #t (col1, col2)
SELECT 0, 15 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 2, 60 UNION ALL
SELECT 3, 40 UNION ALL
SELECT 4 ,20 UNION ALL
SELECT 5, 10
CREATE CLUSTERED INDEX IX_tempt_id --clustered to resolve "Merry-go-Round"
ON #t (col1)
DECLARE @Run INT, --Overall running total
@RunMax INT --Max running total
SET @Run = 0
SET @RunMax = 0
UPDATE #t
SET --===== Running Total
@Run = RunTotal = @Run + col2
FROM #t WITH (INDEX(IX_tempt_id),TABLOCKX)
SELECT @RunMax = MAX(RunTotal) FROM #t
--===== Display the result
SELECT TOP 1 col1
FROM #t
WHERE (RunTotal+0.00)/(@RunMax) > 0.9
ORDER BY RunTotal
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply