Dynamically Generated Page, how to? - Programmers Heaven

Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Dynamically Generated Page, how to?

vb89vb89 Posts: 3Member
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

Comments

  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    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.

    -firesickle.com
Sign In or Register to comment.