asked on

Getting a breakdown within the same table

I'm having some difficulty trying to get what I think is a simple query to work correctly.
The idea is that I need to get a difference in scoring from other scorers in the same table.
I will need to add a parameter to get the scorer who I want to be the primary scorer etc.
I've attached what I need from what the table looks like.

thanks in advance with your help

Scorer-excel.jpg
score-table.jpg
score-layout.jpg

Aneesh

SELECT A.*, b.ScorerID, b.Score, SpecificScore-Score as Difference
FROM (
select StudentID,scorerID SpecificScore, Score
FROM Score
WHERE scorerID = @SpecificScorer
)A
INNER JOIn (
select StudentID,scorerID Score
FROM Score
WHERE scorerID = @SecondaryScorerID ) B
ON A.StudentID = B.StudentID

Wayne Burr

ASKER

That got me a closer to what I need. Closer then what I had.
One thing is that I do not and would not know is who the SecondaryScorer is. I just need to get everybody who scored the same DistrictStudentID as the one who I specify.
So, specificScorer is supplied and I need all other Scorers who scored the same DistrictStudentID and I get the difference score.

thanks.

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Luttrell

That is on the right track but I think the query can be more concise without the subselects, like below.

SELECT A.DistrictStudentId, A.ScorerID, A.Score, B.ScorerID, B.Score, B.Score-A.Score as Difference 
FROM Score A 
INNER JOIN Score B ON A.DistrictStudentId= B.DistrictStudentIdAND A.ScorerID <> B.ScorerID
WHERE A.ScorerID= @SpecificScorer

Open in new window

SOLUTION