create table pitching_gamelog AS
select
/* player and game data */
	a.res_pit_ID AS player_ID,
	if(a.bat_home_ID = 1,a.away_team_ID,substring(a.game_ID,1,3)) AS team,
	if(if(a.bat_home_ID = 1,a.away_team_ID,substring(a.game_ID,1,3)) = a.away_team_ID,"road","home") AS loc,
	concat(
		substring(a.game_ID,10,2),"-",
		if(substring(a.game_ID,8,2)="01","Jan",
		if(substring(a.game_ID,8,2)="02","Feb",
		if(substring(a.game_ID,8,2)="03","Mar",
		if(substring(a.game_ID,8,2)="04","Apr",
		if(substring(a.game_ID,8,2)="05","May",
		if(substring(a.game_ID,8,2)="06","Jun",
		if(substring(a.game_ID,8,2)="07","Jul",
		if(substring(a.game_ID,8,2)="08","Aug",
		if(substring(a.game_ID,8,2)="09","Sep",
		if(substring(a.game_ID,8,2)="10","Oct",
		if(substring(a.game_ID,8,2)="11","Nov","Dec"))))))))))),
		"-",substring(a.game_ID,4,4)) AS date,
/* stats area - modify this stuff if you want other information */
	if(if(a.bat_home_ID = "T",a.away_team_ID,substring(a.game_ID,1,3)) = a.away_team_ID,b.away_score_ct,b.home_score_ct) AS team_runs,
	if(if(a.bat_home_ID = "T",a.away_team_ID,substring(a.game_ID,1,3)) = a.away_team_ID,b.home_score_ct,b.away_score_ct) AS opp_runs,
	sum(a.event_outs_ct)/3 AS innings,
	sum(a.balls_ct + strikes_ct) AS pitch_count,
	sum(a.balls_ct) AS balls_count,
	sum(a.strikes_ct) AS strikes_count,
	sum(a.event_runs_ct) AS runs_allowed,
	sum(if(a.h_cd != 0,1,0)) AS hits_allowed,
	sum(if(a.event_cd = 14 OR a.event_cd = 15,1,0)) AS walks_allowed,
	sum(if(a.event_cd = 21,1,0)) AS 2B,
	sum(if(a.event_cd = 22,1,0)) AS 3B,
	sum(if(a.event_cd = 23,1,0)) AS HR,
	sum(if(a.event_cd = 16,1,0)) AS HBP,
	sum(if(a.battedball_cd = "G",1,0)) AS GB,
	sum(if(a.battedball_cd = "F",1,0)) AS FB,
	sum(if(a.battedball_cd = "L",1,0)) AS LD,
	sum(if(a.event_cd = 9,1,0)) AS wild_pitch,
	sum(if(a.bat_event_FL = "T",1,0)) AS TBF,
	sum(if(a.ab_fl = "T",1,0)) AS AB,
	if(a.res_pit_ID = b.away_start_pit_ID OR a.res_pit_ID = b.home_start_pit_ID,"Starter","Reliever") AS pitcher_type,
	if(a.res_pit_ID = b.win_pit_ID,"W",if(a.res_pit_ID = b.lose_pit_ID,"L",if(a.res_pit_ID = b.save_pit_ID,"S","ND"))) AS game_decision,
	a.game_ID AS game_ID

/* Includes only non-playoff games; just change table names if you want to include playoffs */	
from events_regseason a
	inner join games_regseason b
	on a.game_ID = b.game_ID
group by a.res_pit_ID, a.game_ID
order by a.res_pit_ID, substring(a.game_ID,4,9) asc