I have two tables - let's call them dbo.ValuesToReduce and dbo.Reserve The data in the first table (dbo.ValuesToReduce) is:
The second table (dbo.Reserve) looks like this
What I need to do is: update the Values in ValuesToReduce table using the latter table of Reserves, reducing the numbers until the reserve supply is exhausted. Here's what I should get after running the script:
So basically, every partner has a "reserve" which he can deplete, and values in the value table should be reduced by partner accordingly if there is still something in the reserves. Reserves should be collocated in the order provided by ValuesToReduceId.
For partner with PartnerId of 1, you can see that he had enough reserve to update his first value to 0 and still had some left to reduce the second value by that amount.
Partner with ID of 2 had a reserve of 425.19, and there were two entries in the values table for that partner, 601.98 and 44.56, in that order (by ValuesToReduceId), so we only updated the first value since the reserve is not big enough for both. The wrong way would have been to update the second value to 0.00 and the first to 221.35.
Partner with ID of 3 has more than enough reserve, so after updating his value to 0, he's left with -9.06
I tried something with recursive cte, but I can't seem to get my head around it. Hope I described the problem clearly enough..
Here is a set based approach.
answered Oct 15 '09 at 04:23 PM
I don't think this is possible in a truly set based manner - because the fact that you have more than one PartnerID value in the ValuesToReduce table makes it an iterative process. Is there any way that you can re-factor such that PartnerID is unique in the values to reduce table? As that would make the problem much much simpler...
If not, then the way I would attack it is actually with a cursor (because it is an iterative process in it's nature). In fact, I would use two cursors, with logic roughly equivalent to:
edit -> I really hope someone comes along with a set-based solution, however. I'd learn a lot from that.
answered Oct 14 '09 at 10:05 AM
Matt Whitfield ♦♦
An interesting question, but I don't think you can do it with as a set-based solution. Even if you could, I'm not sure you would want to. Any solution you could come up with will almost certainly look a little crazy to the next guy that's got to support it.
Most old-school db guys would have already passed this off as something you should handle in your application rather than in the database. I don't exactly believe in hard-line rules, but in this case I think you either have to use your application or some cursors/loops to get the functionality you're looking for.
answered Oct 14 '09 at 03:52 PM
Thanks guys. I ended up writing something similar to Andomar's solution here http://stackoverflow.com/questions/1564917/reducing-values-in-one-table-until-reserves-depleted-in-another-recursion
I'll try to optimize it and fiddle with it some more when I have more time, since it's an interesting problem.
answered Oct 15 '09 at 03:50 AM