I am looking for help writing a calculated expression (if possible) to calculate the weighted average using multiple joined tables, concerning a golf tournament.
Each tournament will have many teams. Each team will consist of 2 (players) golfers. Each golfer has a handicap. I would like an expression that would look at each team, find each of the 2 golfer?s individual handicap, determine the lower of the 2 handicaps, multiply the lower handicap by 2 and then divide the result by 3.
Tournaments Table
Tourn_ID: 0002 Active: Yes
Teams Table
Team_ID: 0025 Tourn_ID: 0002 Team_Handicap: CALCULATED
Players Table
Player_ID: 0076 Team_ID: 0025 Golfer_ID: 0197
Player_ID: 1005 Team_ID: 0025 Golfer_ID: 1967
Golfers Table
Golfer_ID: 0197 First_Name: John Last_Name: Smith Handicap: 5
Golfer_ID: 1967 First_Name: Bill Last_Name: Jones Handicap: 9
Tournament - Teams (one to many) Tourn_ID
Teams ? Players (one to many) Team_ID
Players ? Golfers (one to one) Golfer_ID
The following Select statement returns the correct records and data needed:
SELECT Tournaments.Tourn_ID, Tournaments.Active, Teams.Team_ID, Teams.Tourn_ID AS Tourn_ID1, Teams.Team_Handicap, Players.Player_ID, Players.Team_ID AS Team_ID1, Players.Golfer_ID, golfers.golfer_id AS golfer_id1, golfers.first_name, golfers.last_name, golfers.Handicap
FROM Tournaments Tournaments
INNER JOIN (Teams Teams
INNER JOIN (Players Players
INNER JOIN golfers golfers
ON Players.Golfer_ID = golfers.golfer_id )
ON Teams.Team_ID = Players.Team_ID )
ON Tournaments.Tourn_ID = Teams.Tourn_ID
WHERE Tournaments.Active = 'Yes'
Microsoft SQL Server Management Server Studio 2014
Alpha 4770 5018
Thanks
Tony
Each tournament will have many teams. Each team will consist of 2 (players) golfers. Each golfer has a handicap. I would like an expression that would look at each team, find each of the 2 golfer?s individual handicap, determine the lower of the 2 handicaps, multiply the lower handicap by 2 and then divide the result by 3.
Tournaments Table
Tourn_ID: 0002 Active: Yes
Teams Table
Team_ID: 0025 Tourn_ID: 0002 Team_Handicap: CALCULATED
Players Table
Player_ID: 0076 Team_ID: 0025 Golfer_ID: 0197
Player_ID: 1005 Team_ID: 0025 Golfer_ID: 1967
Golfers Table
Golfer_ID: 0197 First_Name: John Last_Name: Smith Handicap: 5
Golfer_ID: 1967 First_Name: Bill Last_Name: Jones Handicap: 9
Tournament - Teams (one to many) Tourn_ID
Teams ? Players (one to many) Team_ID
Players ? Golfers (one to one) Golfer_ID
The following Select statement returns the correct records and data needed:
SELECT Tournaments.Tourn_ID, Tournaments.Active, Teams.Team_ID, Teams.Tourn_ID AS Tourn_ID1, Teams.Team_Handicap, Players.Player_ID, Players.Team_ID AS Team_ID1, Players.Golfer_ID, golfers.golfer_id AS golfer_id1, golfers.first_name, golfers.last_name, golfers.Handicap
FROM Tournaments Tournaments
INNER JOIN (Teams Teams
INNER JOIN (Players Players
INNER JOIN golfers golfers
ON Players.Golfer_ID = golfers.golfer_id )
ON Teams.Team_ID = Players.Team_ID )
ON Tournaments.Tourn_ID = Teams.Tourn_ID
WHERE Tournaments.Active = 'Yes'
Microsoft SQL Server Management Server Studio 2014
Alpha 4770 5018
Thanks
Tony
Comment