Dynamically Generated Page, how to?

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


  • seancampbellseancampbell Pennsylvania, USA
    Hi there!! Sounds like you are working on a pretty exciting project. To help you more, I was wondering if you could give me the design specs of the Tables you want to hit (all of the existing fields and what data types they are). With that information I can help you go about writing a SQL statement that will grab all of the data you need.

    If what I am assuming is correct, we may not need an extra DB field, but instead get the statement to return the value as a positive or negative integer.

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion