locked
Dynamically Generated Page, help with SELECT Statement RRS feed

  • Question

  • User-768405968 posted

    Basically im editijng a dynmically generated page for hockey player stats, and im trying to build a select statement which will allow to count the '+/-' feature that i have added to the preexisiting tables. Basically what i did was add a '+/-' column and what this column will do is calculate the +/- of a player based on the goals the were scored while he was on the ice compared to the goals that were scored against him while they were on the ice. Example, if while player A was on the ice 2 his team scored two goals and the other team scored one goal against his team(this is all while he was on the ice) he will have a +/- rating of +1 (2 goals scored - 1 goal scored against).

     I defined two variables:  Dim tStatsSQL, Dim tTotalSQLPlusMinus

    and I have a table called xTitle = that hold the stats (games played, minutes played, goals scored, assists, etc)

    This is what I have so far for the SELECT statement:

     tTotalSQL = "SELECT "& tStatsSQL
      
     tTotalSQLPlusMinus = " ,(case when p.pm > 0 then '+'||to_char(p.pm) " &_
                               "else to_char(p.pm) end) as col19 "   (col19 will be the +/- column)

      tTotalSQLPlusMinus = 

    Can someone lead me in the right direction as to what should go into 'tTotalSQLPlusMinus' and am i on the right track...thanks for the help!

    Thursday, August 21, 2008 12:26 PM

All replies

  • User-1811063981 posted

    Hi

    How about this:

    tTotalSQL = "SELECT "& tStatsSQL & ", to_char(p.pm, 'S9999') AS col19";

    Regards

    Steve

    Friday, August 22, 2008 12:49 AM
  • User-990694832 posted

    First,I would suggest using system.text.stringbuilder to create your sql.

    Next, Guessing your table structure as normalized

    dim sb as system.text.stringbuilder

    sb.append("Select gs.goalsScored - gsOpponent.goalsScored as Rating ")
    sb.append("from   (select goalsscored from yourtable ")
    sb.append("          where ...) as gs, ")
    sb.append("         (select goalsscored from yourtable ")
    sb.append("          where ...) as gsOpponent ") 

    For a more precise answer please include the table structure.

    Hope this helps

    DK 

     

    Friday, August 22, 2008 8:41 AM
  • User-768405968 posted

    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

    Friday, August 22, 2008 10:12 AM
  • User-1811063981 posted

    Hi

    1. I suppose it's possible, but you already got a pretty big SQL statement, so it would probably more advisable to begin to break it down into smaller parts. Maybe use functions?

    2. I assume the line 'sch.game_code_1032 = " & iCode' restricts the query to one game code. So, you could just remove that. But I'm not 100% sure, because I don't know your table structures.

    3. Not sure. Aren't you already filtering this with 'oc.oncourt <= 48'?

    I'm sorry that I'm not able to help you more, but without seeing the table structures, it's a bit of a guessing game. Also, I don't understand the difference between one game and 48 minutes. Is 48 min the duration of one round?

    Regards

    Steve

    Saturday, August 23, 2008 12:42 AM