I have a problem that I don't know how to fix .. here is the code and wanting result
if object_id('tempdb..#A') IS NOT NULL drop table #A
create table #A (ID int, Value decimal(6,2), value2 decimal(6,2), Result decimal(6,2))
insert into #A (ID, Value, value2, Result)
values
(1, 10, 25, null),
(1, 10, 25, null),
(1, 10, 25, null),
(2, 10, 5, null),
(2, 10, 5, null),
select * from #A
So, I would like to take Value away from "value2", if there are left overs, just update it to 0, for next row i would take those "left overs" and use them to take away from, with next Value
I would like to get results like this...
ID Value value2 Result
1 10 25 0
----------------------------
1 10 25 0
----------------------------
1 10 25 5
----------------------------
2 10 5 5
----------------------------
2 10 5 10
So as you can see with ID 1 ... it would be:
10 - 25 = 0
10 - 15 = 0
10 - 5 = 5
I hope you understand what I am trying to do here ... let me know if I can explain more ...
You seem to want the cumulative sum of the difference, with no negative values allowed. Most databases support window functions, which include cumulative sums:
I am going to assume that id really specifies the ordering. You need some column that serves this purpose because SQL tables represent unordered sets and have no ordering.
But, something like this should work:
select a.*,
sum(case when value2 >= value then 0 else value - value2 end) over
(order by id) as result -- or whatever the column is that specifies the ordering
from #A a;
With help of Gordon and using some part of his idea ... i did something, that at this moment seems to work, but will need a lot of more testing
if object_id('tempdb..#testDataWithRunningTotal') IS NOT NULL drop table #testDataWithRunningTotal
select id, value, value2, cast(null as float) as Result
into #testDataWithRunningTotal
from #A order by id;
declare @runningTotal float = 0, @previousParentId int = null;
update #testDataWithRunningTotal
set
@runningTotal = Result = case when @previousParentId <> id
then value2 - value
else
case when ISNULL(@runningTotal,0) < 0 then value * (-1)
when value2 - value < 0 and ISNULL(@runningTotal,0) = 0 then value2
when value2 - value > 0 and ISNULL(@runningTotal,0) = 0 then value2 - value
else
case when @runningTotal - value < 0 and ISNULL(@runningTotal,0) = 0 then value
else @runningTotal - value
end
end
end,
@previousParentId = id
from #testDataWithRunningTotal
update tst
set Result = case when Result > 0
then 0
else Result * -1
end
from #testDataWithRunningTotal tst
select * from #testDataWithRunningTotal
So, I am keeping @runningTotal running with update, and allowing it to go under 0 ... once it goes less then 0 it means that is moment where SUM of value is greater then SUM of Value2 ... so i keep the record there, and at end of this calculation i do update.
