question

ltang avatar image
ltang 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

sql-server-2012
question.jpg (109.7 KiB)
10 |1200

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

1 Answer

·
Kev Riley avatar image
Kev Riley answered

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
10 |1200

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

ltang avatar image ltang commented ·

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.