Anyone familiar with the Retrosheet data? (1 Viewer)

[Mike]

I've extracted all of the 2009 event and game files from Retrosheet.org and created MySQL tables as follows:

create table retrosheet_bevent (

EventID int(8) not null auto_increment,

game_id char(12) not null,

visiting_team char(3) not null,

inning int(2) not null,

batting_team int(1) not null,

outs int(1) not null,

balls int(1) not null,

strikes int(1) not null,

vis_score int(2) not null,

home_score int(2) not null,

res_batter varchar(8) not null,

res_batter_hand enum('R','L') not null,

res_pitcher varchar(8) not null,

res_pitcher_hand enum('R','L') not null,

first_runner varchar(8),

second_runner varchar(8),

third_runner varchar(8),

event_text varchar(64) not null,

leadoff_flag enum('T','F') not null,

pinch_hit_flag enum('T','F') not null,

defensive_position int(2) not null,

lineup_position int(1) not null,

event_type int(2) not null,

batter_event_flag char(1) not null,

ab_flag enum('T','F') not null,

hit_value int(1) not null,

sh_flag enum('T','F') not null,

sf_flag enum('T','F') not null,

outs_on_play int(1) not null,

rbi_on_play int(1) not null,

wild_pitch_flag enum('T','F') not null,

passed_ball_flag enum('T','F') not null,

num_errors int(1) not null,

batter_dest int(1) not null,

first_runner_dest int(1) not null,

second_runner_dest int(1) not null,

third_runner_dest int(1) not null,

first_runner_sb_flag enum('T','F') not null,

second_runner_sb_flag enum('T','F') not null,

third_runner_sb_flag enum('T','F') not null,

first_runner_cs_flag enum('T','F') not null,

second_runner_cs_flag enum('T','F') not null,

third_runner_cs_flag enum('T','F') not null,

first_runner_picked_off_flag enum('T','F') not null,

second_runner_picked_off_flag enum('T','F') not null,

third_runner_picked_off_flag enum('T','F') not null,

first_runner_pitcher varchar(8),

second_runner_pitcher varchar(8),

third_runner_pitcher varchar(8),

primary key (EventID)

);

create table retrosheet_bgame (

game_id varchar(12) not null,

game_date date not null,

game_number int(1) not null,

day_of_week enum('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') not null,

start_time int(4) not null,

dh_used_flag enum('T','F') not null,

day_night_flag enum('D','N') not null,

visiting_team char(3) not null,

home_team char(3) not null,

game_site char(5) not null,

vis_starting_pitcher varchar(8) not null,

home_starting_pitcher varchar(8) not null,

attendance int(6),

time_of_game int(3),

number_of_innings int(2) not null,

visitor_final_score int(2) not null,

home_final_score int(2) not null,

visitor_hits int(2) not null,

home_hits int(2) not null,

visitor_errors int(2) not null,

home_errors int(2) not null,

visitor_left_on_base int(2) not null,

home_left_on_base int(2) not null,

winning_pitcher varchar(8) not null,

losing_pitcher varchar(8) not null,

save_for varchar(8),

vis_last_pitcher varchar(8),

home_last_pitcher varchar(8),

primary key (game_id)

);

It doesn't include absolutely everything from the event and game files which you can see from this link down near the bottom, but I think I have all of the essential data I need.

Where I'm stuck is trying to query this data to determine save opportunities, so then I can figure out blown saves. The number of saves achieved is right in the game file as "save_for".

Somehow I need to look at the event file and find the first time a pitcher enters a ballgame if he has a save opportunity at that point. Anyone have any ideas?

First time I've used the geek post icon, but it seems very fitting here.