0

I have the following SQL table:

StockID Flow Stock 1 + 2 1 - 3 1 + 8

I have another table:

ID InStock 1 22 2 51 3 92

I want to select the first value of the second table 22 and add or subtract it cumulatively from the values Stock of the first table with the StockID = ID, based on the sign in the flow column. Then add/subtract the values in the column Stock from this solution.

This should appear in a new column Sol:

ID Flow Stock Sol 1 + 2 24 (22+2) 2 - 3 21 (24-3) 3 + 8 29 (21+8)

Do you have any ideas?

Improve this question
7
  • Check out this post stackoverflow.com/questions/2120544/how-to-get-cumulative-sum Commented Aug 8, 2018 at 16:33
  • 1
    Where's the common column??? Without a common column, you can't join the 2 tables. Commented Aug 8, 2018 at 16:33
  • 1
    You have simplified this so much it isn't answerable. You need something to use for ordering. You need something to join on. But in the end this is just a running total made a little bit odd by the flow column. But that is trivial to deal with. Just requires a few silly string manipulations to turn your string into a usable formula. Commented Aug 8, 2018 at 16:42
  • Added common row. Commented Aug 8, 2018 at 16:43
  • still isn't enough for even a recursive cte i don't think Commented Aug 8, 2018 at 16:48

2 Answers 2

Reset to default
1

You need to add some kind of ordering column to the first table. Otherwise it doesn't make sense. I decided to add the extra column seq that seves this purpose.

Therefore, with the following data:

create table flow ( stockid int, seq int, flow char(1), stock int ); insert into flow (stockid, seq, flow, stock) values (1, 10, '+', 2); insert into flow (stockid, seq, flow, stock) values (1, 11, '-', 3); insert into flow (stockid, seq, flow, stock) values (1, 12, '+', 8); create table stock ( id int, instock int ); insert into stock (id, instock) values (1, 22); insert into stock (id, instock) values (2, 51); insert into stock (id, instock) values (3, 92);

The query is:

select s.id, f.seq, f.flow, f.stock, s.instock + sum(case when f.flow = '+' then 1 else -1 end * f.stock) over(partition by s.id order by f.seq) as sol from stock s left join flow f on f.stockid = s.id;

Result:

id seq flow stock sol -- ------ ------ ------ ------ 1 10 + 2 24 1 11 - 3 21 1 12 + 8 29 2 <null> <null> <null> <null> 3 <null> <null> <null> <null>
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

In SQL Server 2008, you are stuck with apply for calculating the cumulative sum:

select t1.*, tt1.stock + t2.instock from t1 cross apply (select sum(case when flow = '+' then tt1.sock else - tt1.stock end) as stock from t1 tt1 where tt1.id <= t1.id ) tt1 cross join (select top (1) t2.* from t2 order by id ) t2;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.