Re: [baseballonastic:discussion] Re: [baseballonastic:discussion] errors in 2013 retrosheet data?

Help
2014-03-19
2014-03-20
  • Brian L Cartwright

    here is my games script


     
    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;

     
     
    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.


    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/

     
    • Deez Nutz

      Deez Nutz - 2014-03-20

      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:

      here is my games script

      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;

      On 03/19/14, mcbrownppicalino@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.


      Re: [baseballonastic:discussion] errors in 2013 retrosheet data?https://sourceforge.net/p/baseballonastic/discussion/820145/thread/6b6ad462/?limit=25#9dbf/77e7

      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/


      [Re: [baseballonastic:discussion] Re: [baseballonastic:discussion]
      errors in 2013 retrosheet data?](
      http://sourceforge.net/p/baseballonastic/discussion/820145/thread/c3c0b7e1/?limit=25#23a9
      )


      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/

       
      • mcbrown

        mcbrown - 2014-03-20

        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!

         
        • mcbrown

          mcbrown - 2014-03-20

          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...

           

Log in to post a comment.