[thelist] SQL to get combo that sums to amount

[Luther, Ron]

aspscript at canada.com noted:

>>What I'm trying to do is simulate a "stock market"
>>where a user places an order for a certain number of
>>shares.  For example, if a user places an order to to
>>buy 50 then I need to go thru and search to see if
>>there is any combination of sell orders that adds to 50
>>to complete the order.  


Hi 'aspscript',


Sounds very very similar to MRP functionality ...  you need to 'peg' 
demand against supply.  {There are lots of books and resources for 
checking into MRP stuff.}

Not too bad to set up. The 'real fun' comes into play if you need to try 
to 'tune' your processing logic to optimize the volume of executable 
orders!   ;-)

Q - Will you allow "buy" or "sell" orders to be partially filled? (If I 
have an outstanding 'sell' order for 50, can I execute a 'buy' order of 
10 against it ... or do I have to wait until I have enough 'buy' orders 
to account for the whole 50?)


Now, I haven't given this a whole lot of thought ... (and it's been a 
while since I've built one of these) ... but assuming you can only execute 
"complete" orders I think I'd look at this as a multiple step process ... 
one to 'allocate and reserve' quantities and another one or more to actually 
'execute' the orders.

I might consider adding an 'allocated' flag to the "buy orders" table, 
a 'reserved' flag to the "sell orders" table, and set up a new table 
called "pegging" that contained the 'buy' order number, the 'buy quantity' 
being reserved against a matching 'sell' order, the 'remaining qty left to 
buy', the 'sell' order number, and the 'remaining qty left to sell'.

Grab all the 'buy orders' [demand] for a given stock/commodity/widget and 
start allocating demand and 'reserving' supply [decrementing your 'sell' 
orders for this same stock/whatsit].  When remaining qtys hit zero, set 
the appropriate flag in the appropriate table.  [Once nice feature here 
is that the way these tables are constructed if you want to know the current 
remaining status on a buy or sell order you only need to look at the last 
record in a recordset returned from this table for that order. That comes 
in very handy!]

Process two would then look at allocated buy orders, check the pegging table 
to see if all supply orders have been fully reserved and, if so, execute the 
appropriate buy and sell orders. (You may need a separate process here or a 
check to make sure you don't try to execute the same 'sell' order twice.)


Good Luck!!


RonL.

[I mentioned 'tuning' above because running off order size alone may 
'orphan' some large or 'odd size' orders so that they never get executed 
- causing hard feelings with customers you may not want to upset ... but 
a FIFO methodology alone probably won't optimize the volume of executed 
orders.]