Forum Stats

  • 3,836,948 Users
  • 2,262,207 Discussions
  • 7,900,151 Comments

Discussions

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

2»

Answers

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
This discussion has been closed.