This procedure creates a season-to-date park factor (std_park_factor_avg) which is part of what is necessary to create a season-to-date FIPminus column from Retrosheet for starting pitchers in MySQL. Since FIP minus corrects for the parks that a pitcher pitches in, this is a necessary step. The full park factor values used are the ones calculate…

season-to-date average park factor code that goes into FIP- formula

DROP PROCEDURE IF EXISTS std_park_factor_avg;
CREATE PROCEDURE std_park_factor_avg()
BEGIN
    DECLARE pit_id CHAR(10);
    DECLARE lgID CHAR (2); 
    DECLARE YEARID INT;
    DECLARE gdate DATE;
    DECLARE seq INT;
    DECLARE F_park_factor INT;
    DECLARE RNUMBER INT;
    DECLARE accum_F_parkfactor REAL;
    DECLARE accum_row_number INT;
    DECLARE accum_avg_F_parkfactor REAL;
    DECLARE prev_year YEAR(4);
    DECLARE end_of_cursor BOOLEAN;

    DECLARE no_table CONDITION FOR SQLSTATE '42S02';

    DECLARE c1 CURSOR FOR
      SELECT Starting_Pitcher, lg_ID, YEAR_ID, Game_Date, Game_Number, full_park_factor, ROW_NUMBER 
        FROM starting_pitcher_stats
        GROUP BY Starting_Pitcher, lg_ID, YEAR_ID, Game_Date, Game_Number;
        
    DECLARE CONTINUE HANDLER FOR NOT FOUND
      SET end_of_cursor := TRUE;

    SET end_of_cursor := FALSE;  -- reset
    SET prev_year := 0;          -- reset control-break

    OPEN c1;

    fetch_loop: LOOP
      FETCH c1 INTO pit_id, lgID, YEARID, gdate,seq, F_park_factor, RNUMBER;
      IF end_of_cursor THEN
        LEAVE fetch_loop;
      END IF;

      -- check control-break conditions
      IF YEAR(gdate) != prev_year THEN
       SET accum_F_parkfactor := 0.0;
       SET RNUMBER:= 1.0;
       SET accum_avg_F_parkfactor := 0.0;
        SET prev_year := YEAR(gdate);
      END IF;
     
     SET accum_F_parkfactor := accum_F_parkfactor + F_park_factor;
     SET accum_avg_F_parkfactor := accum_F_parkfactor/RNUMBER;

      UPDATE starting_pitcher_stats
        SET std_F_parkfactor =accum_avg_F_parkfactor
        WHERE Starting_Pitcher = pit_id
          AND lg_ID = lgID 
          AND YEAR_ID = YEARID
          AND Game_Date = gdate
          AND Game_Number = seq;

    END LOOP;
    CLOSE c1;
  END