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.
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