title: SQL Bowling Score Calculations
body: <p>I'm still a SQL noob and I've been struggling with this problem for quite some time and am finally at my wits end, and thus I must ask for assistance.
  I am attempting to calculate the score of a bowlers game but don't understand how to do math on columns within the same table as well as put in the logic to handle when it's an open frame, strike or spare, and the 10th frame. The table that holds the game data is this:</p>
  
  <pre><code>TYearLeagueGamePlayerFrameThrows
       intYearID
      ,intLeagueID
      ,intTeamID
      ,intGameID
      ,intGameSetIndex
      ,intPlayerID
      ,intFrameID
      ,intTurnID
      ,intThrowPinCount
  </code></pre>
  
  <p>So for one bowlers game the inserts look like:</p>
  
  <pre><code>             ( 1, 1, 1, 1, 1, 1, 1, 1, 9 )  
          ,( 1, 1, 1, 1, 1, 1, 1, 2, 1 ) 
          ,( 1, 1, 1, 1, 1, 1, 2, 1, 8 )
          ,( 1, 1, 1, 1, 1, 1, 2, 2, 0 )
          ,( 1, 1, 1, 1, 1, 1, 3, 1, 10 )
          ,( 1, 1, 1, 1, 1, 1, 4, 1, 10 )
          ,( 1, 1, 1, 1, 1, 1, 5, 1, 7 )
          ,( 1, 1, 1, 1, 1, 1, 5, 2, 2 )
          ,( 1, 1, 1, 1, 1, 1, 6, 1, 9 )
          ,( 1, 1, 1, 1, 1, 1, 6, 2, 0 )
          ,( 1, 1, 1, 1, 1, 1, 7, 1, 6 )
          ,( 1, 1, 1, 1, 1, 1, 7, 2, 3 )
          ,( 1, 1, 1, 1, 1, 1, 8, 1, 1 )
          ,( 1, 1, 1, 1, 1, 1, 8, 2, 9 )
          ,( 1, 1, 1, 1, 1, 1, 9, 1, 7 )
          ,( 1, 1, 1, 1, 1, 1, 9, 2, 2 ) 
          ,( 1, 1, 1, 1, 1, 1, 10, 1, 9 )
          ,( 1, 1, 1, 1, 1, 1, 10, 2, 1 )
          ,( 1, 1, 1, 1, 1, 1, 10, 3, 8 )
  </code></pre>
  
  <p>Any help is greatly appreciated!!</p>
  
  <p>The only thing I've been able to come up with so far is this, but the totals are wrong:</p>
  
  <pre><code>  DECLARE    @TurnID    AS INTEGER
          ,@Count     AS INTEGER
          ,@Frame     AS INTEGER
          ,@Score     AS INTEGER
          ,@Value     AS INTEGER
          ,@Throw1    AS INTEGER
          ,@Throw2    AS INTEGER
          ,@Throw3    AS INTEGER
          ,@Throw4    AS INTEGER
          ,@Total     AS INTEGER
  
  SELECT 
      TOP 21 
      intTurnID 
      ,intPlayerID 
  
  FROM 
       TYearLeagueTeamPlayerGameFrameThrows 
  
  WHERE
      intPlayerID = @intPlayerID 
  ORDER BY
  
        intTurnID 
       ,intFrameID 
  
  SET @Count      = @@ROWCOUNT 
  SET @TurnID     = 1
  SET @Frame      = 0
  SET @Score      = 0
  SET @Total      = 0
  
  WHILE @Frame &lt; 10 AND @TurnID &lt; @Count 
      BEGIN
  
      SET @Frame  = @Frame + 1
  
  
  SELECT 
      @Throw1 = intThrowPinCount 
  FROM 
      TYearLeagueTeamPlayerGameFrameThrows 
  WHERE
      intFrameID  = 1
  AND 
      intThrowPinCount = @TurnID 
  
  SELECT
      @Throw2 = intThrowPinCount 
  FROM
      TYearLeagueTeamPlayerGameFrameThrows 
  WHERE
      intFrameID  = 1
  AND 
      intThrowPinCount = @TurnID + 1
  
  SELECT 
      @Throw3 = intThrowPinCount
  FROM
      TYearLeagueTeamPlayerGameFrameThrows 
  WHERE
      intFrameID = 2
  AND 
      intThrowPinCount = @TurnID + 2
  
  
  SET @Value = 
      CASE
      WHEN @Throw1            = 10 THEN @Throw1 + @Throw2 + @Throw3 --STRIKE
      WHEN @Throw1 + @Throw2  = 10 THEN @Throw1 + @Throw2 + @Throw3 --SPARE
      ELSE @Throw1 + @Throw2 
  
  END
  
  SET @TurnID = @TurnID + 
      CASE 
      WHEN @Throw1            = 10 THEN 3 --STRIKE
      WHEN @Throw1 + @Throw2  = 10 THEN 2 --SPARE
      ELSE 1
  
  END
  
  SET @Score = @Score + @Value 
      IF @Throw1 + @Throw2 &lt;= 10 or @Throw3 IS NOT NULL
          PRINT 'FRAME: ' + str(@Frame, 2 ) + '  Score: ' + str(@Score, 3 )  
  END
  </code></pre>
  
  <p>The frames and game should total to be:</p>
  
  <pre><code>Frame 1 = 18
  Frame 2 = 26
  Frame 3 = 53
  Frame 4 = 72
  Frame 5 = 81
  Frame 6 = 90
  Frame 7 = 99
  Frame 8 = 116
  Frame 9 = 125
  Frame 10 = 143 - Final Score
  </code></pre>