Self Join Table, MS SQL 2005

bumblebee

Hi

I have stucked in this query for quite some time.
My table as follows

StudentID Result ResultRating LastUpdate
------------------------------------------------------------------------
StudentA 60 Average 6 June 2008
StudentA 80 Good 20 June 2008
StudentB 10 Bad 6 June 2008
StudentB 50 Average 20 June 2008
StudentB 100 Good 08 July 2008
StudentC 100 Good 08 July 2008
StudentB 50 Average 10 July 2008
------------------------------------------------------------------------

How can I get the total number of student attaining "Good", "Average", "Bad" in each month, assuming we will only take the highest rating for each student. For example, June StudentA, will be a "Good" Student.

The result will be as follows
Number of Students ResultRating Month
------------------------------------------------------------------------
1 Good June
1 Average June
0 Bad June
2 Good July
0 Average July
0 Bad July
------------------------------------------------------------------------

Hope someone could help .. Thank you in advanced.

Oct 22 '08 #1

ck9663

Sounds like an assignment.

Post what you have so far.

-- CK

Oct 22 '08 #2

bumblebee

I have came out with this sql

SELECT MAX(Result) AS MaxResult, MONTH(LastUpdated) AS LastUpdatedMonth
FROM dbo.StudentResultHistory
GROUP BY StudentID, MONTH(LastUpdated)

It did return the maximum score for each individual student for each month but it doesn't count the number of student in "Good" grades in each month.

Oct 23 '08 #3

bumblebee

Hey I have solved it using 2 views, 1 stored procedure. Anyway, Thanks for your reply. ^^

Oct 23 '08 #4