Batting Stats 2: Correction

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

Leave a Reply

Your email address will not be published. Required fields are marked *