Solved

Getting a breakdown within the same table

Posted on 2009-05-09
6
175 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now