August 3, 2007 at 9:01 am
I need to calculate the overall GPA for a student in a particular class.
YEAR SCHOOL STUDENT IDENT GRADETEACHER CLASS GPA
2007 Snow Canyon High Student1 321649 10 Teacher1Earth Systems 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher1Earth Systems 1.6700
2007 Snow Canyon High Student1 321649 10 Teacher1Earth Systems 3.3300
2007 Snow Canyon High Student1 321649 10 Teacher1Earth Systems 3.6700
2007 Snow Canyon High Student1 321649 10 Teacher2Elementary Algebra 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher2Elementary Algebra 0.6700
2007 Snow Canyon High Student1 321649 10 Teacher2Elementary Algebra 1.0000
The problem I'm having is that a student may not taken the class for four terms (as in the Elementary Algebra example above). So I can't hard code it to sum the gpa and divide by 4; it needs to be the number of terms the student took the class.
Here's my sql:
select
trnscrpt.schyear as [Year],
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as suniq,
stugrp_active.graden as Grade,
trnscrpt.teachname as Teacher,
trnscrpt.descript as Class,
gpamarks.gpavallvl0 AS GPA
from
dbo.trnscrpt
inner join dbo.stugrp_active on trnscrpt.suniq = stugrp_active.suniq INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark
where
trnscrpt.graden >= 6 and
trnscrpt.markawd1 not in ('NC','NG','P','W','WA','WF','WI','WP') and
trnscrpt.subjectc in ('LA', 'MA', 'CP', 'CB') and
trnscrpt.schyear = 2007 and
stugrp_active.schoolc = 725
order by
school.schname,
student,
grade,
class
August 3, 2007 at 11:49 am
How big are the tables? How fast does the code have to run? What if you 'counted' the number of quarters that had a grade and then stored it for a divisor?
I see '0' as a 'valid' value for both classes. Are those legitimate quarter grades, or was there a change in how to record grades? It looks to me like there's something funny about how the grades are stored.
Also, how frequently will this calculation be performed? End of school year? After every grading period? I'm intuiting a more satisfactory solution by joining to other tables that aren't listed, in order to be totally accurate in what is provided.
In other words, what's the data structure? I find the sort order for the column titled 'GPA' suspect, as well as the 0.0000 values. Is 0.0000 = failing grade, or not a grade at all?
August 3, 2007 at 12:13 pm
I ended up using the average function and a group by statement.
The data is in a read-only db for the last school.
All the 0.00 values are failing grades since they are associated with F's or incompletes.
Thanks for the reply.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply