467,134 Members | 961 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,134 developers. It's quick & easy.

Self Join Table, MS SQL 2005

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
  • viewed: 2447
Share:
3 Replies
ck9663
Expert 2GB
Sounds like an assignment.

Post what you have so far.

-- CK
Oct 22 '08 #2
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
Hey I have solved it using 2 views, 1 stored procedure. Anyway, Thanks for your reply. ^^
Oct 23 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Denis St-Michel | last post: by
2 posts views Thread by Tech | last post: by
4 posts views Thread by Shahzad | last post: by
6 posts views Thread by davegb | last post: by
3 posts views Thread by sks | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.