Add all negative values between two positive values

Question

Here is my table

    ID  account Value date
    0     Tom    10   6/7
    1     Tom    -9   6/8
    2     Tom    -5   6/9
    3     Tom    -4   6/10
    4     Tom    20   6/11
    5     Tom    30   6/12
    6     Tom    -4   6/13
    7     Tom    -5   6/14
    8     Tom     7   6/15
    9     Tom    -5   6/16
   10     Tom    -5   6/16
   11     Tom    10   6/16
   12     Tom    -2   6/17

I have to add all negative values between positive values and minus them with previous positive value. Below is result of above example table

    ID  account Value date
    0     Tom    10   6/7
    4     Tom     2   6/11
    5     Tom    30   6/12
    8     Tom    -2   6/15
   11     Tom     0   6/16
   12     Tom    -2   6/17

Answer 1

Using MySQL: http://www.sqlfiddle.com/#!2/61773/18

Too much Mysqlism here, selected fields are not required to be on GROUP BY:

select 
  id, account, sum(value), date
from
(  
  select *, @gn := @gn +  ( (sign(value)+1) / 2 ) as gn
  from (tbl, (select @gn := 0)) vars
  order by id desc
) as x
group by gn
order by id

Better to write it like this, intent is explicit:

select 
  max(id) as id, max(account) as account, sum(value) as value, max(date) as date
from
(  
  select *, @gn := @gn +  ( (sign(value)+1) / 2 ) as gn
  from (tbl, (select @gn := 0) vars)
  order by id desc
) as x
group by gn
order by gn desc

---

Logic explanation for sign(value), see this: https://stackoverflow.com/a/10514961

Answer 2

Works on Sql Server 2008: http://www.sqlfiddle.com/#!3/4b7cb/2

with a as 
(
  select z.id,z.account,z.date, z.value, coalesce(x.id,-1) as grp
  from tbl z
  outer apply
  (
    select top 1 y.id
    from tbl y where y.id < z.id and y.value > 0
    order by y.id desc
  ) as x  
)
select 
  id = max(id), 
  account = max(account),
  value = sum(value),
  date = max(date)
from a 
group by grp
order by id

Data sample:

CREATE TABLE [tbl]
    ([ID] int, [account] varchar(3), [Value] int, [date] date);

INSERT INTO [tbl]
    ([ID], [account], [Value], [date])
VALUES
    (0, 'Tom', 10, '2001-06-07 00:00:00'),
    (1, 'Tom', -9, '2001-06-08 00:00:00'),
    (2, 'Tom', -5, '2001-06-09 00:00:00'),
    (3, 'Tom', -4, '2001-06-10 00:00:00'),
    (4, 'Tom', 20, '2001-06-11 00:00:00'),
    (5, 'Tom', 30, '2001-06-12 00:00:00'),
    (6, 'Tom', -4, '2001-06-13 00:00:00'),
    (7, 'Tom', -5, '2001-06-14 00:00:00'),
    (8, 'Tom', 7, '2001-06-15 00:00:00'),
    (9, 'Tom', -5, '2001-06-16 00:00:00'),
    (10, 'Tom', -5, '2001-06-16 00:00:00'),
    (11, 'Tom', 10, '2001-06-16 00:00:00'),
    (12, 'Tom', -2, '2001-06-17 00:00:00');

Output:

ID      ACCOUNT VALUE   DATE
0       Tom     10      2001-06-07
4       Tom     2       2001-06-11
5       Tom     30      2001-06-12
8       Tom     -2      2001-06-15
11      Tom     0       2001-06-16
12      Tom     -2      2001-06-17

---

How it works:

select *, coalesce(x.id,-1) as z_id
from tbl z
outer apply
(
  select top 1 y.id 
  from tbl y where y.id < z.id and y.value > 0
  order by y.id desc
) as x
order by z.id desc;

Output:

ID      ACCOUNT VALUE   DATE            Z_ID
12      Tom     -2      2001-06-17      11
11      Tom     10      2001-06-16      8
10      Tom     -5      2001-06-16      8
9       Tom     -5      2001-06-16      8
8       Tom     7       2001-06-15      5
7       Tom     -5      2001-06-14      5
6       Tom     -4      2001-06-13      5
5       Tom     30      2001-06-12      4
4       Tom     20      2001-06-11      0
3       Tom     -4      2001-06-10      0
2       Tom     -5      2001-06-09      0
1       Tom     -9      2001-06-08      0
0       Tom     10      2001-06-07      -1

Answer 3

While copying my answer I realised that you need it for sql-server or plsql. My solution is for MySQL. Sorry about that. Maybe it helps anyhow.

Without using cursors I see no way.

So here it comes.

Test data:

drop table if exists test;
create table test(id int, value int, c_date date);
insert into test values 
(    0     ,    10   ,'2012/6/7'),
(    1     ,    -9   ,'2012/6/8'),
(    2     ,    -5   ,'2012/6/9'),
(    3     ,    -4   ,'2012/6/10'),
(    4     ,    20   ,'2012/6/11'),
(    5     ,    30   ,'2012/6/12'),
(    6     ,    -4   ,'2012/6/13'),
(    7     ,    -5   ,'2012/6/14'),
(    8     ,     7   ,'2012/6/15'),
(    9     ,    -5   ,'2012/6/16'),
(   10     ,    -5   ,'2012/6/16'),
(   11     ,    10   ,'2012/6/16'),
(   12     ,    -2   ,'2012/6/17');

The procedure you need:

drop procedure if exists read_test;

create procedure read_test()
begin
declare done int default false;

declare v_id, v_value int;
declare v_date date;
declare adding_result int;

declare cur cursor for select id, value, c_date from test order by id;

declare continue handler for not found set done = true;

drop table if exists tmp_result;
create temporary table tmp_result as select * from test where 1 = 0;
set adding_result = 0;

open cur;

read_loop: LOOP
fetch cur into v_id, v_value, v_date;
if done then
leave read_loop;
end if;

if v_value >= 0 then

set adding_result = adding_result + v_value;
insert into tmp_result (id, value, c_date) values (v_id, adding_result, v_date);
set adding_result = 0;

else
set adding_result = adding_result + v_value;
end if;

end loop;
insert into tmp_result (id, value, c_date) values (v_id, adding_result, v_date);
close cur;

select * from tmp_result;

end;

And finally

call read_test();

That's it. Output:

id  value   c_date
0   10  2012-06-07
4   2   2012-06-11
5   30  2012-06-12
8   -2  2012-06-15
11  0   2012-06-16
12  -2  2012-06-17

Answer 4

Try this solution (caveat: Sql Server 2012) : http://www.sqlfiddle.com/#!6/f5ce0/7

with summary as
(
  select 
      *,
      -- if negative make it zero
      -- if positive make it 1


     grp = 
       sum((sign(value)+1) / 2)
       over(order by id desc) 


  from tbl
)
select 
  id = max(id),
  account = max(account),
  value = sum(value),
  date = max(date)
from summary
group by grp
order by grp desc

Output:

ID  ACCOUNT VALUE   DATE
0   Tom     10      2001-06-07
4   Tom     2       2001-06-11
5   Tom     30      2001-06-12
8   Tom     -2      2001-06-15
11  Tom     0       2001-06-16
12  Tom     -2      2001-06-17

---

First step, create a marker for positive and negative number, we will use this later for segregating groups:

select 
    *,
    -- if negative make it zero
    -- if positive make it 1
    (sign(value)+1) / 2 as rn

from tbl;

Output:

ID  ACCOUNT VALUE   DATE            RN
0   Tom     10      2001-06-07      1
1   Tom     -9      2001-06-08      0
2   Tom     -5      2001-06-09      0
3   Tom     -4      2001-06-10      0
4   Tom     20      2001-06-11      1
5   Tom     30      2001-06-12      1
6   Tom     -4      2001-06-13      0
7   Tom     -5      2001-06-14      0
8   Tom     7       2001-06-15      1
9   Tom     -5      2001-06-16      0
10  Tom     -5      2001-06-16      0
11  Tom     10      2001-06-16      1
12  Tom     -2      2001-06-17      0

Second step, SUM OVER those 1s 0s to create grouping, this is backward as per OP's problem: http://www.sqlfiddle.com/#!6/f5ce0/3

select 
    *,
    -- if negative make it zero
    -- if positive make it 1

   grp = 
     sum((sign(value)+1) / 2)
     over(order by id desc) 

from tbl;

Output:

ID  ACCOUNT VALUE   DATE            GRP
12  Tom     -2      2001-06-17      0
11  Tom     10      2001-06-16      1
10  Tom     -5      2001-06-16      1
9   Tom     -5      2001-06-16      1
8   Tom     7       2001-06-15      2
7   Tom     -5      2001-06-14      2
6   Tom     -4      2001-06-13      2
5   Tom     30      2001-06-12      3
4   Tom     20      2001-06-11      4
3   Tom     -4      2001-06-10      4
2   Tom     -5      2001-06-09      4
1   Tom     -9      2001-06-08      4
0   Tom     10      2001-06-07      5

Final step, group those related numbers: http://www.sqlfiddle.com/#!6/f5ce0/7

with summary as
(
  select 
      *,
      -- if negative make it zero
      -- if positive make it 1


     grp = 
       sum((sign(value)+1) / 2)
       over(order by id desc) 


  from tbl
)
select 
  id = max(id),
  account = max(account),
  value = sum(value),
  date = max(date)
from summary
group by grp
order by grp desc

Output:

ID  ACCOUNT VALUE   DATE
0   Tom     10      2001-06-07
4   Tom     2       2001-06-11
5   Tom     30      2001-06-12
8   Tom     -2      2001-06-15
11  Tom     0       2001-06-16
12  Tom     -2      2001-06-17