-1

I have table with a amount column which has both negative and positive values.I want to calculate the sum of values and set negative cumulative sum to zero.

date        amount  actu_cusum  exp_cusum
22-06-2018  60.626   60.626      60.626
29-06-2018  -78.309 -17.683       0
02-07-2018  -0.824  -18.507       0
09-07-2018  -0.822  -19.329       0
10-07-2018  14.79   -4.539       14.79
29-07-2018  20      15.461       34.79
30-07-2018  -30     -24.539      4.79
31-07-2018  15      -9.539       19.79


update a set a.calamount= z.cusum
from #temp a, 
( select CustomerCode,  date ,amount, 
SUM(totaumamount) over ( partition by CustomerCode order by date )
as cusum from #temp
) z
where a.customercode = z.customercode and a.date = z.date
Carsten Massmann
  • 26,510
  • 2
  • 22
  • 43
Ashwini
  • 11
  • 2
  • What "SQL" is this? What is your DBMS? "SQL" is just a query language used by many DBMSs. – rory.ap Jul 31 '18 at 12:22
  • Looks very much like SQL-Server. Your column `customercode` seems to be missing in the sample data section. – Carsten Massmann Jul 31 '18 at 12:34
  • Could it be that the challenge is to calculate those expected [exp_cusum] results? If that's so then this question is not as simple as that duplicate question that locked this post. And it should be reopened. – LukStorms Jul 31 '18 at 13:28

1 Answers1

2

(You are missing columns and tables in your question) You can use:

... set a.calamount = case when z.cusum < 0 then 0 else z.cusum end ...

EDIT: For individual negative values:

update a set a.calamount= z.cusum
from #temp a, 
( select CustomerCode,  date ,amount, 
SUM(case when totaumamount < 0 then 0 else totaumamount end) over ( partition by CustomerCode order by date )
as cusum from #temp
) z
where a.customercode = z.customercode and a.date = z.date
Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
  • I have apply this case but result is not matching because cusum contains the previous value result of that case is new result apply case function 60.626 0 0 0 -4.539 15.461 -24.539 -9.539 – Ashwini Aug 01 '18 at 05:45
  • @Ashwini, sorry I don't understand what you mean. Would you give some sample data WITH expected output. Are you trying to set the result to 0 or each individual value? I will edit my answer for individual values too. – Cetin Basoz Aug 01 '18 at 09:36
  • date amount actu_cusum exp_cusum 22-06-2018 60.626 60.626 60.626 29-06-2018 -78.309 -17.683 0 02-07-2018 -0.824 -18.507 0 31-07-2018 15 -3.507 19.79 I want to set negative sum of actu_cusum column to zero and it should pick zero for next record but at this case its pick the negative record so i am not getting the correct output – Ashwini Sep 28 '18 at 06:28