SQL Server: Rijen per stuk langsgaan, berekening doen en resultaat wegschrijven

Het probleem:
Ik heb een tabel "transacties" waarin per klant stortingen, uitgaven en cashbacks worden vastgelegd. Cashbacks zijn een soort korting die je op toekomstige uitgaven kunt gebruiken of die een negatief saldo verkleinen. De velden zijn klant, transactie datum, bedrag en transactie type.

Ik wil per rij uit de transactie tabel een soort "cashback saldo" veld toevoegen, maar dat wil me maar niet lukken.

Mijn collega's en ik hebben het geprobeerd met de lag functie, maar die kijkt maar naar één specifieke rij terug. En soms zitten er meerdere storting en/of uitgave rijen tussen cashbacks en dan werkt het dus niet.

Je hebt een totaal saldo per rij en als die negatief is dan kan een cashback daar tegen wegvallen. Verder worden nieuwe cashback bedragen erbij opgeteld en als er een casback saldo is, dan worden daar uitgaven van betaald. Het cashback saldo kan nooit negatief zijn. Het is of 0 of er is een saldo.

Ik denk dat we op zoek zijn naar een code die door de rijen kan loopen.

Het idee:
Het idee is om aan de "transactie" tabel twee kolommen toe te voegen. Een rownumber voor alle rijen (rownum_all) en een rownumber voor de rijen per klant (rownum_klant). Daarnaast maken we dan een lege tabel aan die we "saldo" noemen en waar we de velden rownumber_klant, klant en cashback saldo in hebben staan.

Rownumbers
row_number () over (order by klant, transaction_date) as rownumber_all
row_number () over (partition by client_id order by client_id, transaction_date) as rownum_klant

transactie tabel

*saldo tabel*

En nu moet ik dus iets van een loop (of een foreach of een cursor?) gaan gebruiken die de rijen uit de "transacie" tabel stuk voor stuk langs gaat, het cashback saldo voor die rij berekenen en dan data wegschrijven naar de "saldo" tabel. Deze "saldo" tabel gebruiken we ook in de berekening, bij de eerste rij staat er alleen nog niets in (maar vanaf rij twee dus wel). En dat voor alle rownumber_all startend met de eerste.

In SQL Server code is de berekening van het cashback saldo dan iets als onderstaande.


Het gaat dus om het saldo van cashbacks en niet het saldo van alle transacties, maar deze is wel nodig om te bekijken of er een negatief overall saldo is. Bij de berekening zijn dus het vorige berekende cashback saldo, het overall saldo en het transactie type en bedrag van de huidige rij van invloed op het nieuwe saldo. Dit saldo wordt dan weer insert into de "saldo" tabel. En door het rownumber_klant veld en klant veld kunnen we de berekende saldo's aan de transactie tabel joinen.

Verwachte resultaat:

verrijkte transactie tabel

Ik kan me niet voorstellen dat er niet iemand is die dit al eens eerder bij de hand heeft gehad. Denk dat het idee wel goed kan zijn, maar heb dus geen idee hoe ik dit moet aanpakken. Wie weet dat wel of kan me in de goede richting wijzen. Echt alle hulp, hoe groot of klein ook, is van harte welkom.

Met vriendelijke groet,

Vincent

Ik denk niet dat je het ovalsaldo over kan laten aan de db maar in je aplicatie.

dus overall saldo laat je in een transactie verlopen met een voorwaarde dat je het bedrag erbij kan tellen of aftrekken met dan COMMIT en ROLBACK
je berekend dit en zal dan naar de credietstatus kijken (mag wel of niet onder nul en het is wel of niet onder nul ) (dan zit je nog met is gevat in hypo lening wel of niet en kan mag de transactie voltooien ??)

je zou het ook met trigger kunnen laten doen maar (ik zou dit niet doen vanwege de sectorgebonden juistheid en de berekening niet opnieuw mag gebeuren bij terugzetten van backup ect aanpassingen correctie die dan bijkomende schade veroorzaken)

Heb het gevonden! Met wat aangemaakte tabellen en onderstaande code heb ik het voor elkaar gekregen.

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