I’ve been shown the light! Matt Arnold, friendly neighborhood OOTP dev, reminded me that the calculation for OBP isn’t quite what I had remembered. In fact, the denominator isn’t Plate Appearances. Rather, it’s PA minus Sacrifice Hits minus Catcher Interference. Having made that correction, OBP now ties with the game. Our new code is:
#Calculated batting stats for OOTP
DROP TABLE IF EXISTS CalcBatting;
CREATE TABLE IF NOT EXISTS CalcBatting AS
SELECT b.year
, b.league_id
, b.player_id
, b.stint #We can eventually move this down the list
, b.split_id #We can eventually remove
, b.team_id #We can eventually move this down the list
, l.abbr as Lg
, t.abbr as Team
, b.g
, b.ab
, @PA := b.ab+b.bb+b.sh+b.sf+b.hp AS PA
, b.r
, b.h
, b.d
, b.t
, b.hr
, b.rbi
, b.sb
, b.cs
, b.bb
, b.k
, b.ibb
, b.hp
, b.sh
, b.sf
, b.gdp
, b.ci
, round(b.h/b.ab,3) AS ba
, round(b.k/@PA,3) as krate
, round((b.bb)/@PA,3) as bbrate
, @OBP := round((b.h + b.bb + b.hp)/(@PA-b.sh-b.ci),3) AS obp
, round(100*(@OBP/r.woba),0) as 'OBP+'
/* NOT YET CONVERTED TO OOTP AND MYSQL
, round((b.h+[b.2b]+2*b.[3b]+3*b.hr)/b.ab,3) as slg
, [obp]+[slg] as ops
, [slg]-[avg] as iso
, round((r.wobaBB*nz(b.bb,0) + r.wobahb*nz(b.hbp,0) + r.woba1b*(b.h-b.[2b]-b.[3b]-b.hr) + r.woba2b*b.[2b] + r.woba3b*b.[3b] + r.wobahr*b.hr)/(b.ab+nz(b.bb,0)-nz(b.ibb,0)+nz(b.sf,0)+nz(b.hbp,0)),3) as woba
, round((([woba]-r.lgwoba)/r.wobascale)*[PA],1) as wRAA
, round(((([woba]-r.lgwoba)/r.wobascale)+(l.totr/l.totpa))*[PA],0) as wRC
, ((([wRAA]/[PA] + l.RperPA) + (l.RperPA - t.bpf*l.RperPA))/(lb.wRC/lb.PA))*100 AS [wRC+]
*/
FROM
players_career_batting_stats b
INNER JOIN leagues l ON b.league_id=l.league_id
INNER JOIN teams t ON b.team_id=t.team_id
INNER JOIN tblRunValues2 r ON b.year=r.year AND b.league_id=r.league_id
WHERE b.ab<>0 AND b.split_id=1
ORDER BY b.player_id, b.year