?
Solved

Getting a breakdown within the same table

Posted on 2009-05-09
6
Medium Priority
?
180 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 1000 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 1000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 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