Re: Running totals

In response to Bastiaan Olij :
>
> So say my data from the start is:
> 1, 20100801, 400 , 0, 0, 0
> 2, 20100801, 300 , 0, 0, 0
> 3, 20100802, 100 , 0, 0, 0
> 4, 20100803, 500 , 0, 0, 0
> 5, 20100804, 800 , 0, 0, 0
> 6, 20100805, 300 , 0, 0, 0
> 7, 20100806, 400 , 0, 0, 0
>
> I want to end up with:
>
> 1, 20100801, 400 , 400, 0, 0
> 2, 20100801, 300 , 300, 0, 0
> 3, 20100802, 100 , 100, 0, 0
> 4, 20100803, 500 , 200, 300, 0 <-- 400+300+100+500 brings us to 1300, so
> we start using bucket 2
> 5, 20100804, 800 , 0, 700, 100 <-- 1300+800 brings us to 2100, so we
> start using bucket 3
> 6, 20100805, 300 , 0, 0, 300
> 7, 20100806, 400 , 0, 0, 400
>
> Any ideas?

If you on 8.4, then yes. This code isn't correct, but i think, you can
see the way:

test=*# select * from olij;
 id | value
----+-------
  1 |   400
  2 |   300
  3 |   100
  4 |   500
  5 |   800
  6 |   300
  7 |   400
(7 rows)

select *,
  case when sum(value) over (order by id) < 1000
    then value
    else case when sum(value) over (order by id) - value < 1000
      then value + 1000 - sum(value) over (order by id)
      else 0
    end
  end as bucket1,
  case when sum(value) over (order by id) between 1000 and 2000
    then case when sum(value) over (order by id) - value < 1000
      then sum(value) over (order by id) - 1000
      else value
    end
    else 0 end as bucket2,
  case when sum(value) over (order by id) between 2000 and 3000
    then value else 0 end as bucket3 from olij order by id;


 id | value | bucket1 | bucket2 | bucket3
----+-------+---------+---------+---------
  1 |   400 |     400 |       0 |       0
  2 |   300 |     300 |       0 |       0
  3 |   100 |     100 |       0 |       0
  4 |   500 |     200 |     300 |       0
  5 |   800 |       0 |       0 |     800
  6 |   300 |       0 |       0 |     300
  7 |   400 |       0 |       0 |     400
(7 rows)


Again, it's NOT correct, i haven't time yet, but i hope you can see the
way...


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99