Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Unanswered: Multi-calculation query - is there a way to do this?

    I have a table with every NBA game from 2007 season.

    Table has following fields:

    GameID
    GameDate
    HomeTeam
    AwayTeam
    HomeTeamScore
    AwayTeamScore
    HomeTeamRatingPriorToTheCurrentGame
    AwayTeamRatingPriorToTheCurrentGame

    Now, we come to the problem, I want to calculate HomeTeam and AwayTeam's ratings for each game based on their performance up to the current GameDate!

    Every team starts a season with 1000 points and gets points added and deducted based on their results and based on to whom they win or lose.

    For example if a team with 1500 rating beats 500 rated team it gets less points that when a 1500 rated team beats 1200 rated team.

    So, I would need to design the query that can do this (explained in example in plain language):

    On 15th November 2006 Seattle (at home, playing 9th game in the season) plays Philadelphia (visitor, playing 7th game in the season).

    I want to determine Seattle's and Philadelphia's ratings prior to that game.

    I need to pull last Seattle's game (be it away or at home, in this case on 13th of November vs. NJ), retrieve its rating and add or subtract points based on their performance vs. New Jersey.

    Then repeat the same for away club Philadelphia!

    And then repeat that for every game in a season.

    I have no idea how to nest that query, especially given the fact that each new row should take into account calculated ratings of rows above it!

    Anybody can help?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    wow. just wow.

    I very much doubt you can do this with a set based solution.
    You will probably have to use a recordset and iterate through the calculations.

    I think the rules here are too complex at the moment. Perhaps you should break it down into very minor steps.

    Ugh, I know, another cop out (count 2 tonight) but that's one of the most evil questions I've heard!
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well I'd guess they key to that is not to store the rating for a series of games in a table.

    if you identify the date the game was played (and you have) you should design an algolryhtm which calculates the rating based on a date interval.

    If you have to store the rating, for performance or other reasons then logically I'd expect to store the rating for this game (and preceeding games), not rating for previous games in this game.

    once you have decided on your algolrhtym convert that into a query
    eg
    select (<mycomplexratingmathmaticalfunction>) as Rating from <mytable> where gameplayedon between (#startdate# and #enddate#) where teamid=<blah>

    you then need to associate that with the parent SQL I forget what the sub query syntax is.. truth be told, I only recently found that Access supported sub queries thanks to this forum and I've never really used them enough to sink in.

    I still don't think its right to store this as its derived data. However I think you could argue that you needed to know the teams ratings going into a game for each game they've played, if you need that available for each and every game (ie seattle were n% after game1, o% after game2, z% after game13. It sounds all "too American" sports to me.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    I very much doubt you can do this with a set based solution.
    You will probably have to use a recordset and iterate through the calculations.
    wow. just wow.

    please go stand in the corner for an hour
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry Sir.



    *stands in the corner awaiting your solution*
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2007
    Posts
    108
    wow. just wow.

    please go stand in the corner for an hour
    If it's possible r937 can you offer a solution to this problem?


    OR

    To rephrase the question:

    Can you reference a calculated field in another row (other then the current row) within the same query?

    For example, imagine simple table with 3 fields:

    IDPrimaryKey
    NumberOne
    NumberTwo

    You then design a query like this

    SELECT IDPrimaryKey, NumberOne, NumberTwo, SumOfTwoNumbers AS [NumberOne+NumberTwo] FROM bla.................

    And now you want to add another field to that query for example SumOfTwoNumbers(calculated row)_From_Row_That_Has_IDPrimaryKey_OF_CurrentRow' s_IDPrimaryKey_Minus_5.

    That would solve the above mentioned problem!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Riorin
    If it's possible r937 can you offer a solution to this problem?
    sorry, not at this time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2007
    Posts
    108
    sorry, not at this time
    1. No time, will do it later?
    2. I don't know the solution!
    3. I know, but won't share now or ever!

    Sorry I don't understand the basis for your reply, pick an answer, all are fine!

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Riorin
    pick an answer, all are fine!
    the answer is 1 and 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •