Need to use Remain value in calculation

  • luissantos

    SSCertifiable

    Points: 7359

    Hello community,

    I need to performe a calculation by Row using remain values for the same Receipt_nr

    --This is a Temp table for test purpose
    CREATE TABLE #temptab (docdata DATE, Invoice VARCHAR(29), nrInvoice INT, totalinvoice NUMERIC(14,2), totalreceived NUMERIC(14,2),
    Receipt_nr INT, TotalPaymetforInvoice NUMERIC(14,2), remain NUMERIC(14,2) )

    INSERT INTO #temptab (docdata, Invoice, nrInvoice, totalinvoice, totalreceived, Receipt_nr, TotalPaymetforInvoice, remain)

    SELECT CONVERT(DATETIME,'2019-03-07',120) , 'Fatura DR' , 2684, 1188.60, 2162.14, 20190119, 1188.60, 973.54
    UNION
    SELECT CONVERT(DATETIME,'2019-03-13',120) , 'Fatura DR' , 3489, 1649.74, 0.00, 20190119, 0.00 , 0.00
    UNION
    SELECT CONVERT(DATETIME,'2019-03-29',120) , 'Fatura DR' , 3627, 1415.91, 0.00, 20190119, 0.00, 0.00
    UNION
    SELECT CONVERT(DATETIME,'2019-05-13',120) , 'Fatura DR' , 5447, 1672.49, 476.89, 20190192, 476.89, 0.00
    UNION
    SELECT CONVERT(DATETIME,'2019-05-20',120) , 'Fatura DR' , 5799, 1415.91, 0.00, 20190192, 0.00, 0.00
    UNION
    SELECT CONVERT(DATETIME,'2019-05-24',120) , 'Fatura DR' , 6116, 1672.49, 0.00, 20190192, 0.00, 0.00
    UNION
    SELECT CONVERT(DATETIME,'2019-06-03',120) , 'Fatura DR' , 6459, 1415.91, 0.00, 20190235, 0.00, 0.00
    UNION
    SELECT CONVERT(DATETIME,'2019-06-05',120) , 'Fatura DR' , 6644, 42.83, 0.00, 20190235, 0.00, 0.00
    UNION
    SELECT CONVERT(DATETIME,'2019-06-11',120) , 'Fatura DR' , 6841, 1661.12, 0.00, 20190235, 0.00, 0.00
    UNION
    SELECT CONVERT(DATETIME,'2019-07-01',120) , 'Fatura DR' , 7600, 1672.49, 0.00, 20190272, 0.00, 0.00

    The Result i need is :

    If  Totalreceived > totalinvoice then the field TotalPaymetforInvoice must be equal to [ totalInvoice] and the remain value is equal to

    (Totalreceived - totalinvoice). It´s Ok for the first line, but for the second line, and because i have a remain value of 973.54, the calculation must be :

    If  (Remain + Totalreceived ) < TotalInvoice then the value of field TotalpaymentforInvoice must me the remain value 973.54, and so on.

    All of this for each Receipt_nr.

    someone could help me.

    Many thanks,

    Luis

     

     

     

     

     

     

  • scdecade

    Say Hey Kid

    Points: 693

    Does this produce the correct remainder column?

    select 
    t.*,
    sum(t.totalreceived-t.totalinvoice) over (partition by receipt_nr order by docdata) remainder
    from
    #temptab t

    • This reply was modified 3 months, 3 weeks ago by  scdecade.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • luissantos

    SSCertifiable

    Points: 7359

    Hello Scdecade,

    this is work, but this is not the problem that i need to solve.

    in pratice, if on first line i have a remain value, then when i calculate the second line i need to consider the remain value.

    Then if :

    (t.totalreceived + remain) -t.totalinvoice is greather than t.totalinvoice then i must consider t.totalreceived = t.totalinvoice else

    if ( remain + t.totalreceived )  is less than t.totalinvoice  then t.totalreceived = ( remain + t.totalreceived ) otherwise if

    ( remain + t.totalreceived )  is greather than t.totalinvoice  then t.totalreceived = t.totalinvoice... and so on .

    Obviously Partionning by Receipt_nr.

    See Please the result that i need :

    result

    Many thanks,

    Luis

    Have you an ideia ?

     

  • scdecade

    Say Hey Kid

    Points: 693

    select 
    t.*,
    iif((t.totalinvoice+sum(t.totalreceived-t.totalinvoice) over (partition by receipt_nr order by docdata))<0,0,
    iif((t.totalinvoice+sum(t.totalreceived-t.totalinvoice) over (partition by receipt_nr order by docdata))>t.totalinvoice,
    t.totalinvoice, (t.totalinvoice+sum(t.totalreceived-t.totalinvoice) over (partition by receipt_nr order by docdata)))) tot_pay_inv,
    sum(t.totalreceived-t.totalinvoice) over (partition by receipt_nr order by docdata) remainder
    from
    #temptab t

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • scdecade

    Say Hey Kid

    Points: 693

    This should come with a warning though.  If there are repeated 'docdata' dates within a partitioning window, then this code will not produce correct results.  If it's possible to implement a unique constraint on (Receipt_nr, docdata) then this code works.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • luissantos

    SSCertifiable

    Points: 7359

    Hello Scdecade,

    Many thanks for your reply and also the last consideration that you have post.

    I understand your concept and the same work like great.

    Many thanks,

    Best regards,

    Luis

     

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply