August 4, 2011 at 12:30 pm
Here is my problem:
I have a simple table that basically contains account numbers, quantities, prices, and types. The quantities can be either negative or positive.
What i need to do is within a particular account and type, offset the negative quantities against the positive quantities in price order, and then record the offsets.
For example.
Rec_no, Account, Qty, Price, Type, Offset Amount, Offset Records
1, A001, 2, 45, call, 0, ''
2, A001, 1, 50, call, 0, ''
3, A023, 2, 46, put, 0, ''
4, A023, 2, 47, put, 0, ''
5, A023, -3, 45, put, 0, ''
6, A056, 1, 40, call, 0, ''
7, A056, 2, 45, call, 0, ''
8, A056, 2, 40, put, 0, ''
9, A056, -1, 47, call, 0, ''
10, A056, -1, 42, call, 0, ''
Would become the following after the update:
1, A001, 2, 45, call, 0, '' <-- no offset possible (no negative value in group)
2, A001, 1, 50, call, 0, '' <-- no offset possible
3, A023, 2, 46, put, 2, '5' <-- offset 2 (full) with record 5
4, A023, 2, 47, put, 1, '5' <-- offset 1 (partial) with record 5
5, A023, -3, 45, put, -3, '3;4' <-- offset -3 (full) with records 3 and 4
6, A056, 1, 40, call, 1, '10' <-- offset 1 (full) with record 10
7, A056, 2, 45, call, 1, '9' <-- offset 1 (full) with record 9
8, A056, 2, 40, put, 0, '' <-- no offset possible
9, A056, -1, 47, call, -1, '7' <-- offset -1 (full) with record 7
10, A056, -1, 42, call, -1, '6' <-- offset -1 (full) with record 6
Essentially for each company and type combination, I need to match offsetting records starting with the lowest price. the prices do not have to be the same to match the records, only the account and type. Negative qty offsets positive qty. Partial offsets are possible and if a records is partially offset, the remaining quantity can then be used to find additional offsets by continuing to find eligible offsetting records. The process is complete when either the negative or positive quantities (or both) have no more records with quantities that have not been offset.
I have thought of a number of possible paths to go down on this but I cannot quite get my head around it. For each matching offset I am updating two records (each side of the match). One of these records may still have a qty to offset so should then continue to be in the pool of records to match, but with a reduced quantity. the process continues, until, as I mentioned, there are no more records on one side (+ or -) left to match.
I can sort by account, type and price and then join the lowest + and - record, add the values and get the remaining quantity, but then I need to update both records with the offsets taken and continue to find additional matches with the record that was partially offset.
Sounds a little like recursion, but I have no idea how I would accomplish that in this context.
This is a fundamentally simple problem, but I don't see what is likely the obvious solution. Any help pointing out the obvious to me would be greatly appreciated!
August 4, 2011 at 1:02 pm
Rob Titerle (8/4/2011)
This is a fundamentally simple problem, but I don't see what is likely the obvious solution. Any help pointing out the obvious to me would be greatly appreciated!
Actually, this is not a fundamentally simple problem. SQL is optimized to deal with sets and this is not a set-based operation, so it is fundamentally complex (in T-SQL).
Your best bet is to look up "Quirky Update" and/or "Running Total" on this site. Jeff has a good article on how to do similar updates using the quirky update method. Since you need to update both sides, you'll probably have to use a temp table to do the quirky updates and then apply the results to the actual records.
If you want more details, you'll need to post table definitions, sample data, and expected results in an easily digestible format.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 4, 2011 at 10:50 pm
Thanks Drew,
I guess in a way I can be thankful it is not obvious...
I have been looking for a strategy and I did run into the quirky update but did not stop there long enough to really access how it could help. your answer did get me on a track that has lead to what might be a reasonable solution. You knocked sense into me by mentioning SQL was set based - so I rethought the approach and I now have a working prototype that joins the two records to identify a partner record and ease the calculations, but then only updates one side. The join is constructed so each record will only join with it's current preferred partner (lowest price, opposite sign), so both sides will get the appropriate update - just not at the same time. Using a standard UPDATE and doing a self join, update the qty_used amount based on the amount available for offset from the partner (joined) record and record the record # of the partner record (for simplicity I concatenate additional record numbers in one field for multiple partial offsets).
Each pass offsets the lowest price records. I perform multiple passes, each one promoting the current lowest price records for accounts that still have amounts available for offset. Once the join returns no records, there is nothing left to offset and I am done.
The code is short, although I suspect it is inefficient (I have not done any performance testing), but in this application, the files are small (2000 - 5000 records) so it is not a big deal. Much, much, much, better than doing it manually. Each record participating in a offset is read twice - once on the left and once on the right side of the JOIN. The join automatically eliminates any records that have no possible join partner in each pass.
Anyway, thank you for the response. I am going to do some more research to see if I can improve on this, but at least you set me thinking in the right direction. I am now going to venture into 'Quirky Update' and see what I am missing...
Regards,
Rob.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy