July 2, 2008 at 10:02 am
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;
July 2, 2008 at 11:30 am
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
July 3, 2008 at 5:45 am
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
July 3, 2008 at 6:33 am
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.
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
November 16, 2015 at 8:30 am
and if you want a table for only their last 5 or 10 matches played? what would the sql look like than?
November 18, 2015 at 4:03 pm
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