case
	when game_id = '2007/09/26/seamlb-clemlb-1' then 'SEA200709261'
	else

		upper(substr(g.game_id,19,3))
		|| substr(g.game_id,1,4)
		|| substr(g.game_id,6,2)
		|| substr(g.game_id,9,2)
		||
			case
				when game_id in (
					'2000/09/25/minmlb-clemlb-2'
					, '2007/09/26/clemlb-seamlb-2') then '2'
				when game_id in (
					'2000/09/25/chamlb-clemlb-1') then '1'
				when game_id in (
					'2000/09/19/oakmlb-balmlb-1'
					, '2001/07/18/texmlb-balmlb-1'
					, '2003/08/31/anamlb-kcamlb-1'
					, '2004/09/06/tbamlb-nyamlb-1'
					, '2006/06/10/texmlb-bosmlb-1'
					, '2008/09/06/oakmlb-balmlb-2'
					, '2010/05/12/phimlb-colmlb-1'
					, '2011/09/08/lanmlb-wasmlb-1') then '0'
				when double_header_sw = 'N' then '0' 
				else substr(g.game_id,26,1) 
			end 
end as retro_game_id