Hello.
I wrote this SQL thinking I was a candidate for the 5 bucks.
However, after reading further, I'm not a qualified Google
researcher. Since I did the work, I thought I'd post
it anyway, so you get to keep your money. : )
If you have any questions, you can email me at danwX@Xcox.net
remove the Xs
-- this has been formatted flush left because of
-- the formatting issues with the message board.
-- use this to drop the table
drop table #sample
-- create a table to hold data in lieu of
-- actual data table
create table #sample
(score int
,number_of_scores decimal(18,2))
-- load sample data from last example on
-- http://cyberlearn.fau.edu/dwalker/practice_percentile_ranks.htm
insert #sample (score, number_of_scores) select 33, 10
insert #sample (score, number_of_scores) select 32, 5
insert #sample (score, number_of_scores) select 30, 6
insert #sample (score, number_of_scores) select 27, 2
insert #sample (score, number_of_scores) select 26, 4
insert #sample (score, number_of_scores) select 25, 5
insert #sample (score, number_of_scores) select 24, 9
insert #sample (score, number_of_scores) select 22, 2
insert #sample (score, number_of_scores) select 20, 3
-- Acronyms SB, SAT, and N reference
-- http://cyberlearn.fau.edu/dwalker/practice_percentile_ranks.htm
-- 1. create a table to hold the number of students below score
-- SB = the number of students below a particular score
create table #students_below
(score int
,SB decimal(18,2))
insert #students_below
(score
,SB)
select
t1.score
,isnull(sum(t2.number_of_scores), 0) as SB
from #sample t1
left join #sample t2
on t1.score > t2.score
group by t1.score
-- 2. create a table to hold the value of half of the students at that score
-- .5(SAT) = half of the students at the particular score
-- use CAST or result will be integer and not give you .5
create table #half_count
(score int
,SAT decimal(18,2))
insert #half_count
select
t1.score
,CAST(t1.number_of_scores as decimal (5,2))/2 as SAT
from #sample t1
-- 3. create table to hold total number of students
-- N = the number of students
-- use variable to hold value
create table #total_students
(N int)
insert #total_students
(n)
select sum(number_of_scores)
from #sample
-- return values
select
t1.score
,SB
,SAT
,N
,(SB + SAT) / N as percentile_rank
from
#students_below t1
inner join #half_count t2
on t1.score = t2.score
cross join #total_students t3
drop table #sample
drop table #students_below
drop table #half_count
drop table #total_students |
