Re: Expression for weighted average, working with joined tables??
Tony,
Now that I know that, this code should create the results you're looking for.
I renamed calcColumn to 'Team Handicap', and moved it to just after 'Team_ID'.
Based on the data I've supplied so far, the results are shown in this image.
Capture.PNG
Tony,
Now that I know that, this code should create the results you're looking for.
I renamed calcColumn to 'Team Handicap', and moved it to just after 'Team_ID'.
Based on the data I've supplied so far, the results are shown in this image.
Capture.PNG
Code:
SELECT t.Tourn_ID, tm.Team_ID, ( cast( case -- when tm.player1_handicap < tm.player2_handicap then (tm.player1_handicap * 2) / 3 -- when tm.player2_handicap < tm.player1_handicap then tm.player2_handicap * 2 / 3 -- else -- tm.player1_handicap * 2 / 3 when tm.player1_handicap < tm.player2_handicap then ((tm.player1_handicap * 2)+tm.player2_handicap)/3 when tm.player2_handicap < tm.player1_handicap then ((tm.player2_handicap * 2)+tm.player1_handicap)/3 else ((tm.player1_handicap * 2)+tm.player2_handicap)/3 end as decimal(4,2)) ) as 'Team Handicap', tm.Player1_ID,cast(tm.player1_handicap as int) as 'Player1 Handicap',g1.first_name, g1.last_name, tm.Player2_id,cast(tm.player2_handicap as int) as 'Player2 Handicap', g2.first_name, g2.last_name From Tournaments t inner join teams tm on tm.tourn_ID = t.tourn_ID inner join golfers g1 on g1.golfer_ID = tm.player1_ID inner join golfers g2 on g2.golfer_ID = tm.player2_ID where tm.player1_handicap is not null
Comment