Helpmij.nl
Helpmij.nl
Helpmij.nl
Steun Helpmij.nl! Klik hier     Computerprobleem? Klik hier!

Quote

Weergeven resultaten 1 tot 3 van 3

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

  1. #1
    Member vvanasperen's avatar
    Geregistreerd
    9 maart 2009
    Locatie
    Zwolle
    Afstand tot server
    ±32 km
    Vraag is opgelost

    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
    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 aangepast door vvanasperen : 13 januari 2019 om 22:09

  2. #2
    Mega Senior
    Geregistreerd
    22 mei 2007
    Locatie
    BE
    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)
    [statement not part of message]
    IT are one and zero's , Be the one. bytheway your not the only one. Now One by, One we walked that road.You not the one of the zero's if as you understanding me at the one times.
    Als je het koud krijgt moet je windows sluiten.

  3. #3
    Member vvanasperen's avatar
    Geregistreerd
    9 maart 2009
    Locatie
    Zwolle
    Afstand tot server
    ±32 km
    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

  4. Dit topic is automatisch gesloten omdat er sinds vier maanden niet meer op gereageerd is.

    Indien gewenst kan de topicstarter een verzoek tot heropening indienen.

Berichtenregels

  • U mag geen nieuwe vragen starten.
  • U mag niet reageren op berichten.
  • U mag geen bijlagen versturen.
  • U mag uw berichten niet bewerken.
  •  
Helpmij.nl
Helpmij.nl

Helpmij.nl en business

Partners
Sponsoren