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.
Author |
Topic |
greenspacechunks
Starting Member
5 Posts |
Posted - 2007-04-10 : 17:24:34
|
I am having problems with this gradebook database I am developing. I have a temporary table that contains all the category grades for each students (i.e. quiz, tests, and homework). However, I cannot just simply add these grades for the student's total grade to update in the CLASS table. I think I need to do something else besides a "while" loop because my loop is simply going through one time for the first student's class. I am new to SQL, so any help would be greatly appreciated! Thanks!CREATE TABLE TEMP_TABLE(StudentID int NOT NULL, ClassID int NOT NULL, CatID int NOT NULL, Grade decimal(3,2) NOT NULL)INSERT INTO TEMP_TABLE(StudentID, ClassID, CatID, GRADE)SELECT STUDENT.StudentID, CATEGORY.ClassID, CATEGORY.CategoryID, STUDENT_POINTS_CATEGORY.CategoryGrade * CATEGORY.WeightFROM STUDENT INNER JOIN STUDENT_POINTS_CATEGORY ON STUDENT.StudentID = STUDENT_POINTS_CATEGORY.StudentID INNER JOIN CATEGORY ON STUDENT_POINTS_CATEGORY.CategoryID = CATEGORY.CategoryIDDECLARE @student_count intDECLARE @class_count intDECLARE @s intDECLARE @c intDECLARE @grade decimal(3,2)SET @s = 2SET @c = 1SELECT @student_count = COUNT(DISTINCT StudentID)FROM TEMP_TABLESELECT @class_count = COUNT(DISTINCT ClassID)FROM TEMP_TABLEWHILE(@s < @student_count)BEGIN SELECT @grade = SUM(Grade) FROM TEMP_TABLE WHERE StudentID = @s AND ClassID = @c UPDATE STUDENT_CLASS SET STUDENT_CLASS.PercentageGrade = @grade WHERE StudentID = @s AND ClassID = @c IF(@c > @class_count) BEGIN SET @c = 0 SET @s = @s + 1 END SET @c = @c + 1 END |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-10 : 18:59:20
|
You don't need WHILE loop.UPDATE cSET PercentageGrade = TotalGradeFROM STUDENT_CLASS c INNER JOIN ( SELECT ClassID, StudentID, TotalGrade = SUM(Grade) FROM TEMP_TABLE GROUP BY ClassID, StudentID ) t ON c.StudentID = t.StudentID AND c.ClassID = t.ClassID KH |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-10 : 19:02:58
|
Is TEMP_TABLE really a temporary table or permanent table ?Temporary table is created with table name prefix with '#'.Like CREATE TABLE #TEMP_TABLE( StudentID int NOT NULL, ClassID int NOT NULL, CatID int NOT NULL, Grade decimal(3,2) NOT NULL) Looking at it, you don't need temp table at all. You can update STUDENT_CLASS directly from your "INSERT INTO TEMP_TABLE" query KH |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-10 : 19:14:06
|
Here is the combined queryUPDATE cSET PercentageGrade = TotalGradeFROM STUDENT_CLASS c INNER JOIN ( SELECT ClassID, StudentID, TotalGrade = SUM(Grade) FROM ( SELECT STUDENT.StudentID, CATEGORY.ClassID, CATEGORY.CategoryID, STUDENT_POINTS_CATEGORY.CategoryGrade * CATEGORY.Weight as Grade FROM STUDENT INNER JOIN STUDENT_POINTS_CATEGORY ON STUDENT.StudentID = STUDENT_POINTS_CATEGORY.StudentID INNER JOIN CATEGORY ON STUDENT_POINTS_CATEGORY.CategoryID = CATEGORY.CategoryID ) t GROUP BY ClassID, StudentID ) t ON c.StudentID = t.StudentID AND c.ClassID = t.ClassID KH |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-10 : 19:21:42
|
to further simplify itUPDATE cSET PercentageGrade = TotalGradeFROM STUDENT_CLASS c INNER JOIN ( SELECT STUDENT.StudentID, CATEGORY.ClassID, CATEGORY.CategoryID, SUM(STUDENT_POINTS_CATEGORY.CategoryGrade * CATEGORY.Weight) AS TotalGrade FROM STUDENT INNER JOIN STUDENT_POINTS_CATEGORY ON STUDENT.StudentID = STUDENT_POINTS_CATEGORY.StudentID INNER JOIN CATEGORY ON STUDENT_POINTS_CATEGORY.CategoryID = CATEGORY.CategoryID GROUP BY STUDENT.StudentID, CATEGORY.ClassID ) t ON c.StudentID = t.StudentID AND c.ClassID = t.ClassID KH |
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 2007-04-10 : 20:11:56
|
Nice Code!!!In SQL Server 2000, instead of DERIVED TABLE, you can use a CTE!Kalman Toth, Database, DW & BI ArchitectSQL Server 2005 Training - http://www.sqlusa.com |
|
|
greenspacechunks
Starting Member
5 Posts |
Posted - 2007-04-10 : 20:42:53
|
Actually, getting away from the computer made me realize that I don't even need an updated column. I should be using a view. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-10 : 21:08:47
|
quote: Originally posted by SQLUSA Nice Code!!!In SQL Server 2000, instead of DERIVED TABLE, you can use a CTE!Kalman Toth, Database, DW & BI ArchitectSQL Server 2005 Training - http://www.sqlusa.com
You mean 2005 ? KH |
|
|
|
|
|
|
|