Hello,

I'm a bit rusty with my Transact-SQL and I've come across an issue with a
Query.

I have the following pair of tables:

Scores               Grants
--------               ---------------
scoreID              grantID
judge_ID            grantTitle
grant_ID
score

Sample Data:

Table Scores
scoreID    judge_ID     grant_ID     score
--------     ----------     ---------      -------
    1              10             20            35
    2              10             21            25
    3              15             20            45
    4              15             21            50

Table Grants
grantID      grantTitle
--------      -----------
    10         Title #1
    20         Title #2

Result Table:

grantID      grantTitle     score1      score2       Sum Score
---------     ------------   ---------      ----------     --------------
    20          Title #2         25              50              75
    21          Title #3         25              15              40

I'm stumped on how to extract the two judges scores and add the fields to
it's corresponding record based on Scores.grant_ID = Grants.grantID.

How would I accomplish this?

Thanks for any assistance with this.
Albert

Albert Frausto wrote:
In SQL 2000:

SELECT s.GrantID, s.GrantTitle,
      Score1 = SUM(CASE WHEN s.Rank = 1 THEN s.score ELSE 0 END,
      Score2 = SUM(CASE WHEN s.Rank = 2 THEN s.score ELSE 0 END,
      SumScore = SUM(s.score)
FROM (
    SELECT  g.GrantID, g.GrantTitle, s.score,
            Rank = (SELECT COUNT(*) + 1
                        FROM Scores s2
                        WHERE s2.GrantID = s.GrantID
                        AND s2.JudgeID = s.JudgeID
                        AND s2.ScoreID < s.ScoreID )
    FROM Grants g
    INNER JOIN Scores s
        ON s.GrantID = g.GrantID
    ) s
GROUP BY s.GrantID, s.GrantTitle

In SQL2005 you can use ROW_NUMBER() OVER() to calculate the Rank
column.

Chris

Correction:

SELECT s.GrantID, s.GrantTitle,
      Score1 = SUM(CASE WHEN s.Rank = 1 THEN s.score ELSE 0 END,
      Score2 = SUM(CASE WHEN s.Rank = 2 THEN s.score ELSE 0 END,
      SumScore = SUM(s.score)
FROM (
    SELECT  g.GrantID, g.GrantTitle, s.score,
            Rank = (SELECT COUNT(*) + 1
                        FROM Scores s2
                        WHERE s2.GrantID = s.GrantID
                        AND s2.JudgeID < s.JudgeID )
    FROM Grants g
    INNER JOIN Scores s
        ON s.GrantID = g.GrantID
    ) s
GROUP BY s.GrantID, s.GrantTitle

Chris,

That worked perfectly. I was clueless how to tackle this problem. Thanks for
your assistance.

Albert

Show quoteHide quote

Stored Procedure commit/rollback
User defined functions and booleans?
BCP .dbf file useing t-sql
tough request
How to combine several long strings and then insert them to a text field