SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

greenspacechunks
Starting Member

5 Posts

Posted - 04/10/2007 :  17:24:34  Show Profile  Reply with Quote
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.Weight
FROM         STUDENT INNER JOIN
                      STUDENT_POINTS_CATEGORY ON STUDENT.StudentID = STUDENT_POINTS_CATEGORY.StudentID INNER JOIN
                      CATEGORY ON STUDENT_POINTS_CATEGORY.CategoryID = CATEGORY.CategoryID

DECLARE @student_count int
DECLARE @class_count int
DECLARE @s int
DECLARE @c int
DECLARE @grade decimal(3,2)

SET @s = 2
SET @c = 1

SELECT @student_count = COUNT(DISTINCT StudentID)
FROM TEMP_TABLE
SELECT @class_count = COUNT(DISTINCT ClassID)
FROM TEMP_TABLE

WHILE(@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)

Singapore
17634 Posts

Posted - 04/10/2007 :  18:59:20  Show Profile  Reply with Quote
You don't need WHILE loop.

UPDATE c
SET    PercentageGrade = TotalGrade
FROM   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


Edited by - khtan on 04/10/2007 19:17:05
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17634 Posts

Posted - 04/10/2007 :  19:02:58  Show Profile  Reply with Quote
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

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17634 Posts

Posted - 04/10/2007 :  19:14:06  Show Profile  Reply with Quote
Here is the combined query

UPDATE	c
SET     PercentageGrade = TotalGrade
FROM	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


Edited by - khtan on 04/10/2007 19:17:21
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17634 Posts

Posted - 04/10/2007 :  19:21:42  Show Profile  Reply with Quote
to further simplify it

UPDATE	c
SET     PercentageGrade = TotalGrade
FROM	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

Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/10/2007 :  20:11:56  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
Nice Code!!!

In SQL Server 2000, instead of DERIVED TABLE, you can use a CTE!

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

greenspacechunks
Starting Member

5 Posts

Posted - 04/10/2007 :  20:42:53  Show Profile  Reply with Quote
Actually, getting away from the computer made me realize that I don't even need an updated column. I should be using a view.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17634 Posts

Posted - 04/10/2007 :  21:08:47  Show Profile  Reply with Quote
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 Architect
SQL Server 2005 Training - http://www.sqlusa.com



You mean 2005 ?


KH

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000