December 3, 2019 at 11:00 pm
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
December 4, 2019 at 3:12 am
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
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 4, 2019 at 11:12 am
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 :
Many thanks,
Luis
Have you an ideia ?
December 4, 2019 at 1:51 pm
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
December 4, 2019 at 2:43 pm
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
December 4, 2019 at 5:41 pm
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