[MYSQL] Multi-year FIP Function for Retrosheet

Retrosheet Multi-Year FIP

-- function to calculate multi-year FIP for Retrosheet
-- This snippet supposes you have the Fangraphs GUTS table located with-in retrosheet.
-- Currently calculates career FIP. Just add a year parameter and it works for single season calculations. 

DELIMITER //

	CREATE FUNCTION FIP(pitcher VARCHAR(8))
	RETURNS DECIMAL(10,2)
	BEGIN
		DECLARE FIP DECIMAL(10,2);
		SET FIP = 0;
		SELECT (SELECT SUM(w_FIP) as sum_FIP
		FROM (
			SELECT	((SUM(if(e.event_CD=23,1,0))*13
					+ SUM(if(e.event_CD BETWEEN 14 AND 16,1,0))*3
					- SUM(if(e.event_CD=3,1,0))*2)
					/ (SUM(e.event_outs_ct)/3) 
					+ g.cFIP)
					*SUM(e.event_outs_ct) as w_FIP	 		 	
			FROM events e
			INNER JOIN GUTS g
				ON substring(e.game_id,4,4)=g.season
			WHERE e.pit_id=pitcher
			GROUP BY g.season) as FIP_table)
		/ SUM(e.event_outs_ct) into FIP
		FROM events e
		WHERE e.pit_id=pitcher
		LIMIT 1;
		
		RETURN FIP;
	END //
	
DELIMITER ;