Gameday Fielder Positions

Uses the Baseball On A Stick database schema.

CREATE TABLE player_subs AS
SELECT gameName
    , IF(halfInning = "top","home","away") AS homeAway
    , eventNumber
    , player
    , (CASE WHEN des LIKE "%playing second base%" THEN "2B"
        WHEN des LIKE "%playing third base%" THEN "3B"
        WHEN des LIKE "%playing first base%" THEN "1B"
        WHEN des LIKE "%playing shortstop%" THEN "SS"
        WHEN des LIKE "%playing catcher%" THEN "C"
        WHEN des LIKE "%playing left field%" THEN "LF"
        WHEN des LIKE "%playing center field%" THEN "CF"
        WHEN des LIKE "%playing right field%" THEN "RF"
        WHEN des LIKE "%playing designated hitter%" THEN "DH"
        WHEN des LIKE "%as the second base%" THEN "2B"
        WHEN des LIKE "%as the third base%" THEN "3B"
        WHEN des LIKE "%as the first base%" THEN "1B"
        WHEN des LIKE "%as the shortstop%" THEN "SS"
        WHEN des LIKE "%as the catcher%" THEN "C"
        WHEN des LIKE "%as the left field%" THEN "LF"
        WHEN des LIKE "%as the center field%" THEN "CF"
        WHEN des LIKE "%as the right field%" THEN "RF"
        WHEN des LIKE "%as the designated hitter%" THEN "DH"
        WHEN des LIKE "%to second base%" THEN "2B"
        WHEN des LIKE "%to third base%" THEN "3B"
        WHEN des LIKE "%to first base%" THEN "1B"
        WHEN des LIKE "%to shortstop%" THEN "SS"
        WHEN des LIKE "%to catcher%" THEN "C"
        WHEN des LIKE "%to left field%" THEN "LF"
        WHEN des LIKE "%to center field%" THEN "CF"
        WHEN des LIKE "%to right field%" THEN "RF"
        WHEN des LIKE "%to designated hitter%" THEN "DH"
        ELSE "UNK" END) AS POS
    , des
FROM gameday.action a
WHERE (event = "Defensive Switch" OR event = "Defensive Sub");
 
CREATE TABLE game_starters AS
SELECT g AS gameName, h AS homeAway, p AS player, game_position AS POS
FROM (SELECT p.gameName AS g, p.homeAway AS h, p.id AS p, p.game_position
    FROM gameday.players p
    WHERE p.game_position IS NOT NULL) a
LEFT OUTER JOIN player_subs s
ON g = s.gameName
AND h = s.homeAway
AND p = s.player
AND s.player IS NULL;
 
CREATE TABLE event_player_subs AS
SELECT p.gameName, p.homeAway, a.eventNumber, p.POS, p.player
FROM player_subs p, (SELECT s.gameName, s.homeAway, s.POS, a.eventNumber, MAX(s.eventNumber) AS subNumber
FROM gameday.atbats a, player_subs s
WHERE a.gameName = s.gameName
AND IF(a.halfInning = "top","home","away") = s.homeAway
AND a.eventNumber >= s.eventNumber
GROUP BY s.gameName, s.homeAway, s.POS, a.eventNumber) a
WHERE p.gameName = a.gameName
AND p.homeAway = a.homeAway
AND p.eventNumber = a.subNumber
AND p.POS = a.POS;
 
CREATE TABLE event_player_starters AS
SELECT s.gameName, s.homeAway, a.eventNumber, s.POS, s.player
FROM gameday.atbats a, game_starters s
WHERE a.gameName = s.gameName
AND IF(a.halfInning = "top","home","away") = s.homeAway;
 
CREATE TABLE event_player AS
SELECT * FROM (SELECT p.gameName, p.homeAway, p.eventNumber, p.POS, p.player FROM event_player_starters p
LEFT JOIN event_player_subs s
ON p.gameName = s.gameName
AND p.homeAway = s.homeAway
AND p.eventNumber = s.eventNumber
AND p.POS = s.POS
WHERE s.POS IS NULL
UNION ALL
SELECT gameName, homeAway, eventNumber, player, POS FROM player_subs) a
ORDER BY gameName, eventNumber, POS;