Basically what I have is a Dynamically generated page with preexisting data which displays hockey stats for players for the current season. What I want to do is add a column to a preexisiting table whick basically shows a '+/-' of a current total. This page currently displays hockey game stats, things like: minutes played, goals scored, assists, etc. I want to create a column called "+/-" which basically displays a + or - column based the amount of goals the team scored while he was on the ice, compared to how many were scored against them. For instance: If while a player was on the ice his team scored 2 goals, and 1 was scored against him, he would have a rating of +1 (the 2 goals that were scored while he was on the ice - 1 that was scored against them while he was on the ice). Can someone please lead me in the right direction as to how to go about this...hpw would i go about building the select statement, and then where in the coding would i insert it, and things of this nature.
Ok so i got started and basically what i want to do is have the player's +/- be calculated on their season total, per 48 min total. I have three diff headers named xTitle A, xTitle B, xTitle C that will display all three of the diff. results above(season total, per-game, and per 48).
And i have the following code so far:
tStatsSQL = tStatsSQL & " , points AS A_col120, "
tSelectDataPlusMinus = " ,(case when p.pm > 0 then '+'||to_char(p.pm) " &_
"else to_char(p.pm) end) as A_col19 "
tFromPlusMinus = " , (SELECT oc.player_id, oc.team_id, " &_
" sum(CASE WHEN oc.oncourt and oc.team_id = pbp.off_team_id and pbp.score_before <> pbp.score_after THEN pbp.points ELSE 0 END) - " &_
" sum(CASE WHEN oc.oncourt <= 48 and oc.team_id = pbp.def_team_id and pbp.score_before <> pbp.score_after THEN pbp.points ELSE 0 END) PM " &_
"FROM customer_data.cd_bk_schedule sch, " &_
" customer_data.cd_bk_pbp_oncourt oc, " &_
" customer_data.cd_bk_pbp pbp " &_
" WHERE sch.game_code = oc.game_code and " &_
" sch.game_code = pbp.game_code and " &_
" sch.game_code_1032 = " & iCode & " and " &_
" oc.oncourt = pbp.oncourt " &_
"GROUP BY oc.player_id, oc.team_id) p "
1. Do I have to rewrite the tStatsSQL statement three time since I want three diff results, so basically rewrite the statement changing col, to B_col, and for the third table to C_col, etc?
2. The statement above calculates +/- for one game, how would i have it calculate +/- based on the duration of an entire season?
3. How would I get the above statement to only calc +/- based on the first 48 minutes of the game only?
Thanks for all the help
