create table #T (id int,item varchar(100),day datetime,money int,out int,daynumber int)
insert into #T
select 1,'a','2008-5-1',60,-5,null union all
select 2,'a','2008-5-2',61,-30,null union all
select 3,'a','2008-5-8',30,-30,null union all
select 4,'a','2008-5-12',10,-5,null union all
select 5,'b','2008-5-1',60,-5,null union all
select 6,'b','2008-5-2',61,-30,null union all
select 7,'b','2008-5-8',30,-30,null union all
select 8,'b','2008-5-12',10,-5,null ;
select id,item,day,money,out,
daynumber = datediff(d,day,
(select top 1 day from #t c where a.money <
(select sum(- out) from #t where item=a.item and id >= a.id and id <=c.id)
)
)
from #t a
with
T1 as (select a.item, a.day day1,b.day day2,a.money,b.out from #T a, #T b where a.item=b.item and a.day<=b.day),
T2 as (select item, day1,day2,money, sum_out=(select sum(out) from #T1 where item=t.item and day1=t.day1 and day2<=t.day2) from #T1 as t),
T3 as (select item, day1,min(day2)day2 from T2 where money+sum_out<0 group by item, day1)
update a set a.daynumber=datediff(day,b.day1,b.day2) from #T a join T3 b on a.item=b.item and a.day=b.day1
select * from #t
1 a 2008-05-01 00:00:00.000 60 -5 7
2 a 2008-05-02 00:00:00.000 61 -30 10
3 a 2008-05-08 00:00:00.000 30 -30 4
4 a 2008-05-12 00:00:00.000 10 -5 0
5 b 2008-05-01 00:00:00.000 60 -5 7
6 b 2008-05-02 00:00:00.000 61 -30 10
7 b 2008-05-08 00:00:00.000 30 -30 4
8 b 2008-05-12 00:00:00.000 10 -5 0
