# question

asked

## How to get add negative to next positive number in the column in SQL?

I am having problem trying to get negative value added to next positive number in the column using SQL? I need to write SQL to calculate the New Value using the Value column like the sample on the attachment. Please help

question.jpg (109.7 KiB)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

### 1 Answer

·

Here's a solution that uses a recursive CTE to calculate the values. Performance isn't going to be great on a large dataset.

```declare @yourtable table (tradingdate date, tradinghour int, value int);

insert into @yourtable (tradingdate, tradinghour, value) select '16 march 2019',22,-1;
insert into @yourtable (tradingdate, tradinghour, value) select '16 march 2019',23,-1;
insert into @yourtable (tradingdate, tradinghour, value) select '16 march 2019',24,0;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',1,-1;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',2,-2;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',3,0;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',4,-3;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',5,0;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',6,24;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',7,3;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',8,-2;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',9,-1;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',10,-2;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',11,-2;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',12,2;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',13,1;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',14,2;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',15,8;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',16,45;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',17,55;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',18,103;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',19,137;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',20,107;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',21,113;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',22,115;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',23,97;
insert into @yourtable (tradingdate, tradinghour, value) select '17 march 2019',23,62;

with cte1 as (
select
tradingdate, tradinghour, value,
row_number()over(order by tradingdate, tradinghour) as rn
from @yourtable
),

rcte as(
select top 1
tradingdate, tradinghour,value,rn,
runningtotal = value,
newvalue = case when value < 0 then 0 else value end
from cte1
union all
select
yt.tradingdate, yt.tradinghour,
yt.value,
yt.rn,
case when rcte.runningtotal >= 0 then yt.value else rcte.runningtotal+yt.value end ,
case when (case when rcte.runningtotal >= 0 then yt.value else rcte.runningtotal+yt.value end) <0 then 0
else
case when rcte.runningtotal >= 0 then yt.value else rcte.runningtotal+yt.value end
end
from rcte join cte1 yt on yt.rn= rcte.rn+1
)

select
tradingdate, tradinghour,value, newvalue
from rcte
order by tradingdate, tradinghour

tradingdate tradinghour value       newvalue
----------- ----------- ----------- -----------
2019-03-16  22          -1          0
2019-03-16  23          -1          0
2019-03-16  24          0           0
2019-03-17  1           -1          0
2019-03-17  2           -2          0
2019-03-17  3           0           0
2019-03-17  4           -3          0
2019-03-17  5           0           0
2019-03-17  6           24          16
2019-03-17  7           3           3
2019-03-17  8           -2          0
2019-03-17  9           -1          0
2019-03-17  10          -2          0
2019-03-17  11          -2          0
2019-03-17  12          2           0
2019-03-17  13          1           0
2019-03-17  14          2           0
2019-03-17  15          8           6
2019-03-17  16          45          45
2019-03-17  17          55          55
2019-03-17  18          103         103
2019-03-17  19          137         137
2019-03-17  20          107         107
2019-03-17  21          113         113
2019-03-17  22          115         115
2019-03-17  23          97          97
2019-03-17  23          62          62

(27 rows affected)

```
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·

Thanks, that works perfectly

0 Likes 0 ·

### Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.