Solved

Getting a breakdown within the same table

Posted on 2009-05-09
6
179 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:wayneburr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24345225

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
0
 

Author Comment

by:wayneburr
ID: 24345427
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.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 24345577
i think this will do
 

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 <> @SpecificScorer ) B 
ON A.StudentID = B.StudentID

Open in new window

0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24346035
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

0
 
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 250 total points
ID: 24346042
A space got lost in that query and would have caused an error, so I reposted
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.DistrictStudentId AND A.ScorerID <> B.ScorerID
WHERE A.ScorerID = @SpecificScorer 

Open in new window

0
 

Author Closing Comment

by:wayneburr
ID: 31579819
You guys are good.  Thanks a ton!  
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question