Bowling Challenge - The Results

  • Andy Warren

    SSC Guru

    Points: 119676

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarr

  • DBA-Thinker

    SSC Veteran

    Points: 252

    The entry below was submitted about three hours after the challenge first appeared on the web site.  It uses the Scores table as the table was originally defined (instead of a modified version), it seems to address the challenge more directly, it seems to pass all the tests, and the scoring algorithm (the stated focus) is much shorter.  Could you please elaborate on the criteria that favored the winning entry over this one?

    Thanks!

    ------

    CREATE PROCEDURE BowlingChallenge

        @userid int

    AS

    SET NOCOUNT ON

    DECLARE @RowID tinyint,

            @Count tinyint,

            @Frame tinyint,

            @Score smallint,

            @Value tinyint,

            @Ball1 tinyint,

            @Ball2 tinyint,

            @Ball3 tinyint

    CREATE TABLE #Pins (NumberOfPins tinyint, RowID tinyint IDENTITY(1,1))

      INSERT #Pins -- small temporary table for code simplicity because a contiguous set of RowID values was not guaranteed

            (NumberOfPins)

      SELECT TOP 21

             NumberOfPins

        FROM Scores

       WHERE UserID = @userid

    ORDER BY RowID

    SET @Count = @@ROWCOUNT

    SET @RowID = 1

    SET @Frame = 0

    SET @Score = 0

    WHILE @Frame < 10 AND @RowID < @Count

        BEGIN

        SET @Frame = @Frame + 1

        SET @Ball3 = NULL

        SELECT @Ball1 = NumberOfPins FROM #Pins WHERE RowID = @RowID

        SELECT @Ball2 = NumberOfPins FROM #Pins WHERE RowID = @RowID + 1

        SELECT @Ball3 = NumberOfPins FROM #Pins WHERE RowID = @RowID + 2

        SET @Value =

            CASE

            WHEN @Ball1          = 10 THEN @Ball1 + @Ball2 + @Ball3 -- strike

            WHEN @Ball1 + @Ball2 = 10 THEN @Ball1 + @Ball2 + @Ball3 -- spare

                                      ELSE @Ball1 + @Ball2

            END

        SET @RowID = @RowID +

            CASE

            WHEN @Ball1          = 10 THEN 1 -- strike

            WHEN @Ball1 + @Ball2 = 10 THEN 2 -- spare

                                      ELSE 2

            END

        SET @Score = @Score + @Value

        IF @Ball1 + @Ball2 < 10 OR @Ball3 IS NOT NULL PRINT 'Frame: ' + STR(@Frame,2) + ' - Score: ' + STR(@Score,3)

        END

    DROP TABLE #Pins

    RETURN (0)

    Example data...

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  1, 1)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  1, 9)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  2, 1)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  2, 9)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  3, 1)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  3, 9)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  4, 1)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  4, 9)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  5, 1)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  5, 9)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  6, 1)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  6, 9)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  7, 1)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  7, 9)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  8, 1)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  8, 9)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  9, 1)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1,  9, 9)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 10, 1)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 10, 9)

    INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 10,10)

     

  • Adam Machanic

    SSCoach

    Points: 15259

    Including validation in the script earned extra points? That should deduct points, IMO. Data validation belongs in constraints, not in data access code. Yes, this was just a contest, but now it's posted as an example of a "good solution". Data validation in data access code is not a good solution and should probably be written up in one of the "worst practices" articles... As a matter of fact, I think I'll get to work on that right after I hit send on this message

    --
    Adam Machanic
    whoisactive

  • GaryMcAllister

    SSC-Addicted

    Points: 411

    My Solution used 3 functions and 1 stored proc, used the original table.  I added a game id for testing and presumed scores would be entered in order.. I know this is a year on but see below:-

    CREATE FUNCTION FUN_BOW_getLastFrameScore

    (

    @userid INT,

    @GameId INT,

    @FrameNumber SMALLINT

    )

    RETURNS INT

    AS

    BEGIN

    DECLARE @RetVal INT

    SELECT @RetVal = SUM(NumberOfPins) FROM Scores WHERE FrameNumber = @FrameNumber - 1 AND UserId = @userid AND GameId = @GameId

    RETURN ISNULL(@RetVal,0)

    END

     

    CREATE  FUNCTION FUN_BOW_getLastFrameShots

    (

    @userid INT,

    @GameId INT,

    @FrameNumber SMALLINT

    )

    RETURNS INT

    AS

    BEGIN

    DECLARE @RetVal INT

    SELECT @RetVal = Count(1) FROM Scores WHERE FrameNumber = @FrameNumber - 1 AND UserId = @userid AND GameId = @GameId

    RETURN ISNULL(@RetVal,0)

    END

    CREATE FUNCTION FUN_BOW_getNextShotValue

    (

    @userid INT,

    @GameId INT,

    @FrameNumber SMALLINT,

    @NextShots SMALLINT

    )

    RETURNS SMALLINT

    BEGIN

    DECLARE @RETVAL INT

    DECLARE @SHOTNUMBER INT

    SELECT @SHOTNUMBER=MAX(ShotNumber)

    FROM

    (

     SELECT

      (SELECT Count(1) FROM Scores WHERE RowId <= S.RowId) as ShotNumber,

      FrameNumber,

      NumberOfPins

     FROM

      Scores S

     WHERE UserId = @userid AND GameId = @GameId

    ) SN

    WHERE FrameNumber = @FrameNumber

    SELECT @SHOTNUMBER = ISNULL(@SHOTNUMBER,0)

    IF (SELECT Count(1) FROM (SELECT (SELECT Count(1) FROM Scores WHERE RowId <= S.RowId) as ShotNumber, FrameNumber, NumberOfPins FROM Scores S WHERE UserId = @userid AND GameId = @GameId) SN WHERE ShotNumber BETWEEN @SHOTNUMBER + 1 AND @SHOTNUMBER + @NextShots) = @NextShots

    SELECT @RETVAL = SUM(NumberOfPins) FROM

    (

     SELECT

      (SELECT Count(1) FROM Scores WHERE RowId <= S.RowId) as ShotNumber,

      FrameNumber,

      NumberOfPins

     FROM

      Scores S

     WHERE UserId = @userid AND GameId = @GameId

    ) SN

    WHERE ShotNumber BETWEEN @SHOTNUMBER + 1 AND @SHOTNUMBER + @NextShots

    ELSE

     SELECT @RetVal = -1

    RETURN ISNULL(@RetVal,0)

    END

    --USP_BOW_getUserBowlingGame 1,3

    CREATE PROCEDURE USP_BOW_getUserBowlingGame

    (

    @userid INT,

    @GameId INT

    )

    AS

    --DECLARE @userid INT

    --DECLARE @GameId INT

    --SELECT @userid = 1, @GameId = 3

    --Used whilst debugging Stored Procedure

    SELECT

     FrameNumber,

     FinishedGo,

     CASE WHEN FinishedGo = 1 AND  (Pins = 10 AND Split > -1 AND Strike >-1 OR Shots >= 2)   THEN --If they have finished the go display the score.

      (

      SELECT SUM(Score)

      FROM

       (

        SELECT

         FrameNumber,

         CASE WHEN FrameNumber < 10 THEN --Normal bowling rules.

          CASE WHEN SUM(NumberOfPins) < 10 THEN --Have not finished go yet.

           SUM(NumberOfPins)

          ELSE

           CASE WHEN COUNT(FrameNumber) = 1 THEN --Strike.

            CASE WHEN dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,2) = -1 THEN 0 ELSE SUM(NumberOfPins) + dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,2) END

           ELSE --Split

            CASE WHEN dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,1) = -1 THEN 0 ELSE SUM(NumberOfPins) + dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,1) END

           END

          END

         ELSE

          CASE WHEN Count(FrameNumber) < 2 THEN --If the Frame isn't finished.

           0

          ELSE

           SUM(NumberOfPins) --We just want the number

          END

         END  as Score

        FROM

         Scores

        WHERE

         UserId = @userid AND

         GameId = @GameId

        GROUP BY

         FrameNumber

      &nbsp as BowlingSq

      WHERE FrameNumber <= Bowling.FrameNumber

     &nbsp

     ELSE

      NULL

     END

     as RollingScore

    FROM

    (

     SELECT

      FrameNumber,

      Count(FrameNumber) Shots, --Number of shots

      SUM(NumberOfPins) Pins, --Number of Pins

      dbo.FUN_BOW_getLastFrameScore(@UserId, @GameId, FrameNumber) LastScore, --LastScore

      dbo.FUN_BOW_getLastFrameShots(@UserId, @GameId, FrameNumber) LastShots, --LastShots

      dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,1) Split, --Score for a split.

      dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,2) Strike, --Score for a strike.

      CASE WHEN FrameNumber < 10 THEN --Normal Frame

       CASE WHEN Count(FrameNumber) = 2 OR SUM(NumberOfPins) = 10 THEN 1 ELSE 0 END

      ELSE

       CASE WHEN SUM(NumberOfPins) >= 10 THEN --Strike or Spare in the 10th Frame

        CASE WHEN Count(FrameNumber) = 3 THEN 1 ELSE 0 END

       ELSE --Screwed up in the 10th

        CASE WHEN Count(FrameNumber) = 2 THEN 1 ELSE 0 END

       END

      END FinishedGo,

      CASE WHEN FrameNumber < 10 THEN --Normal bowling rules.

       CASE WHEN SUM(NumberOfPins) < 10 THEN --Have not finished go yet.

        SUM(NumberOfPins)

       ELSE

        CASE WHEN COUNT(FrameNumber) = 1 THEN --Strike.

         CASE WHEN dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,2) = -1 THEN 0 ELSE SUM(NumberOfPins) + dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,2) END

        ELSE --Split

         CASE WHEN dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,1) = -1 THEN 0 ELSE SUM(NumberOfPins) + dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,1) END

        END

       END

      ELSE

       CASE WHEN Count(FrameNumber) < 2 THEN --If the Frame isn't finished.

        0

       ELSE

        SUM(NumberOfPins) --We just want the number

       END

      END  as Score

     FROM

      Scores

     WHERE

      UserId = @userid AND

      GameId = @GameId

     GROUP BY

      FrameNumber

    ) as Bowling

    GO

     

    Ive done tests on this and it works pretty well.  I think loops should be shot dead.

     

     

     

     

     

     

     

     

     

  • Salvor

    Ten Centuries

    Points: 1189

    Since the whole thing seems to be revamped, here's the solution I proposed one year ago.

    It's pure set-based T-SQL, no cursor or structured instructions (the If's are there only for debug).

    /********************************************************************************/

    create procedure UserScore

    /*

    Created by Salvor for the SSC's Bowling Challenge

    Purpose: A bowling score calculator

    Usage:

    exec UserScore @userid  -- show the current score for the user

    exec UserScore @userid,1 -- show the current score for the user with additional debug information

    */

       @userid int

      ,@Debug bit = 0

    as

    set nocount on

    declare @UserScores table (

     RowID int IDENTITY (1, 1) NOT NULL ,

     FrameNumber tinyint NOT NULL ,

     NumberOfPins tinyint NOT NULL

    )

    insert into @UserScores(FrameNumber,NumberOfPins)

    select FrameNumber,NumberOfPins

      from Scores

     where UserID=@UserID

     order by FrameNumber,RowID

    if @Debug=1

    begin

       print 'User ' + cast(@UserID as varchar) + ' throws:'

       select * from @UserScores

    end

    Declare @UnfoldedScores TABLE  (

     FrameNumber tinyint NOT NULL ,

     NumberOfPins int NOT NULL ,

     RecordType tinyint NOT NULL

    )

    insert into @UnfoldedScores (FrameNumber,NumberOfPins,RecordType)

    select FrameNumber,NumberOfPins,0

      from @UserScores

    union all

    select s2.FrameNumber, s1.NumberOfPins,1

      from @UserScores s1

           inner join (

              select FrameNumber, max(RowID) RowID

                from @UserScores

              group by FrameNumber

              having sum(NumberOfPins)=10 and count(*)=2

           ) s2 on s1.RowID=s2.RowID+1

    union all

    select s2.FrameNumber, s1.NumberOfPins,2

      from @UserScores s1

           inner join (

              select FrameNumber, max(RowID) RowID

              from @UserScores

              group by FrameNumber

              having sum(NumberOfPins)=10 and count(*)=1

           ) s2 on s1.RowID in(s2.RowID+1,s2.RowID+2)

    If @Debug=1

    begin

       print 'User ' + cast(@UserID as varchar) + ' unfolded scores:'

       print 'RecordType=0 -> Single throw score record'

       print 'RecordType=1 -> Spare bonus record'

       print 'RecordType=2 -> Strike bonus record'

       print 'RecordType=3 -> Previous frame score record'

       select FrameNumber,NumberOfPins,RecordType

       from @UnfoldedScores

       union all

       select s1.FrameNumber,s2.NumberOfPins, 3

       from (select FrameNumber from @UnfoldedScores group by FrameNumber) s1  join

            (select FrameNumber,sum(NumberOfPins) NumberOfPins from @UnfoldedScores group by FrameNumber) s2

         on s1.FrameNumber>s2.FrameNumber

    end

    select @userid 'UserID', s1.FrameNumber 'Frame number',sum(s2.NumberOfPins) 'Running score'

      from (select FrameNumber from @UnfoldedScores group by FrameNumber) s1

           inner join (

              select FrameNumber,sum(NumberOfPins) NumberOfPins from @UnfoldedScores group by FrameNumber

           ) s2 on s1.FrameNumber>=s2.FrameNumber

     group by s1.FrameNumber

    if @Debug=1

    begin

    select @userid 'UserID', s.FrameNumber 'Frame number',s.NumberOfPins 'Single throw score',t.NumberOfPins 'Running score'

    from @UserScores s

         inner join (

            select s1.FrameNumber ,sum(s2.NumberOfPins) NumberOfPins

              from (select FrameNumber from @UnfoldedScores group by FrameNumber) s1

                   inner join (

                      select FrameNumber,sum(NumberOfPins) NumberOfPins from @UnfoldedScores group by FrameNumber

                   ) s2 on s1.FrameNumber>=s2.FrameNumber

             group by s1.FrameNumber

         ) t on s.FrameNumber=t.FrameNumber

    end

    GO

    /********************************************************************************/

    How it works.

    The key concept of this algorithm is 'unfolding the user scores'.

    The score of a frame but the last is given by:

    the sum of the scores of the frame's throws

                     +

    if the frame is a spare, the score of the next throw (that is the first of the next frame)

    if the frame is a strike, the scores of the next two throws (which can belong to two different frames if the user scores another strike)

                     +

    the scores of the previous frames (this could suggest a recursive approach, but I won't go that way)

    For the last frame, both the spare bonus and the stryke bonus are just an extra throw for the same frame, so we only have:

    the sum of the scores of the frame's throws

                     +

    the scores of all the previous frames

    As we will see, the very nature of the last frame is such that we won't need extra code to differentiate the algorithm's behaviour (it will just be a side-effect).

    Let's begin thinking SQL.

    To find 'the sum of the scores of the frame's throws' we'll have of course to group-by on the FrameNumber, summing the NumberOfPins; if the player scored a mark in the current frame, we could also insert the scores of the next frames as belonging to the current frame (that is, where the mark occurred), and the group-by would also include these bonus records. Likewise, if we also inserted the scores of the previous frames as scores of the current frame, the group-by would take care of everything. But, in the latter case there's a catch: the scores of the previous frames should already be inclusive of the bonuses for previous marks: this simply means that we will have to first insert the records for the bonuses, and then calculate and insert the records for previous frames' scores.

    Let's see an example (of course, in the sp we won't modify the Scores table):

    If the Scores table contained these records:

    UserID FrameNumber NumberOfPins

    ------ ----------- ------------

         1           1           10

         1           2            4

         1           2            5

    we would need to insert the following records:

    insert into Scores(UserID,FrameNumber,NumberOfPins) values(1,1,4)  -- 1st strike bonus record

    insert into Scores(UserID,FrameNumber,NumberOfPins) values(1,1,5)  -- 2nd strike bonus record

    This way, the Scores table would look like this:

    UserID FrameNumber NumberOfPins

    ------ ----------- ------------

         1           1           10

         1           2            4

         1           2            5

         1           1            4

         1           1            5

    As you can see, if we grouped by FrameNumber, we'd obtain 19 as sum(NumberOfPins) for frame 1, thus we would need to insert this value for FrameNumber 2:

    insert into Scores(UserID,FrameNumber,NumberOfPins) values(1,2,19) -- Previous frame score record

    Now we'd have

    UserID FrameNumber NumberOfPins

    ------ ----------- ------------

         1           1           10

         1           2            4

         1           2            5

         1           1            4

         1           1            5

         1           2           19

        

    Grouping by FrameNumber, we'll get:

    UserID FrameNumber NumberOfPins

    ------ ----------- ------------

         1           1           19

         1           2           28

    Suppose now the the game goes on, and the player's situation is:

    UserID FrameNumber NumberOfPins

    ------ ----------- ------------

         1           1           10

         1           2            4

         1           2            5

         1           3            5

         1           3            5

         1           4            3

         1           4            0

    Ok, we now have 4 frames and a spare at frame 3 (and of course the strike at frame 1).

    After inserting all the calculated records, we would get

    UserID FrameNumber NumberOfPins

    ------ ----------- ------------

         1           1           10  Frame 1 first throw score (strike)

         1           2            4  Frame 2 first throw score

         1           2            5  Frame 2 second throw score

         1           3            5  Frame 3 first throw score

         1           3            5  Frame 3 second throw score (spare)

         1           4            3  Frame 4 first throw score

         1           4            0  Frame 4 second throw score

         1           3            3  Spare bonus record (first throw of spare 4 added to spare 3)

         1           1            4  Strike bonus record (first throw of spare 2 added to spare 1)

         1           1            5  Strike bonus record (second throw of spare 2 added to spare 1)

         1           2           19  Frame 1 score added to frame 2

         1           3           19  Frame 1 score added to frame 3

         1           4           19  Frame 1 score added to frame 4

         1           3            9  Frame 2 score added to frame 3

         1           4            9  Frame 2 score added to frame 4

         1           4           13  Frame 3 score (including spare bonus) added to frame 4

        

    and so on...    

    In a way, we are unfolding the scores.

    Now, how can we recognize spares and strikes?

    Easy done: a frame scoring 10 with just one throw is a strike, with two throws is a spare.

    In SQL this translates as:

    --Strikes

    select FrameNumber

      from Scores

    group by FrameNumber

    having sum(NumberOfPins)=10

       and count(*)=1

    --Spares

    select FrameNumber

      from Scores

    group by FrameNumber

    having sum(NumberOfPins)=10

       and count(*)=2

    Once we've found spares and strikes, we'll need a way to find the subsequent throws that, as mentioned before, could belong to different frames if the user scores more strikes in a row. We'll see how to handle this in the code.

    Now we can almost start coding, but first we need to make a few assumptions:

    1. The process (or user) that feeds the Scores table is responsible for assuring that the data are correct/consistent: i.e. no more than two throws for each frame but the last, no more than three throws on the last frame but only if the player scores a strike or a frame, no more the 10 pins for each throw ecc... The script doesn't complain for wrong data, but of course the output will be also wrong (or better, will be consistent with the wrong input).

    2. Since the Score table doesn't provide a way to identify a single game, we can assume that each player is assigned a new UserID for each game: that is, the UserID identifies both the player and the game which I'm asking the score of.

    3. Since the Score table doesn't provide a way to identify a single throw (ok, at this point I could've modified the table to contain GameID and Throw number, but I'm too lazy sometimes), we can assume that, given the same FrameNumber, the record with lower RowID corresponds to the first throw. Knowing which is the first throw score is essential to calculate a spare bonus.

    Ok, let's start coding.

    First, we extract the scores of the user we're passing as input parameter, and put them in the table variable @UserScores.

    declare @UserScores table (

       RowID int IDENTITY (1, 1) NOT NULL ,

       FrameNumber tinyint NOT NULL ,

       NumberOfPins tinyint NOT NULL

    )

    insert into @UserScores(FrameNumber,NumberOfPins)

    select FrameNumber,NumberOfPins

      from Scores

     where UserID=@UserID

     order by FrameNumber,RowID

     

    This way, we are sure that the user RowID's will be consecutive, which can not be said for those in Scores (where we have the scores of every user). Besides, we'll be dealing with a smaller structure than Scores, which could grow quite large. Lastly, should Scores' structure or its population logic change (see assumption 2. and 3.), we'll only need to modify this query to select tha data we need, not the following steps.

    Now that the RowID's are consecutive, we can easily find the throws following a spare or a strike: we just need the RowID of the record where the mark occured, and then take the records RowID+1 (spare and strike) and RowID+2 (only for strike).

    We can now make the first unfolding step: inserting the bonus records (for the sake of clarity and debugging purpose I've used a new table to store the unfolded scores, but these could as well be appended to @UserScores, thus avoiding the first UNION ALL).

    Declare @UnfoldedScores TABLE  (

       FrameNumber tinyint NOT NULL ,

       NumberOfPins int NOT NULL ,

       RecordType tinyint NOT NULL

    )

    insert into @UnfoldedScores (framenumber,numberofpins,RecordType)

    select framenumber,numberofpins,0  -- Insert single throw scores

      from @UserScores

    union all

    select s2.framenumber, s1.numberofpins,1  -- Insert spare bonus records

      from @UserScores s1

           inner join (

              select framenumber, max(rowid) rowid

                from @UserScores

              group by framenumber

                having sum(numberofpins)=10 and count(*)=2

           ) s2 on s1.rowid=s2.rowid+1

    union all

    select s2.framenumber, s1.numberofpins,2  -- Insert strike bonus records

      from @UserScores s1

           inner join (

              select framenumber, max(rowid) rowid

                from @UserScores

              group by framenumber

              having sum(numberofpins)=10 and count(*)=1

           ) s2 on s1.rowid in(s2.rowid+1,s2.rowid+2)

    The field RecordType is there for debugging purpose (see later).

    But, wait! What about the last frame - usually the 10th, but we could imagine an extended game with more than 10 frames, and this query will still work - which we should avoid adding the bonus records for? Well, the query above already answers the question: in fact, since we are taking the max(RowID) of the frame where a mark occured, records RowID+1 and RowID+2 must belong to a subsequent frame. By its very nature, the LAST frame has no subsequent RowID's, thus it can't satisfy the join conditions. (As a matter of fact, this is true for each last frame you have in a game: that is, you can't calculate the bonus for a mark at the 4th frame until the 5th throw)

    Let's go on to the second (and last) unfolding step: inserting the previous frames' scores and grouping the frames. Really, at this point it simply means to calculate the running totals for each frames, which can be done in a 'standard' way.

    Also, instead of inserting the records and then selecting them back, I'm returning them back directly to the caller:

    select s1.framenumber 'Frame number',sum(s2.numberofpins) 'Running score'

      from (select framenumber from @UnfoldedScores group by framenumber) s1

           inner join (

              select framenumber,sum(numberofpins) numberofpins from @UnfoldedScores group by framenumber

           ) s2 on s1.framenumber>=s2.framenumber

    group by s1.framenumber

    Here, the last frame has no special treatment.

    Well, we're done... almost.

    We need to test the sp.

    To use the sp, just insert the records into Scores (remember, the sp assumes the data are correct) and call the sp passing the UserID. The sp also accepts a second parameter @Debug which defaults to 0: with @Debug=0, the sp only shows the frames scores, with @Debug=1 the sp also shows the contents of the @UserScores table, the unfolded results, with a RecordType field describing the origin of each record, and a join between single throws (@UserScores table) and frames results.

    And now a few test samples I ran:

    -- 1) 2 players game:

    truncate table Scores

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,1,2)

    exec UserScore 1,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,1,6)

    exec UserScore 1,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,1,10)

    exec UserScore 2,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,2,4)

    exec UserScore 1,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,2,5)

    exec UserScore 1,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,2,9)

    exec UserScore 2,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,2,1)

    exec UserScore 2,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,3,5)

    exec UserScore 1,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,3,5)

    exec UserScore 1,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,3,9)

    exec UserScore 2,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,3,0)

    exec UserScore 2,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,4,3)

    exec UserScore 1,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,4,0)

    exec UserScore 1,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,4,6)

    exec UserScore 2,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,4,2)

    exec UserScore 2,1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,5,10)

    exec UserScore 1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,5,3)

    exec UserScore 2

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,5,7)

    exec UserScore 2

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,6,10)

    exec UserScore 1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,6,10)

    exec UserScore 2

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,7,4)

    exec UserScore 1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,7,5)

    exec UserScore 1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,7,9)

    exec UserScore 2

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,7,1)

    exec UserScore 2

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,8,5)

    exec UserScore 1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,8,5)

    exec UserScore 1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,8,9)

    exec UserScore 2

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,8,0)

    exec UserScore 2

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,9,3)

    exec UserScore 1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,9,0)

    exec UserScore 1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,9,6)

    exec UserScore 2

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,9,2)

    exec UserScore 2

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,10,10)

    exec UserScore 1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,10,10)

    exec UserScore 1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,10,10)

    exec UserScore 1

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,10,3)

    exec UserScore 2

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,10,7)

    exec UserScore 2

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,10,10)

    exec UserScore 2

    -- 2) A perfect game:

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,1,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,2,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,3,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,4,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,5,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,6,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,7,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,8,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,9,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,10,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,10,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,10,10)

    exec UserScore 3

    -- 3) An extended game (20 frames)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,1,2)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,1,6)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,2,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,3,4)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,3,5)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,4,9)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,4,1)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,5,5)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,5,5)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,6,9)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,6,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,7,3)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,7,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,8,6)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,8,2)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,9,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,10,3)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,10,7)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,11,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,12,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,13,4)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,13,5)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,14,9)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,14,1)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,15,5)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,15,5)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,16,9)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,16,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,17,3)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,17,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,18,6)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,18,2)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,19,10)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,20,3)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,20,7)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,20,10)

    exec UserScore 4

    -- the EndOfArray test

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,1,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,2,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,3,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,4,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,5,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,6,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,7,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,8,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,9,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,1,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,2,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,3,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,4,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,5,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,6,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,7,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,8,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,9,0)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,10,2)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,10,8)

    Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,10,10)

    exec UserScore 5

     

     


    Salvor

  • GaryMcAllister

    SSC-Addicted

    Points: 411

    temp tables... boo hoo.

  • Salvor

    Ten Centuries

    Points: 1189

    It's not a temp table, it's a table variable


    Salvor

  • GaryMcAllister

    SSC-Addicted

    Points: 411

    Sorry its a in-memory temp table until it get too big, then its stored in the temp database.

    He he..

    Looked at yours liked the way that you have made the game length flexible.. It would be quite simple to add that to mine.. Just gotta change some hard coded variables.

  • Salvor

    Ten Centuries

    Points: 1189

    you're right, still it's conceptually different, e.g. table variables don't get involved in transactions.

     


    Salvor

  • bwalker

    SSC Veteran

    Points: 286

    It appears that some people tend to single out one small aspect of a T-SQL solution, an aspect they personally dislike, and use that to justify dismissal of the entire solution.  Loops and temporary tables have been singled out in this topic, but I see these things (and many others) being singled out in other topics and in other forums.  In most cases, the opinion being expressed makes it sound like the aspect being singled out is the most terrible coding construct one could ever implement.  The same posts usually contain claims that the problem can be solved without using the coding construct being criticized.  It seems like there is a masochistic glee to implementing solutions using massive and convoluted SELECT statements.

    Unlike scoring rules for bowling, business requirements tend to change.  When they do change any T-SQL code that implements them has to change as well.  Which solution proposed in this topic would you prefer to maintain?  The solution proposed by DBAdmin includes both a loop and a temporary table.  Does that make the solution bad?  Does that make the solution less efficient?  I doubt it, but I have not done any testing.  It looks a lot shorter and cleaner to me and I would choose to maintain that one if the performance characteristics are comparable to the others.

    I'm not advocating the use of loops, temporary tables, cursors, dynamic SQL, or any other coding construct that gets portrayed as a scourge to T-SQL programming.  I think all of these coding constructs, as well as complex SELECT statements, can be used appropriately with great success or inappropriately with dismal results.  I think it's silly and sheepish to criticize the coding constructs themselves while ignoring the context.  I think the quality of any particular solution is determined by how the coding constructs are applied.

     

  • GaryMcAllister

    SSC-Addicted

    Points: 411

    This is a friendly community.

    If your willing to post a solution you must be willing to take critism.

    I never claimed that my solution was in fact 'THE BEST'.. Ive always taken the view personally why create temp tables and loops when a solution can be acheived without doing so.

    In my opinion.. SELECT statements and FUNCTIONS are the lowest form of T-SQL for a DBA to administer.. If a DBA cannot under-stand or reverse engineer Functions or SELECT statements then I do believe it is time to hang up your hat and mosey on home.

    Well commented / Documented T-SQL is never hard to maintain.

     

  • bwalker

    SSC Veteran

    Points: 286

    "If your willing to post a solution you must be willing to take critism."

    I assume your statement applies to everybody.

    I finally took the time to test your solution and the solution posted by DBAdmin.  I started with the original table.  I added GameID to the table and to the solution posted by DBAdmin, in order to accommodate your solution.  I added a temporary table to the solution posted by DBAdmin to make it return a result set like your solution.  I used the sample data supplied by DBAdmin.  The two solutions produced the same results.  I did not check for accuracy of the results.  I assumed both solutions do the scoring correctly.

    I tested each solution independently.  I used identical code to make each solution score the sample data five times.  I ran the code repeatedly to get a range of execution times.  Here are the results:

    Your solution took 7.55 to 7.65 seconds.

    The DBAdmin solution took 0.06 to 0.07 seconds.

    Are you willing to endure a 100+ times slower execution in order to avoid temp tables and loops?

    I hope somebody else repeats these tests to verify my findings.

     

  • GaryMcAllister

    SSC-Addicted

    Points: 411

    To be honest.. I don't give a flying poop.. Temp tables suck.

    I presume you done some work with execution plans / indexes to make your findings fair ?

    Or did you run the first sql statment on a duel zeon box and mine on a 386 ?

  • bwalker

    SSC Veteran

    Points: 286

    All tests were performed on the same server, in the same database, using the same table of sample data.

    I would be happy to repeat the tests using any index(es) you think would help on a table with 21 narrow rows.  I strongly suspect that the performance gap would widen as the row count increases.

    I did not expect a reasoned, or even rational, reply from you.  Your reply was neither.  It's typical that those who engage in venomous criticism (as you have) react badly when they find their criticism proven very unfounded.  It's typical that those who proclaim broad generalities (as you have) react badly when they find their beliefs and assumptions proven very misguided.

     

  • Adam Machanic

    SSCoach

    Points: 15259

    Why do temp tables "suck"?

    Purely set-based solutions are not always the best in terms of performance. There is a time and place for everything -- even cursors have some utility. Don't drop something from your tool kit just because certain gurus want you to think you shouldn't use it.

    --
    Adam Machanic
    whoisactive

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply