Go through each row of a table, do a calculation on that row, insert in temp table and use temp table for next row

Question

Problem:
We have a transaction table. All records in that table have one of the following transaction types: wallet deposit (payments), wallet withdrawal (sales) and cashback (discount to be used for future sales). I want to add a additional column to each row displaying the cashback balance. Cashback is either used for discount on new sales or to reduce negative overall balance.

transaction table:

customer (int)
transaction_date (date)
amount (int)
transaction_type (varchar(25))

I've tried using the lag function to get the value of the previous row and use that for the calculation in the current row. But that doesn't always work because the lag function looks back to the row it's specificly pointed to.

using lag function in calculation:

case when
isnull(lag(balance_cashback) over (partition by client_id order by transaction_date), 0)
+ case when type = "cashback" then amount else 0 end 
+ case when type = "revenu"  and amount < 0 then amount else 0 end 
<= 0 then 0
else
lag(balance_cashback) over (partition by client_id order by transaction_date)
+ case when type = "cashback" then amount else 0 end 
+ case when type = "revenu"  and amount < 0 then amount else 0 end 
end

Searching the internet I think I should be using a loop or maybe a cursor?

Idea:
The idea is to use the transaction table and add two rownumber columns. A rownumber for all rows in the transaction table I want to loop through. And a second rownumber on all transactions of each clients. The next step seems to create an empty balance table with fields rownumclient, client_id, overall_balance and cashback_balance.

calculation of rownumber colums:

row_number () over (order by client_id, transaction_date) as rownumber_all
row_number () over (partition by client_id order by client_id, transaction_date) as rownumber_client

transaction table with rownumbers:

rownumber_all (int)
rownumber_client (int)
client (int)
transaction_date (date)
amount (int)
transaction_type (varchar(25))

balance table:

rownumber_client (int)
client_id (int)
overall_balance (int)
cashback_balance (int)

example transaction table with rownumbers:

rownumbwr_all | rownumber_client | client_id | transaction_date | amount | transaction_type  
1           1              123         2018-10-12         10       wallet deposit  
2           2              123         2018-10-27         5        cashback  
3           3              123         2018-11-03         -2,5     wallet withdrawal  
4           4              123         2018-11-13         -5       wallet withdrawal  
5           5              123         2018-12-18         10       wallet deposit  
6           6              123         2018-12-19         20       wallet deposit  
7           7              123         2018-12-21         5        cashback  
8           1              456         2018-10-11         -45      wallet withdrawal  
9           2              456         2018-10-23         5        cashback  
10          3              456         2018-11-01         5        cashback  
11          4              456         2018-11-04         10       wallet deposit  
Etc.  

With the additional rownumber columns and new balance table in place, I have to create a loop through all rows in the transaction table. Using the column rownumber_all to start with the first one. The newly created balance table is used to calculate the cashback balance in the current row. We use this table with a left join to the transaction tabel with the rownumber columns. When we loop through the first row, the balance table is empty, but from the second row on there is a calculated cashback balance from the previous row.

select statement for calculating current cashback balance:

select  
 t1.rownumall,
 t1.rownumclient,
 t1.client_id,
 t2.overall_balance + t1.amount as overall_balance,
 case
 when (t2.overall_balance + case when t1.type = 'cashback' then t1.amount else 0 end) < 0 then 0
 when t1.type in (sales, cashback) then amount 
 else null 
 end + t2.cashback_balance as cashback_balance
/*insert into balance*/
from
 transactions as t1
 left join cashback as t2 on t2.client_id = t1.client_id and t2.rownumber_client = t1.rownumber_client-1

For each row that is looped through the result of the select statement above should be inserted into the balance table as long as there are transaction records available. And as said before cashback balance is either used for discount on new sales or to reduce negative overall balance. That said the expected result I'm looking for is as follows and cashback_balance is the most important field.

expected transaction table with balances:

client_id | transaction_date | amount | transaction_type | overall_balance | cashback balance  
123         2018-10-12         10       wallet deposit        10                0 
123         2018-10-27         5        cashback              15                5
123         2018-11-03         -2,5     wallet withdrawal     12,5              2,5
123         2018-11-13         -5       wallet withdrawal     7,5               0
123         2018-12-18         10       wallet deposit        17,5              0
123         2018-12-19         20       wallet deposit        37,5              0
123         2018-12-21         5        cashback              42,5              5
456         2018-10-11         -45      wallet withdrawal     -2,5              0
456         2018-10-23         5        cashback              2,5               2,5
456         2018-11-01         5        cashback              7,5               7,5
456         2018-11-04         10       wallet deposit        17,5              7,5
Etc.  

I tried to explain as much as possible and hope the idea and expected result is clear. I can't imagine that what I need hasn't been done before, but I just can't seem to find the specific use case anywhere.

So which SQL expert will be kind enough to tell me in plain english how this can be achieved bu using a loop, cursor or any other way? Any help would be highly appreciated. If any clarification is needed, please let me know.

Answer 1

Are you looking for a running total like a bank statement. This query does that

SELECT
    client_id,
    Transaction_Date,
    Trans_Type,
    Amount,
    Balance = SUM(CASE WHEN Trans_Type = 'Cash back' Then Amount ELSE 0 END) OVER(ORDER BY RowNumber)

FROM
(
    SELECT 
        client_id,
        Transaction_Date,
        Trans_Type,
        Amount,
        ROW_NUMBER() OVER(ORDER BY Client_id) AS RowN

    FROM temp.dbo.Trans_Table) RC

    GROUP BY client_id, Trans_Type, Transaction_Date, Amount, RowN

Sample Data

Answer 2

After searching and some trial and error I've found a way to loop through all the rows and calculate the correct cashback balance for each row. I want to thank all the people who tried to help me and Jorge E. Hernández for the solution to my "loop problem".

Here the final code I used.

-- declare the start and end variable
declare
    @counter int = 1,
    @max_rownumber int = (select max(rownumber_all) as max_rownumber from dbo.transactions)

-- loop 
while @counter <= @max_rownumber
begin

-- calculate overall_balance and cashback_balance for each row in the transactions table filtered by the rownumber_all field
insert into dbo.transactions_enriched
select  
    t1.rownumber_client as rownumber
  , t1.client_id
  , t1.transaction_date
  , t1.amount
  , t1.transaction_type
  , t1.payment_method
  , isnull(t2.overall_balance ,0) + t1.amount as overall_balance
  , case 
    when t1.transaction_type = 'cashback' and isnull(t2.overall_balance, 0) >= 0 then isnull(t2.cashback_balance, 0) + t1.amount
    when (case when t1.transaction_type = 'revenue' and t1.amount < 0 then t1.amount else 0 end) + isnull(t2.cashback_balance, 0) <= 0 then 0
    else (case when t1.transaction_type = 'revenue' and t1.amount < 0 then t1.amount else 0 end) + isnull(t2.cashback_balance, 0)
    end as cashback_balance
from
    dbo.transactions as t1
    left join dbo.transactions_enriched as t2 on t2.client_id = t1.client_id and t2.rownumber_client = t1.rownumber_client - 1
where 
    t1.rownumber_all = @counter

-- update the counter by adding 1
set @counter = @counter + 1

end