conditional incremensum (condition is on the sum value of the previous row)

Aketi Jyuuzou

I think MODEL is the best way to do this.

I think recursive with clause may be the best way to do this B-)
Hehe I used PostgreSQL8.4 because I do not have Oracle11gR2 :8}

with recursive tmp(HIREDATE,SAL) as(
values(date '1980-12-17',  800),
      (date '1981-02-20',-1600),
      (date '1981-02-22',-1250),
      (date '1981-04-02', 2975),
      (date '1981-05-01', 2850),
      (date '1981-06-09', 2450),
      (date '1981-07-08',-1500),
      (date '1981-07-28',-1250),
      (date '1981-11-17',-5000),
      (date '1981-12-03',  950),
      (date '1981-12-03', 3000),
      (date '1982-01-23', 1300),
      (date '1987-04-19', 3000),
      (date '1987-05-23', 1100)),
tmp2(rn,SAL) as(
select Row_Number() over(order by HIREDATE),SAL
  from tmp),
rec(rn,SAL,CREDIT) as(
select rn,SAL,SAL from tmp2
where rn=1
union all
select b.rn,b.SAL,Least(0,a.CREDIT)+b.SAL
  from rec a,tmp2 b
 where a.rn+1 = b.rn)
select rn,SAL,-Least(0,CREDIT) as CREDIT
  from rec;

 rn |  sal  | credit
----+-------+--------
  1 |   800 |      0
  2 | -1600 |   1600
  3 | -1250 |   2850
  4 |  2975 |      0
  5 |  2850 |      0
  6 |  2450 |      0
  7 | -1500 |   1500
  8 | -1250 |   2750
  9 | -5000 |   7750
 10 |   950 |   6800
 11 |  3000 |   3800
 12 |  1300 |   2500
 13 |  3000 |      0
 14 |  1100 |      0