Need to use Remain value in calculation

Question

Post reply

Need to use Remain value in calculation

luissantos

SSCertifiable

Points: 7391

December 3, 2019 at 11:00 pm

#3704034

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

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

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

Steve Collins SSCrazy Points: 2069 More actions · Answer 1

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 4 years, 4 months ago by Steve Collins.

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

luissantos SSCertifiable Points: 7391 More actions · Answer 2

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 ?

Steve Collins SSCrazy Points: 2069 More actions · Answer 3

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

Steve Collins SSCrazy Points: 2069 More actions · Answer 4

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: 7391 More actions · Answer 5

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