Hi All,
I was wondering if anyone had a neat trick to do the following. Right
now the only way I can think of is to go with cursors but I hate those.
I did manage to do this with a query but that became exponentially
slower the bigger the data set as I was counting the total up to the
record I'm updating in a subquery and the further down the dataset I
get, the bigger the reverse lookup.
In Sybase there was a trick where you update a variable that resulted in
a running total but you didn't have control over the order in which the
records are processed.
I have a table with the columns:
- Primary key
- Date
- Value
- Bucket_1
- Bucket_2
- Bucket_3
What I want is to store any value up to the sum of 1000 into bucket 1,
up to 2000 into bucket 2, and the remainder in bucket 3, by date. Date
does not have to be unique so the primary key is used as a second sort.
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?
--
Kindest Regards,
Bastiaan Olij
e-mail/MSN: bastiaan@basenlily.me
web: http://www.basenlily.me
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij