Calculation percentile - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculation percentile

Author

Topic

kvkumar
Starting Member

2 Posts

Posted - 2009-09-10 : 23:33:19

Write an SSIS package to calculate the ranking of a student within a current school and grade based on DSS Score.

Ranking will be done on SCHOOL (column) from STUDENT table and GRADE (column) from MATH table and READING table.

STUDENT table contains student_id,school columns.

MATH table contains student_id,grade,dss score columns.

READING table contains student_id,grade,dss score columns.

There are 442 schools, 3,4,5,6,7,8,9,10 grades and 200000 students in all the schools. So, for the students of each school i need to calculate the ranking for 3-10 grade students.

Example: for suppose 6211 school have 3rd grade students, 4th grade students untill 10th grade students. I need to calculate ranking for all grade students of 6211. Then I need to calculate ranking for all grade students of 6212 same way. Then 6213, 6214, 6215,..........

I should create SSIS package. Anything like batch processing is usefull over here.

I need to calculate ranking for MATH and READING for all the students, then load into PERCENTILE table.

PERCENTILE table is having 3 columns. Student_ID, MATH_RANKING, READING_RANKING

So, i need 3 columns Student_ID, MATH_RANKING, READING_RANKING as output. So,that i can load that output to PERCENTILE table using SSIS package.
Requirement

Calculation for Percentile

1. Ranking will be done on current school from the STUDENT_DEMOGRAPHIC and FCAT_GRADE from the FCAT_MATH_CORRECTED or FCAT_READ_CORRECTED
2. Select school and FCAT grade level
3. Eliminate any students with a DSS (SSS develop) score of blank or zero.
4. Sort by DSS score (lowest to highest)
5. Take the row (record position and subtract 0.5.
6. Take the new value and divide by the number of rows (total students) for the school and FCAT grade level.
7. Multiple by 100.
8. Round off to a whole integer.
9. A second pass through the table will be necessary for students with the same scores. Starting from the top of the table check the first student to the next student. If they have the same DSS score move the percentile ranking of the first student to the second. Continue the checking, moving to the second student and compare to the third student. Continue until you have reached the end of the table.

…… ……

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-11 : 00:22:03

make use of row_number(), rank() or dense_rank() function to perform the ranking

KH
[spoiler]Time is always against us[/spoiler]

kvkumar
Starting Member

2 Posts

Posted - 2009-09-11 : 09:14:52

I wrote a query like this but its not working perfectly.

I need to group by grade, that is the reason its not working. but here in this case both the tables MATH and READING have the GRADE column with the same name GRADE. That is the reason i am not able to do group by GRADE.

ANY SUGGESTION please

;with StudentScores as
(
select s.SchoolID
,s.StudentID
,MathScore=coalesce(m.DSSScore,0)
,ReadScore=coalesce(r.DSSScore,0)
from Students s
left join Math m on s.StudentID=m.StudentID
left join Reading r on s.StudentID=r.StudentID
group by s.SchoolID
)

,MathRanks as
(
select SchoolID
,StudentID
,MathScore
,MathRank=rank() over (Partition by SchoolID order by MathScore)
,NumMathRows=count(*) over (Partition by SchoolID)
from StudentScores
where MathScore<>0
)
,ReadRanks as
(
select SchoolID
,StudentID
,ReadScore
,ReadRank=rank() over (Partition by SchoolID order by ReadScore)
,NumReadRows=count(*) over ()
from StudentScores
where ReadScore<>0
)

,Percentiles as
(
select SchoolID
,StudentID
,ScoreType=cast('MathScore' as varchar(20))
,Score=MathScore
,PercentileType=cast('MathPercentile' as varchar(20))
,Percentile=convert(int,round((MathRank-0.5)/NumMathRows*100,0))
from MathRanks
union all
select SchoolID
,StudentID
,ScoreType=cast('ReadingScore' as varchar(20))
,Score=ReadScore
,PercentileType=cast('ReadingPercentile' as varchar(20))
,Percentile=convert(int,round((ReadRank-0.5)/NumReadRows*100,0))
from ReadRanks
)

select SchoolID
,StudentID
,MathScore=sum(MathScore)
,MathPercentile=sum(MathPercentile)
,ReadingScore=sum(ReadingScore)
,ReadingPercentile=sum(ReadingPercentile)
from Percentiles
pivot (min(Score) for ScoreType in (MathScore,ReadingScore)) Pvt1
pivot (min(Percentile) for PercentileType in (MathPercentile,ReadingPercentile)) Pvt2
group by SchoolID,StudentID

quote:
Originally posted by khtan

make use of row_number(), rank() or dense_rank() function to perform the ranking

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-11 : 11:05:13

table structure, sample data and expected result would be nice

pls refer to http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources