Multiple Table Calculation

Question

Multiple Table Calculation

gtan018

SSChasing Mays

Points: 624
More actions
May 6, 2008 at 12:28 pm

#185099

I have three tables that I wish to perform a simple query calculation. What would be the best option for my situation? I would need to calculation:
(ScoreAdjTotal) - (CourseRating) * 113 / (CourseSlope) = ?
Example: 91 - 70.5 * 113 / 118 = 19.6
Table1
PlayerID
PlayerFName
Table2
CourseID
CourseName
CourseRating
CourseSlope
Table3
ScoreID
ScoreAdjTotal

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

gtan018 (5/6/2008)
I have three tables that I wish to perform a simple query calculation. What would be the best option for my situation? I would need to calculation:
(ScoreAdjTotal) - (CourseRating) * 113 / (CourseSlope) = ?
Example: 91 - 70.5 * 113 / 118 = 19.6
Table1
PlayerID
PlayerFName
Table2
CourseID
CourseName
CourseRating
CourseSlope
Table3
ScoreID
ScoreAdjTotal

Looks to me like some important info is missing. How are the three tables related?

😎

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 2

Agree with Lynn, the referential integrity is needed to provide the solution, without it you would end up

with a messy cartesian product. Now once you have set up the integrity you can do something

like this

SELECT

(a.ScoreAdjTotal - b.CourseRating) * 113/ b.CourseSlope) AS 'Calculation'

FROM

Table3 a

INNER JOIN Table2 b ON (This is where your referential integrity comes in)

Hope that points you in the right direction

Marvin Dillard
Senior Consultant
Claraview Inc

gtan018 SSChasing Mays Points: 624 More actions · Answer 3

dbo.Event

EventID (PK) / int

EventDate / smalldatetime

CourseID (FK) / int

dbo.Course

CourseID (PK) / int

CourseName / varchar(50)

CourseRating / decimal(11, 1)

CourseSlope / int

dbo.Player

PlayerID(PK) / int

PlayerFName / varchar(50)

dbo.Score

ScoreID(PK) / int

EventID(FK) / int

Player(FK) / int

ScoreAdjTotal / tinyint

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I put in my own referential integrity. You will need to adjust my code below for the fourth table you added. Also, mathematically, the result of your equation is wrong. More on that later. Here is my code:

--(ScoreAdjTotal) - (CourseRating) * 113 / (CourseSlope) = ?

--

--Example: 91 - 70.5 * 113 / 118 = 19.6

--

--Table1

--PlayerID

--PlayerFName

--

--Table2

--CourseID

--CourseName

--CourseRating

--CourseSlope

--

--Table3

--ScoreID

--ScoreAdjTotal

create table dbo.Player (

PlayerID int,

PlayerFName varchar(25)

)

create table dbo.Course (

CourseID int,

CourseName varchar(25),

CourseRating decimal(5,2),

CourseSlope int)

create table Score (

ScoreID int,

PlayerID int,

CourseID int,

ScoreAdjTotal int)

insert into dbo.Player

select 1, 'Hank' union all

select 2, 'Tom'

insert into dbo.Course

select 1, 'Founders', 70.5, 118 union all

select 2, 'Seaward', 81.3, 102

insert into dbo.Score

select 1,1,1,91 union all

select 2,1,1,82 union all

select 3,1,2,78 union all

select 4,1,2,76 union all

select 5,2,1,74 union all

select 6,2,2,81

--(ScoreAdjTotal) - (CourseRating) * 113 / (CourseSlope) = ?

--

--Example: 91 - 70.5 * 113 / 118 = 19.6

select

*,

cast(round((s.ScoreAdjTotal - c.CourseRating) * 113 / c.CourseSlope,1) as decimal(5,1)) as Score

from

dbo.Player p

inner join dbo.Score s

on (p.PlayerID = s.PlayerID)

inner join dbo.Course c

on (s.CourseID = c.CourseID)

drop table dbo.Player

drop table dbo.Course

drop table dbo.Score

As for your equation, as written in your post, you would multiple CourseRating by 113 first, then you'd divide that result by the CourseSlope, and then finally you'd subtract that from the ScoreAdjTotal. Here is how you should have written the equation:

(((ScoreAdjTotal - CourseRating) * 113) / CourseSlope)

The parens help ensure that the calculations are done in the correct order. My first run using your sample data resulted in a different value than that provided, obviously due to what I stated above.

😎

gtan018 SSChasing Mays Points: 624 More actions · Answer 5

Lynn,

Great job. I integrated your codes as advised and made a few changes with my tables. The formula works great.

How can I Delete all numbers after the tenth digit (truncate).

Do not round to the nearest tenth: 19.1

Than you again for your help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Do the following:

round((s.ScoreAdjTotal - c.CourseRating) * 113 / c.CourseSlope,1,1) -- The additional 1 at the end will truncate instead of round (read BOL)

😎

gtan018 SSChasing Mays Points: 624 More actions · Answer 7

Lynn,

Everything works perfect. It does not round off the number as you stated. Thank you so much for your help. 😀