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