Bowling Challenge - The Results

Many moons I ago I wrote The Bowling Challenge, not realizing how much interest the challenge would generate, or the lack of time I was about to have to try to test them all. I won't say I'm caught up entirely, but I did start working through the responses again this weekend. I'm going to show you one response that I believe scores a game correctly, but since I took so long I'll picking another winning entry soon - I'll be looking for one that takes a different approach than this one.

To start with, if you're not a ace bowling scorer, you might some this online scoring calculator handy.

I identified the following test scenarios that I thought would do a good job of testing the implementation. If you can think of a scenario that could reveal a bug that these don't, drop me a note and I'll add it to the test suite.

Perfect game. Bowler threw strikes in all 10 frames and on the 'extra'
ball at the end of the 10th frame. 10th frame is the worst exception case,
want to be sure we test it.
No strikes, no spares, only score pins on first ball of each frame.
No strikes, no spares, score on both frames.
All spare game, no score on extra ball in 10th. Testing the margins again.
All spare game, strike on extra ball in 10th.
9 spares, all strikes in last frame. Not sure this is a good test, but
doesn't hurt to test it
Absolutely no score

I didn't test validation. We're trying to prove the algorithm, I'm assuming

that anyone who can get the algorithm to work can write the validation to check

for too many frames, more than 10 pins in a frame, etc.

To test the entry this article is based on, start by executing the following

in a test database:

CREATE TABLE [Scores] (
[RowID]

[int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL

,
[DateAdded] [smalldatetime] NOT NULL CONSTRAINT

[DF__Scores__DateAdde__76CBA758] DEFAULT (getdate()),
[FrameNumber]

[tinyint] NOT NULL ,
[Ball1] [tinyint] NOT NULL ,
[Ball2] [tinyint]

NOT NULL ,
[NumberOfPins] [smallint] NULL ,
CONSTRAINT [PK_Scores]

PRIMARY KEY CLUSTERED
(
[RowID]
) ON [PRIMARY]
) ON

[PRIMARY]
GO

Even though it's a little long, I'm going to put the code inline here - both

to make it easier to read and because I know it'll get included in the next of

our 'Best of SQLServerCentral' volumes!

create Procedure dbo.usp_ScoreBowlingGame
@UserID int,
@Ball1 tinyint,
@Ball2 tinyint = 0,
@Ball3 tinyint = 0
AS
--Score a bowling game
Set @Ball1 = isnull(@Ball1, 0)
Set @Ball2 = isnull(@Ball2, 0)
Set @Ball3 = isnull(@Ball3, 0)
Set NoCount On
Declare @NextFrameNumber tinyint
Declare @Score smallint
Declare @FrameNumber tinyint
Declare @FrameBall1 smallint
Declare @FrameBall2 smallint
Declare @NextBall1 smallint
Declare @NextBall2 smallint
Declare @NumberOfPins smallint
Declare @RunningTotal smallint
Set @RunningTotal = 0
--calculate next frame
Select @NextFrameNumber = isnull(Max(FrameNumber), 0) + 1
 From Scores with (nolock)
 Where userid = @UserID
--dont allow extra frames or pin count higher than 10
if (@NextFrameNumber < 10 and @Ball1 + @Ball2 <= 10)
 or (@NextFrameNumber = 10 and @Ball1 + @Ball2 + @Ball3 <= 30)
begin
--insert frame pins
insert into scores(userid, framenumber, ball1, ball2, numberofpins)
  values (@UserID, @NextFrameNumber, @Ball1, @Ball2, null)
--select all frames in order
declare bowling_cursor cursor for
select framenumber, ball1, ball2, numberofpins
 from scores with (nolock)
 where userid = @UserID
 and framenumber <= @NextFrameNumber
 order by framenumber
open bowling_cursor
fetch next from bowling_cursor into @FrameNumber, @FrameBall1, @FrameBall2, @NumberOfPins
while @@Fetch_Status = 0
begin
--if frame already scored, skip
if @NumberOfPins is NULL
begin
--reset value
Set @Score = NULL
if @FrameNumber < 10
begin
--is strike
if @FrameBall1 = 10
begin
if @FrameNumber <> 9
begin
if exists(select * from scores where framenumber = @FrameNumber + 1 and userid = @UserID)
begin
Select @NextBall1 = ball1, @NextBall2 = ball2 from scores
    where framenumber = @FrameNumber + 1
 and userid = @UserID
--if strike
if @NextBall1 = 10
begin
if exists(select * from scores
            where framenumber = @FrameNumber + 2
and userid = @UserID)
begin
Select @NextBall2 = ball1 from scores
  where framenumber = @FrameNumber + 2
  and userid = @UserID
Set @RunningTotal = @RunningTotal + 10 + @NextBall1 + @NextBall2
Set @Score = @RunningTotal
end
else
begin
Set @Score = Null
end
end
else
begin
Set @RunningTotal = @RunningTotal + 10 + @NextBall1 + @NextBall2
Set @Score = @RunningTotal
end
end
end
if @FrameNumber = 9
begin
if exists(select * from scores
            where framenumber = @FrameNumber + 1 and userid = @UserID)
begin
Select @NextBall1 = ball1, @NextBall2 = ball2 from scores
   where framenumber = @FrameNumber + 1
and userid = @UserID
Set @RunningTotal = @RunningTotal + 10 + @NextBall1 + @NextBall2
Set @Score = @RunningTotal
end
end
end
--is spare
if @FrameBall1 + @FrameBall2 = 10  and @FrameBall1 < 10
begin
if exists(select * from scores where framenumber = @FrameNumber + 1 and userid = @UserID)
begin
Select @NextBall1 = ball1 from scores where framenumber = @FrameNumber + 1 and userid = @UserID
Set @RunningTotal = @RunningTotal + 10 + @NextBall1
Set @Score = @RunningTotal
end
end
--is other
if @FrameBall1 + @FrameBall2 < 10
begin
Set @RunningTotal = @RunningTotal + @FrameBall1 + @FrameBall2
Set @Score = @RunningTotal
end
end
--if frame 10
if @FrameNumber = 10
begin
Set @RunningTotal = @RunningTotal + @Ball1 + @Ball2 + @Ball3
Set @Score = @RunningTotal
print 'Game Over'
end
if @Score is not null
begin
--update score for current frame and user
update scores set numberofpins = @Score
  where framenumber = @FrameNumber and userid = @UserID
end
end
else
begin
--print 'Skipped frame: ' + cast(@FrameNumber as varchar(2)) + ', already scored.'
Select @RunningTotal = numberofpins from scores
                       where framenumber = @FrameNumber and userid = @UserID
end
fetch next from bowling_cursor into @FrameNumber, @FrameBall1, @FrameBall2, @NumberOfPins
end
close bowling_cursor
deallocate bowling_cursor
end

I think my first thought on examining this solution is that isn't set

based. Not a requirement, only requirement is that the answer has to be correct!

I noted some decent validation up front to deal with nulls, pins that exceed the

allowable range. Cursor being used - note that it's not specified as local or

readonly, something easily corrected and doesn't affect the test results. I also

see that the code is readable, good variable names, and some comments, enough

for me to decipher the flow. The assumption is that the proc is called once for

each frame, passing in the number of pins for the first and second balls (and

for the third ball on the tenth frame).

I set up a new test database, created the table as noted above, created the

procedure. I then ran through the following tests.

declare @Score int

set nocount on

--test perfect game

delete from scores

exec dbo.usp_ScoreBowlingGame 1, 10, 0, 0

exec dbo.usp_ScoreBowlingGame 1, 10, 10, 10

select @Score = max(numberofpins) from scores with (nolock) where userid = 1

print 'Perfect game'

if @Score = 300

print '--Passed'

else

print '--Failed'

--test all no strike, no spare game

delete from scores

exec dbo.usp_ScoreBowlingGame 1, 1, 0, 0

select @Score = max(numberofpins) from scores with (nolock) where userid = 1

print 'No strike/no spare'

if @Score = 10

print '--Passed'

else

print '--Failed'

delete from scores

exec dbo.usp_ScoreBowlingGame 1, 1, 1, 0

select @Score = max(numberofpins) from scores with (nolock) where userid = 1

print 'No strike/no spare, scored in all frames'

if @Score = 20

print '--Passed'

else

print '--Failed'

--test all all spare game

delete from scores

exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0

select @Score = max(numberofpins) from scores with (nolock) where userid = 1

print 'All spare game'

if @Score = 109

print '--Passed'

else

print '--Failed'

--test all all spare game, strike on last ball

delete from scores

exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0

exec dbo.usp_ScoreBowlingGame 1, 1, 9, 10

select @Score = max(numberofpins) from scores with (nolock) where userid = 1

print 'All spare game, strike on last ball'

if @Score = 119

print '--Passed'

else

print '--Failed'

delete from scores

exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0

exec dbo.usp_ScoreBowlingGame 1, 10, 10, 10

select @Score = max(numberofpins) from scores with (nolock) where userid = 1

print '9 spares, all strikes in last frame'

if @Score = 138

print '--Passed'

else

print '--Failed'

delete from scores

exec dbo.usp_ScoreBowlingGame 1, 0, 0, 0

select @Score = isnull(max(numberofpins), 0) from scores with (nolock) where userid = 1

print 'Total loss!'

if @Score = 0

print '--Passed'

else

print '--Failed'

All tests passed. The only real negative I can see is that the value for the extra ball in the tenth frame is not persisted. The final score returned is correct, but without the stored value you wouldn't be able to rescore the game if needed. This is just housekeeping, easy enough to add a third column to the table even though it would only get used on the tenth frame.

I'm please to announce that the team of Jon Winer, Matt Owen, and Namho Jung will each receive a SSC Polo Shirt for their solution. As Im mentioned earlier, I'll be posting another solution as soon as I find one that is interesting AND passes all the tests. To all of you who took time to participate, I hope you found it as much fun as my team at work did - and don't give up hope, you might yet win!

Bowling Challenge - The Results

Rate

Share

Categories

Share

Rate

Bowling Challenge - The Results

Rate

Share

Categories

Share

Rate

Related content

Help Us Out For A Free Gift

The BestOf vol. 3 and Stumpers Vol. 2

SQLServerCentral.com Celebrates our 200,000 Member Milestone

Decorum in the Forums

What's SQLServerCental.com All About