vvanasperen
Gebruiker
- Lid geworden
- 9 mrt 2009
- Berichten
- 68
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 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
Code:
rownumber_all | rownumber_klant | klant | transactie datum | bedrag | transactie type
1 1 123 2018-10-12 10 storting
2 2 123 2018-10-27 5 cashback
3 3 123 2018-11-03 -4 uitgave
4 4 123 2018-11-13 -5 uitgave
5 5 123 2018-12-18 10 storting
6 6 123 2018-12-19 20 storting
7 7 123 2018-12-21 5 cashback
8 1 456 2018-10-11 -40 uitgave
9 2 456 2018-10-23 5 cashback
10 3 456 2018-11-01 5 cashback
11 4 456 2018-11-04 10 storting
Etc.
*saldo tabel*
Code:
rownumber_klant | klant | overall saldo | cashback saldo
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.
Code:
select
t1.rownumall,
t1.rownumclient,
t1.client_id,
t1.transaction_date,
t1.amount,
t1.type,
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.rownumclient = t1.rownumclient-1
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
Code:
klant | transaction datum | bedrag | transactie type | overall saldo | cashback saldo
123 2018-10-12 10 storting 10 0
123 2018-10-27 5 cashback 15 5
123 2018-11-03 -2,5 uitgave 12,5 2,5
123 2018-11-13 -5 uitgave 7,5 0
123 2018-12-18 10 storting 17,5 0
123 2018-12-19 20 storting 37,5 0
123 2018-12-21 5 cashback 42,5 5
456 2018-10-11 -45 uitgave -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 storting 17,5 17,5
Etc.
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
Laatst bewerkt: