July 15, 2008 at 10:32 pm
I have a math problem, but I can't seem to think of an approach to do it programmatically either in sql or vb.net. So I'll discuss the sql side.
I have a table called SCORES. In this table I have the following columns: sID, Assignment, Category, Score.
sID=student's id
Assignment=name of assignment
Category=type of assignment (Classwork, homework, test...)
Score=score student received on assignment
In the app, Categories are weighted. Classwork is 30% of total grade, Homework is 15%, Quiz 15%, and tests 40%.
The normal way to calculate final grade is:
(Average Classwork total * 30%) + (Average HW total *15%) + (Av. Quiz * 15%) + (Av. Tests * 40%) = Final Grade.
However, I need to do a score normalization. Score normalization takes into account the fact that there is no scores for a particular category. For example, if there were not any test scores recorded yet and I needed to calculate the final grade, the mathematical formula for score normalization is:
[(Av Category1 total * weight value1) + (Av Category2 total * weight value2)...] / (weight value1 + weight value2...)
For example, if a student has only two classwork grades and one homework grade, his score will be normalized as follows:
Classwork grades: 90, 90
Homework grades: 80
Classwork average: (90+90)/2 = 90
Homework average: 80/1=80
Final Grade= [(90 * .30) + (80 * .15)] / (.30 + .15)
Final Grade= 87%
I'm a little stuck on how to approach a normalization problem, particularly programming it to only divide by the total weighted values. If anyone has ever done these types of calculations I'd appreciate any help. I tried to write the calculation myself, but I don't know how to deal with null values when a certain category doesn't have any scores, which in the end affects how many weighted values are added and used to divide by.
Hope anyone can help.
Thanks a lot.
July 16, 2008 at 7:07 am
I'm sorry, but I don't have the time to provide a answer/sample.
I can give you a hint though: probably you can use the COUNT to see how many scores are available. The COUNT function counts all values in a column except NULL values.
July 16, 2008 at 7:38 am
Hi,
is your problem sorted yet?
I agree with the previous writer, but alternatively:
in your vb code, retrieve the resultset with your data;
in the transformation of this data, that is, where you apply the logic, follow the following idea:
loop through your column values, and if a column value is null, you do nothing, but if it has a value, you increment an integer value, lets say "count".
then, when you finished looping, you will have your count to divide by.:P
July 16, 2008 at 7:49 am
My wife is a teacher. Try this
Select sID,
ClassWork, Homework, Quiz, Test,
cast(round(((Cwt + Hwt + Qwt + TWt) / Denominator), 0) as int) as Average
from
(Select sID,
Classwork, Homework, Quiz, Test,
isnull(Classwork, 0) * .3 as CWt,
isnull(Homework, 0) * .15 as HWt,
isnull(Quiz, 0) * .15 as QWt,
isnull(Test, 0) * .4 as TWt,
case when classwork is Null then 0 else .3 end +
case when Homework is Null then 0 else .15 end +
case when Quiz is Null then 0 else .15 end +
case when Test is Null then 0 else .4 end as Denominator
from
( Select sID, Category, Score
from Scores
) Scr
Pivot
( Avg(Score)
FOR Category IN
( [Classwork], [Homework], [Quiz], [Test] )
) AS pvt
) as calc
July 16, 2008 at 8:09 am
Break the calculations up.
Something like this might do it:
;with
Portions ([Type], Weight) as
/*
Replace this CTE with a table in the real app.
This is for proof of concept only.
These numbers should not be hard coded in a proc, should be table-driven.
*/
--Classwork is 30% of total grade, Homework is 15%, Quiz 15%, and tests 40%
(select 'Classwork', cast(.3) as float
union all
select 'Homework', .15
union all
select 'Quiz', .15
union all
select 'Tests', .4),
TypesAvail ([Type]) -- Types for given student
(select distinct category
from dbo.Scores
where [SID] = @SID_in), -- Assumed input parameter
TotalAvail (Tot) as
(select sum(weight)
from portions
inner join typesavail
on portions.type = typesavail.type)
Weights ([Type], Weight) as
(select [type], Weight/Tot
from TypesAvail
inner join Portions
on Portions.Type = TypesAvail.Type
cross join TotalAvail),
StudentScores (Category, TotScore) as
(select category, cast(sum(score) as float)
from dbo.Scores
where [SID] = @SID_in) -- Same assumed input parameter
select Category, TotScore * Weight
from Weights
inner join StudentScores
on [Type] = Category
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 16, 2008 at 9:14 am
In my submission using SQL pivot, you could replace the first occurrence of denominator with
case when Denominator = 0 then 1 else Denominator end
to handle cases where you have set up all the assignements but have not entered a single grade of any kind for a given student.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply