SELECT IF(Mid(g.gameName,10,2)=2 And Mid(g.gameName,13,2)<=20,Mid(g.gameName,5,4)-1, IF(Mid(g.gameName,10,2)=1,Mid(g.gameName,5,4)-1,Mid(g.gameName,5,4))) AS Season, DATE(CONCAT(SUBSTR(g.gameName, 5, 4), '-', SUBSTR(g.gameName, 10, 2), '-', SUBSTR(g.gameName, 13, 2))) as gameDate,
d.game_pk, g.gameName AS game_id, MID(g.gameName,26,3) AS level_cd,
IFNULL(d.venue_id,g.stadiumID) AS stadium_id, IF(c.attendence='',Null,c.attendence) AS attendence_ct,
MID(g.gameName,16,6) AS away_cd, MID(g.gameName,23,6) AS home_cd, IFNULL(d.away_team_id,IF(g.away>'9999',null,g.away)) AS away_id, IFNULL(d.home_team_id,IF(g.home>'9999',null,g.home)) AS home_id,
d.league AS league_cd, d.away_league_id, d.home_league_id,
IFNULL(g.type,d.game_type) AS gametype_cd, IFNULL(g.gameday_sw,d.gameday_sw) AS gameday_sw,
CASE g.gameday_sw WHEN 'P' THEN 'T' WHEN 'E' THEN 'T' WHEN 'Y' THEN 'T' ELSE 'F' END AS pitches_fl,
d.day, g.local_game_time, c.gamelength, IF(c.temperature=0,Null,c.temperature) AS temperature, c.forecast, IF(c.temperature=0 AND c.windMPH=0,Null,c.windMPH) AS windMPH, c.windDirection,
FROM gameday.games AS g LEFT JOIN gameday.gamedetail AS d ON g.gamename=d.gamename LEFT JOIN gameday.gameconditions AS c ON g.gamename=c.gamename;
On 03/19/14, mcbrown<ppicalino@users.sf.net> wrote:
Oh right, almost forgot about that play!
By the way, the potential relevance of this issue to BBOS is that the data in the "games" table is not fully populated for 2013, at least for me. I have tried it a couple of times with the same result. I assume it is related to these errors, and I'm hoping someone else has already found a workaround.
If you have not yet signed up for the Retrolist and/or RetroSQL Yahoo
groups and plan to use Retrosheet data you probably want to.
That being said, bbos doesn't have a convenient spot to say "hold on, after
you download that file I want to go manipulate the bad data out of it
before processing.". Since most people just use the events, I don't know
how much its needed. Apparently the data will be fixed "in the Spring"
according to Retrosheet.
Let us know if this is really important and you need a workaround before
Retrosheet does their update.
Thanks
SELECT
IF(Mid(g.gameName,10,2)=2 And
Mid(g.gameName,13,2)<=20,Mid(g.gameName,5,4)-1,
IF(Mid(g.gameName,10,2)=1,Mid(g.gameName,5,4)-1,Mid(g.gameName,5,4))) AS
Season,
DATE(CONCAT(SUBSTR(g.gameName, 5, 4), '-', SUBSTR(g.gameName, 10, 2),
'-', SUBSTR(g.gameName, 13, 2))) as gameDate,
d.game_pk,
g.gameName AS game_id,
MID(g.gameName,26,3) AS level_cd,
IFNULL(d.venue_id,g.stadiumID) AS stadium_id,
IF(c.attendence='',Null,c.attendence) AS attendence_ct,
MID(g.gameName,16,6) AS away_cd,
MID(g.gameName,23,6) AS home_cd,
IFNULL(d.away_team_id,IF(g.away>'9999',null,g.away)) AS away_id,
IFNULL(d.home_team_id,IF(g.home>'9999',null,g.home)) AS home_id,
d.league AS league_cd,
d.away_league_id,
d.home_league_id,
IFNULL(g.type,d.game_type) AS gametype_cd,
IFNULL(g.gameday_sw,d.gameday_sw) AS gameday_sw,
CASE g.gameday_sw
WHEN 'P' THEN 'T'
WHEN 'E' THEN 'T'
WHEN 'Y' THEN 'T'
ELSE 'F'
END AS pitches_fl,
d.day,
g.local_game_time,
c.gamelength,
IF(c.temperature=0,Null,c.temperature) AS temperature,
c.forecast,
IF(c.temperature=0 AND c.windMPH=0,Null,c.windMPH) AS windMPH,
c.windDirection,
By the way, the potential relevance of this issue to BBOS is that the data
in the "games" table is not fully populated for 2013, at least for me. I
have tried it a couple of times with the same result. I assume it is
related to these errors, and I'm hoping someone else has already found a
workaround.
There are some simple workarounds if I can't get the games data loaded properly, so it's not at all critical to me. And Brian's post has made me realize that I can probably get all of the retrosheet.games data from the gameday database should I need/want it.
Thanks for your help!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
And after reading that thread on the Yahoo group, there is of course the obvious solution of just fixing the retrosheet data and parsing/importing it myself...
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
SELECT
IF(Mid(g.gameName,10,2)=2 And Mid(g.gameName,13,2)<=20,Mid(g.gameName,5,4)-1,
IF(Mid(g.gameName,10,2)=1,Mid(g.gameName,5,4)-1,Mid(g.gameName,5,4))) AS Season,
DATE(CONCAT(SUBSTR(g.gameName, 5, 4), '-', SUBSTR(g.gameName, 10, 2), '-', SUBSTR(g.gameName, 13, 2))) as gameDate,
d.game_pk,
g.gameName AS game_id,
MID(g.gameName,26,3) AS level_cd,
IFNULL(d.venue_id,g.stadiumID) AS stadium_id,
IF(c.attendence='',Null,c.attendence) AS attendence_ct,
MID(g.gameName,16,6) AS away_cd,
MID(g.gameName,23,6) AS home_cd,
IFNULL(d.away_team_id,IF(g.away>'9999',null,g.away)) AS away_id,
IFNULL(d.home_team_id,IF(g.home>'9999',null,g.home)) AS home_id,
d.league AS league_cd,
d.away_league_id,
d.home_league_id,
IFNULL(g.type,d.game_type) AS gametype_cd,
IFNULL(g.gameday_sw,d.gameday_sw) AS gameday_sw,
CASE g.gameday_sw
WHEN 'P' THEN 'T'
WHEN 'E' THEN 'T'
WHEN 'Y' THEN 'T'
ELSE 'F'
END AS pitches_fl,
d.ind,
d.status,
d.reason,
d.scheduled_innings,
d.inning,
d.top_inning,
d.outs,
d.day,
g.local_game_time,
c.gamelength,
IF(c.temperature=0,Null,c.temperature) AS temperature,
c.forecast,
IF(c.temperature=0 AND c.windMPH=0,Null,c.windMPH) AS windMPH,
c.windDirection,
d.away_win,
d.away_loss,
d.home_win,
d.home_loss,
d.away_team_runs,
d.away_team_hits,
d.away_team_errors,
d.home_team_runs,
d.home_team_hits,
d.home_team_errors
FROM gameday.games AS g
LEFT JOIN gameday.gamedetail AS d
ON g.gamename=d.gamename
LEFT JOIN gameday.gameconditions AS c
ON g.gamename=c.gamename;
Oh right, almost forgot about that play!
By the way, the potential relevance of this issue to BBOS is that the data in the "games" table is not fully populated for 2013, at least for me. I have tried it a couple of times with the same result. I assume it is related to these errors, and I'm hoping someone else has already found a workaround.
Re: [baseballonastic:discussion] errors in 2013 retrosheet data?
Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/baseballonastic/discussion/820145/
To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/
Hello,
The answer and "by hand" solution can be found here.
https://groups.yahoo.com/neo/groups/RetroList/conversations/topics/11374
If you have not yet signed up for the Retrolist and/or RetroSQL Yahoo
groups and plan to use Retrosheet data you probably want to.
That being said, bbos doesn't have a convenient spot to say "hold on, after
you download that file I want to go manipulate the bad data out of it
before processing.". Since most people just use the events, I don't know
how much its needed. Apparently the data will be fixed "in the Spring"
according to Retrosheet.
Let us know if this is really important and you need a workaround before
Retrosheet does their update.
Thanks
On Wed, Mar 19, 2014 at 3:09 PM, Brian L Cartwright blcartwright@users.sf.net wrote:
Thanks - I have applied to join the Yahoo group.
There are some simple workarounds if I can't get the games data loaded properly, so it's not at all critical to me. And Brian's post has made me realize that I can probably get all of the retrosheet.games data from the gameday database should I need/want it.
Thanks for your help!
And after reading that thread on the Yahoo group, there is of course the obvious solution of just fixing the retrosheet data and parsing/importing it myself...