Baseball Standings Calculating GB

  • I am tying to set up standings for a fantasy baseball site and I dont know what I am doing to query for Games Back.

    TA is the Teams Abbreviation

    W is wins

    L is losses

    WP is winning percentage

    GB is figured off of the team in first or with the highest WP and that is where I get lost. Here is my query as of now but I dont know how to loop it for the GB.

    The formula for Games back is:

    (first place wins - team wins)+(team losses-first place losses) and then divide by 2.

    SQL Query

    SELECT TEAMS.TA, TEAMS.W, TEAMS.L, Round(TEAMS.W/(TEAMS.W+TEAMS.L),3) AS WP

    FROM TEAMS

    ORDER BY Round(TEAMS.W/(TEAMS.W+TEAMS.L),3) DESC;

  • As Games Behind is within Division within League, here is a solution. Note that Winning Percentage is defined as a computed column.

    Create table TeamRecord

    (Leaguevarchar(255)not null

    ,Dvarchar(255) not null

    ,TAvarchar(255) not null

    ,Wtinyintnot null

    ,Ltinyintnot null

    ,GBdecimal(4,1)

    ,WPas ( cast ( W as decimal(6,3) ) / cast ( w + L as decimal(6,3) ) )

    )

    insert into TeamRecord (League, D , TA,W,L, GB)

    select 'American','Central','White Sox' , 47,35, 0 union all

    select 'American','Central', 'Twins' , 45, 38 , 2.5 union all

    select 'American','Central', 'Tigers' , 42, 40, 5 union all

    select 'American','Central', 'Royals' , 38 , 45 , 9.5 union all

    select 'American','Central', 'Indians', 37, 46, 10.5

    A division leader has the highest winning percentage, so here is the SQL:

    select League, D, MAX(WP) as WP

    from TeamRecord

    group by League, D

    To get all of the columns for the division leader:

    select *

    FROM(select League, D, MAX(WP) as WP

    from TeamRecord

    group by League, D

    ) as LDWP

    on LDWP.League= TeamRecord.League

    and LDWP.D= TeamRecord.D

    joinTeamRecord as LDLeader

    on LDLeader.League= LDWP.League

    and LDLeader.D= LDWP.D

    and LDLeader.WP= LDWP.WP

    Now the final SQL:

    select( ( LDLeader.W - TeamRecord.W) + ( TeamRecord.L - LDLeader.L ) ) / 2.0

    ,*

    fromTeamRecord

    join(select League, D, MAX(WP) as WP

    from TeamRecord

    group by League, D

    ) as LDWP

    on LDWP.League= TeamRecord.League

    and LDWP.D= TeamRecord.D

    joinTeamRecord as LDLeader

    on LDLeader.League= LDWP.League

    and LDLeader.D= LDWP.D

    and LDLeader.WP= LDWP.WP

    GO SOX !!!

    SQL = Scarcely Qualifies as a Language

  • That is awsome thank you for your time. I have one follow up question how can I get it to format the results in typical standings table format

    WP = .xxx

    GB = xx.x

    Bill

  • how can I get it to format the results in typical standings table format

    WP = .xxx

    GB = xx.x

    For Winning Percentage, which is not a percentage but is a rate but needs to be rounded to 3 decimal points.

    Games Behind has more decimal places than desired, so the results are cast to have one decimal point.

    See http://chicagosports.sportsdirectinc.com/baseball/mlb-standings.aspx?page=/data/mlb/standings/2008/league/standings.html

    alter table TeamRecord drop column WP

    alter table TeamRecord

    ADD WP as ( CAST ((CAST ( ( 100 * W / ( W + L ) ) as DECIMAL(6,3) ) / 100 ) as decimal(4,3) ))

    select * from TeamRecord

    There was a problem with the previous solution when there was a tie for first place, the result had duplicates, so here is a corrected solution:

    insert into TeamRecord (League, D , TA,W,L, GB)

    select 'American','East', 'Devil Rays' , 52, 32 , 0 union all

    select 'American','East','Red Sox' , 52, 32 , 0 union all

    select 'American','East','Yankees' , 45 , 40 , 7.5 union all

    select 'American','East', 'Orioles' , 43, 40 , 8.5 union all

    select 'American','East','Blue Jays' , 41, 45 , 12.0

    select CAST( ( CAST( LDLeader.W as smallint ) - TeamRecord.W - LDLeader.L + TeamRecord.L ) / 2.0 as decimal(4,1) )

    , TeamRecord.*

    from TeamRecord

    join (select DISTINCT

    TeamRecord.League

    ,TeamRecord.D

    ,TeamRecord.W

    ,TeamRecord.L

    fromTeamRecord

    join(select League, D, MAX(WP) as WP

    from TeamRecord

    group by League, D

    ) as LDWP

    on TeamRecord.League= LDWP.League

    and TeamRecord.D= LDWP.D

    and TeamRecord.WP= LDWP.WP

    ) as LDLeader

    on LDLeader.League= TeamRecord.League

    and LDLeader.D= TeamRecord.D

    SQL = Scarcely Qualifies as a Language

  • and if you want a table for only their last 5 or 10 matches played? what would the sql look like than?

  • You can think of the table depicted here as merely a daily snapshot. It is based on actual records of games played. To consider only the last ten games I can deduct that you only need to modify any SQL that generated these snapshot records (for here we inserted them manually) .

    ----------------------------------------------------

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply