Solved

Getting a breakdown within the same table

Posted on 2009-05-09
6
177 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
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 26

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 26

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

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.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

830 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