SQL Table linking - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Table linking

Author

Topic

brenkenathan
Starting Member

3 Posts

Posted - 2010-05-12 : 01:11:39

Ok I am newer to SQL and starting very basic with creating a few queries.

Here is what I have so far

SELECT CAST(AVG(SoldierData.SitUpCount)AS DECIMAL(0)) AS SitUpAverage, CAST(AVG(SoldierData.PushUpCount)AS DECIMAL(0))AsPushUpAverage, CAST(AVG(SoldierData.RunTime) AS TIME) AS RunTimeAverage

The column SoldierData.SitUpCount is the raw score for the Sit Up Event once this tally is entered I have a second table that I compare the repetitions and have a column that has the actual score out of 100.

What I want to see is the SoldierData.SitUpCount column to not even show up but the Score table to show up the respective score that is the next column over from the repetition.

I think this makes sense, at least in my head :) Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 01:19:59

Please give us table structure, sample data and wanted output.
That would make it much easier for us to understand your needs and giving some help.

No, you're never too old to Yak'n'Roll if you're too young to die.

brenkenathan
Starting Member

3 Posts

Posted - 2010-05-12 : 01:36:25

SoldierData(LastName, FirstName, SSN, PushUpCount, SitUpCount, RunTime)
SitUpScoreChart(Repetitions, Score)
PushUpScoreChart(Repetitions, Score)
RunTimeScoreChart(Time, Score)

LastName, FirstName, SSN are pretty self explanatory
PushUpCount, SitUpCount, RunTime are Raw numbers input by the user
Repetitions is a raw score again just like the Counts above
Score is an score ranking integer from 1 to 100 as based out of a manual the Army uses for grading. an example would be (77, 100) where 77 push ups = 100 points score.

Final Goal is to be able to Not show the Raw Data at all but to only show the Average of the Score not the Raw Data. So my thinking is to convert the Raw Scores to an average then to some how hide them but yet show the converted score.

I am using the following to get the Average, but how would I hide the SoldierData.SitUpCount and only show the Score field?

SELECT CAST(AVG(SoldierData.SitUpCount)AS DECIMAL(0)) AS SitUpAverage, CAST(AVG(SoldierData.PushUpCount)AS DECIMAL(0))AsPushUpAverage, CAST(AVG(SoldierData.RunTime) AS TIME) AS RunTimeAverage

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 02:02:59

Should be something like this:


select
sd.LastName,
sd.FirstName,
sd.SSN,
su.Score as SitUpScore,
pu.Score as PushUpScore,
rt.Score as RunTimeScore
from 
(
 select
 LastName,
 FirstName,
 SSN,
 CAST(AVG(SoldierData.SitUpCount)AS DECIMAL(0)) AS SitUpAverage,
 CAST(AVG(SoldierData.PushUpCount)AS DECIMAL(0))As PushUpAverage, 
 CAST(AVG(SoldierData.RunTime) AS TIME) AS RunTimeAverage 
 from SoldierData
 group by LastName, FirstName, SSN
) as sd

join SitUpScoreChat su
on su.Repetitions = sd.SitUpAverage 

join PushUpScoreChart pu
on pu.Repetitions = sd.PushUpAverage

join RunTimeScoreChart rt
on rt.[Time] = sd.RunTimeAverage

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources