sql - Running Total until specific condition is true

Question

I'm having a table representing the dealers cards and their rank. I'm now trying to make a query (as fast as possible) to set status on the game.

(As said before, only the dealer cards is shown)
W = Win
S = Stand
L = Loss
B = Blackjack (in two cards)

About the rules: The dealer wins at 21, if it's in two cards its blackjack. If the rank is between 17 and 20 it's S = stand. Over 21 is a loss.

Ranks:

1 (ACE) - 1 or 11 rank. Counted as 11.

2-10 - 2-10 rank

11-13 (knight - king) - 10 rank

╔════╦══════╦════════╗
║ Id ║ Rank ║ Status ║
╠════╬══════╬════════╣
║  1 ║    1 ║        ║
║  2 ║    5 ║        ║
║  3 ║    8 ║ L      ║  //24 = Loss
║  4 ║    3 ║        ║
║  5 ║    1 ║        ║
║  6 ║    7 ║ W      ║  //21 = Win
║  7 ║   10 ║        ║
║  8 ║    1 ║ B      ║  //21 = Blackjack
║  9 ║   10 ║        ║
╚════╩══════╩════════╝

I've tried to use a counter to check if it's blackjack and then I'm using a "RunningPoint" to check the sum of the cards.

I have now a solution bad it shows very bad performance when it's a lot of data. How would you do this and what can I do to optimize my query? When using more data I also need to use option (maxrecursion 0)

(When having 1 million rows it's not even possible to run this...)

My example: http://sqlfiddle.com/#!6/3855e/1

Answer 1

This solution is based on quirky update. More info here.

LiveDemo

Data and structures:

CREATE TABLE #BlackJack
(
   id INT 
  ,Rank INT
  ,running_total INT
  ,result NVARCHAR(100)
);

CREATE CLUSTERED INDEX IX_ROW_NUM ON #BlackJack(id);

insert into #BlackJack (Id, Rank)
values (1, 1),(2, 5), (3, 8), (4, 3), (5, 1),
       (6, 7), (7, 10), (8, 1),(9, 10), (10, 10), (11,1);

Main query:

DECLARE @running_total       INT = 0
        ,@number_of_cards    INT = 0
        ,@prev_running_total INT = 0;

UPDATE #BlackJack
SET 
   @prev_running_total = @running_total
  ,@running_total = running_total = IIF(@running_total >= 20, 0, @running_total) 
                                    + CHOOSE(Rank,11,2,3,4,5,6,7,8,9,10,10,10,10)
  ,result        = CASE WHEN @running_total = 20 THEN 'S'
                        WHEN @running_total = 21 AND @number_of_cards = 2 THEN 'B'
                        WHEN @running_total = 21 THEN 'W'
                        WHEN @running_total > 21 THEN 'L'
                        ELSE NULL
                    END
  ,@number_of_cards  = IIF(@prev_running_total >= 20, 0, @number_of_cards) + 1
FROM #BlackJack WITH(INDEX(IX_ROW_NUM))
OPTION (MAXDOP 1);

SELECT *
FROM #BlackJack
ORDER BY id;

Warning

If you use SQL Server < 2012 you need to replace IIF and CHOOSE with CASE. I don't check all Blackjack rules, only for provided sample. If something is wrong feel free to change CASE logic.

Second I extend base table BlackJack with auxiliary columns, but you can create any new table, if needed.

The key point is to read data sequentially based on clustered key ascending and do not allow parallel execution. Before you use it in production check how it behaves with large data set.