Running Total Until Specific Condition Is True

Running Total until specific condition is true

There's no efficient solution using plain SQL (including Windowed Aggregate Functons), at least nobody found one, yet :-)

Your recursive query performs bad because it's way too complicated, this is a simplified version:

Edit: Fixed the calculation (Fiddle)

WITH ctePoints AS
 (
   SELECT 1 AS id
        ,rank
        ,CASE 
           WHEN rank >= 10 THEN 10
           WHEN rank = 1 THEN 11
           ELSE rank
         END AS Point
        ,1 AS Counter
   FROM dbo.BlackJack
   WHERE Id = 1

   UNION ALL

   SELECT t2.Id
        ,t2.rank
        ,CASE WHEN t1.Point < 17 THEN t1.Point ELSE 0 END 
         + CASE 
             WHEN t2.rank >= 10 THEN 10
             WHEN t2.rank = 1 THEN 11
             ELSE t2.rank
           END AS Point
        ,CASE WHEN t1.Point < 17 THEN t1.Counter + 1 ELSE 1 END AS Counter
   FROM dbo.BlackJack AS t2
   INNER JOIN ctePoints AS t1 ON t2.Id = t1.Id + 1
 ) 
SELECT ctepoints.*
     ,CASE 
        WHEN Point < 17 THEN ''
        WHEN Point < 20 THEN 'S'
        WHEN Point > 21 THEN 'L'
        WHEN Point = 21 AND Counter = 2 THEN 'B'
        ELSE 'W' 
      END AS DealerStatus            
FROM ctePoints

It's probably still too slow, because it processes row by row.

I usually use recursive SQL to replace cursor logic (because in my DBMS it's usually much faster) but a cursor update might actually be faster (Demo):

CREATE TABLE #BlackJack
(
   id INT PRIMARY KEY CLUSTERED
  ,Rank INT
  ,DealerStatus CHAR(1)
);

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);

DECLARE @Counter INT = 0
        ,@Point INT = 0
        ,@id int
        ,@Rank int
        ,@DealerStatus char(1)

DECLARE c CURSOR
FOR
SELECT id, Rank
FROM #BlackJack 
ORDER BY id FOR UPDATE OF DealerStatus

OPEN c

FETCH NEXT FROM c INTO @id, @Rank

WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @counter = @counter + 1

    SET @Rank = CASE
                  WHEN @Rank >= 10 THEN 10
                  WHEN @Rank  = 1  THEN  11
                  ELSE @Rank
                END 

    SET @Point = @Point + @Rank

    SET @DealerStatus = CASE 
                          WHEN @Point < 17 THEN ''
                          WHEN @Point < 20 THEN 'S'
                          WHEN @Point > 21 THEN 'L'
                          WHEN @Point = 21 AND @Counter = 2 THEN 'B'
                          ELSE 'W' 
                        END 

    IF @Point >= 17 
    BEGIN
      UPDATE  #BlackJack 
      SET DealerStatus = @DealerStatus
      WHERE CURRENT OF c;

      SET @Point = 0

      SET @Counter = 0
    END

    FETCH NEXT FROM c INTO @id, @Rank
  END

CLOSE c
DEALLOCATE c

SELECT * FROM #BlackJack ORDER BY id

Still @lad2025's "quirky update" is the fastest way to get the expected result, but it's using an undocumented feature and if a Service Pack breaks it there's no way to complain about it :-)

How to keep a running total column but stop by condition

You can use this predicate.

where Name = 'Foo' 
      and QtyRunningTotal - Quantity < @neededQty 

Conditional Running Sum in Pandas for All Previous Values Only

Finally, I can find a better and faster way to get the desired result. It turns out that it is very easy. One can try:

df['Total_Previous_Sale'] = df.groupby('Event')['Sale'].cumsum() \
                          - df.groupby(['Event', 'Date'])['Sale'].cumsum()

Rolling Cummulative Sum of a Column's Values Until A Condition is Met

You could try using this for loop:

lastvalue = 0
newcum = []
for i in df['a']:
    if lastvalue >= 5:
        lastvalue = i
    else:
        lastvalue += i
    newcum.append(lastvalue)
df['a_cum_sum'] = newcum
print(df)

Output:

   a  a_cum_sum
0  2          2
1  3          5
2  0          0
3  5          5
4  1          1
5  3          4
6  1          5
7  2          2
8  2          4
9  1          5

The above for loop iterates through the a column, and when the cumulative sum is 5 or more, it resets it to 0 then adds the a column's value i, but if the cumulative sum is lower than 5, it just adds the a column's value i (the iterator).

Rolling sum till a certain value is reached, plus calculated duration

A way to solve this efficiently is a procedural solution with two cursors:
One explicit cursor and another implicit cursor of the FOR loop:

CREATE OR REPLACE FUNCTION foo()
  RETURNS TABLE (dt timestamp
               , val real
               , sum_value real
               , time_at_sum timestamp
               , duration interval) AS
$func$
DECLARE
   _bound real := 1.0;          -- your bound here
   cur CURSOR FOR SELECT * FROM sample s ORDER BY s.dt; -- in chronological order
   s sample;                    -- cursor row 
BEGIN
   OPEN cur;
   FETCH cur INTO time_at_sum, sum_value; -- fetch first row into target

   FOR dt, val IN  -- primary pass over table
      SELECT x.dt, x.value FROM sample x ORDER BY s.dt
   LOOP
      WHILE sum_value <= _bound LOOP
         FETCH cur INTO s;
         IF NOT FOUND THEN  -- end of table
            sum_value := NULL; time_at_sum := NULL;
            EXIT;           -- exits inner loop
         END IF;
         sum_value := sum_value + s.value; 
      END LOOP;
      IF sum_value > _bound THEN  -- to catch end-of-table
         time_at_sum := s.dt;
      END IF;   
      duration := time_at_sum - dt;
      RETURN NEXT;
      sum_value := sum_value - val;  -- subtract previous row before moving on
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM foo();

db<>fiddle here

Should perform nicely since it only needs 2 scans over the table.

Note that I implemented > _bound like your description requires, not >= _bound like your result indicates. Easy to change either way.

Assumes the value column to be NOT NULL.

Related:

• Window Functions or Common Table Expressions: count previous rows within range

reset a running total in same partition based on a condition

Do a running total on ResetSum in a derived table and use that as a partition column in the running total on Amount.

select T.PersonID,
       T.Amount,
       T.PayDate,
       sum(T.Amount) over(partition by T.PersonID, T.ResetSum 
                          order by T.PayDate rows unbounded preceding) as SumAmount
from (
     select T1.PersonID,
            T1.Amount,
            T1.PayDate,
            sum(case T1.ResetSum 
                  when 1 then 1 
                  else 0 
                end) over(partition by T1.PersonID 
                          order by T1.PayDate rows unbounded preceding) as ResetSum
     from dbo.Table_1 as T1
     ) as T;

SQL Fiddle

---

Related Topics