How do I add this column and feature to an exisiting page

[vb89]

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. Thanks in advance for taking the time to help!!

 

[jmeckley]

a picture (or in this case code) is worth 1000 words.
what you describe is perfectly possible. Depending on how your page is configured will determine how easy or difficult it is to render.

when I code for a webform, or other IIS request I keep the code behind to a minimum. it only deals with presentation. everything else is pushed to the core domain.

so adding information like scoringDelta would be handled in the domain. all I have to do to the webpage is add a column and display the property.

if your using any type of datasource, or processing your logic in sql then this task becomes much more difficult.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[vb89]

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!

 

[jmeckley]

so all players for a given team will have the same rating (+/-) correct? otherwise I don't see how you doing the math.

your database should not be doing this
[tt]tTotalSQLPlusMinus = " ,(case when p.pm > 0 then '+'||to_char(p.pm) " &_
"else to_char(p.pm) end) as col19 " (col19 will be the +/- column)
[/tt]
databases are good at aggregating/relating data. They are not good for complex business logic and string formatting. save this type of logic for presentation.

in your business logic/domain all you may need to know is the numerical ranking, presentation means nothing to code. the +/- is eye candy for human to quickly interpret the data, so this only needs to be present at the GUI.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[vb89]

No each player will likely have a different +/- rating, the +/- rating applies to only players on the ice, so if player A wasn't on the ice when his team score a goal his +/- = 0 if he was on the ice when his team scored then he would have a +1.

Basically I need the SELECT statement to calculate the players +/- for the season based on the season he's played. What do you recommend I put in the SELECT statement to compile the players +/-? I also have tables with the players game by game log, and things of that nature, i imagine this will be nescessary to compute the players +/- for the duration of the entire season...thanks in advance for the help

 

[jmeckley]

the calculations for ratings are complex. you need to know:
1. the game
2. each period of time the current player was on the ice.
3. how many goals where scored by his team for each of
those periods.
4. how many goals where scored by the opposing team during each of those periods.

I would only use the database to load the players, teams and other objects. I would not do any calculations in the database.

I would start by loading the game.
then loading the teams that played the game.
then loading the players who played in the game.
then aggregate some statistics about the game. something like [tt]select team, goal, time_occurred from [a table] where game = @game[/tt]

using this data I would compute the statistics using a series of services.

interface IStatistic<T>
{
   T ComputeFor(IPlayer player);
}
or
interface IStatistic<T>
{
   T ComputeFor(IGame game);
}

with a different implementation for each statistic. (IPlayer and IGame are objects you define in code)

I would also have a mapping object which would map these complex calculations into simple dtos (data transfer objects). primarily for the GUI.

interface IPlayerToStatisicsGridMapper
{
   PlayerStatsPerGameDto MapFrom(IPlayer player);
}

at the GUI level it would simply be

MyGrid.DataSource = GetData();
MyGrid.DataBind();

with this markup

<asp:GridView ...>
  <Columns>
      <Column for player name/>
      <Column for team name/>
      <template column>
          <%#Eval("sign")%><%#Eval("rating")%>
      </template column>
  </Columns>
</asp:GridView>

where sign and rating are readonly properies on the dto.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[vb89]

Hey Jason I have another question for you, how do I calculate the players +/- based on them playing 48minutes of the game, here is my code for calc +/- based on a normal full game log:

if iSeason >= iSeasonMinPlusMinus then
tFromPlusMinus = " , (SELECT oc.player_id, oc.team_id, " &_
" sum(CASE WHEN oc.team_id = pbp.off_team_id and pbp.score_before <> pbp.score_after THEN pbp.points ELSE 0 END) - " &_
" sum(CASE WHEN 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 "
else
tFromPlusMinus = ""
end if

 

[jmeckley]

you can't because you need to know when the 48 minutes occurred, not just that 48 minutes occured. as for the query. just pull the records into memory and process them. don't do any type of formatting in the db. it's not meant for that.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[vb89]

And i would do this how exactly, some coding would help, please

 

[jmeckley]

I can't provide the code, because I don't know how your system is designed. There are many pieces to this puzzle each piece solving 1 segment of the overall problem.

I can provide concepts on how to solve the problem as a concern of the domain. These are the pieces I posted above.

If your using datasources and binding directly to grids. then my methodology won't work because there is no domain. there is only gui and database. you would need to start over.

if you have business objects (POCO or DataSets) then there is the ability to refactor. how your objects are related will determine how much refactoring needs to be done.

also, are you debugging manually <F5>, or are you using unit testing? tools shouldn't effect design, but design will effect testability. if you're using a testing framework then you can test each piece atomically. if your not using a framework, then you need to test all the pieces at once. This make it more complex to find bugs because you have to step through all the code.

If you want to do this at the database level, I would post a question in the respective db forum. They are experts with this kind of sql. Then it's simply a matter of loading the DataReader into a DataTable and binding the table to the grid.

Jason Meckley
Programmer
Specialty Bakers, Inc.