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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
17689 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
17689 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
17689 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
17689 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
17689 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  
 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.18 seconds. Powered By: Snitz Forums 2000