Mark Breen
marklbreen at gmail.com
Mon Dec 20 04:11:13 CST 2004
Hello Authur and Steve, I agree with Steve's approach, a self correctly architecture is desirable here, but Authur, I am pondering another aspect to your question. I do not know what % of orders are abandoned and for this discussion, the reason is irrelevant. Also, I do not know what stock levels your client currently holds. But, if you have a quite high % of orders that ultimately are only enquiries and reasonably low stock, you will effectively always have 10 - 20% of your stock unavailable. Can you consider some other form of allocations, sort of like when you book your airline tickets, you have the seat but it could be gone by the time you actually try to pay for it. This may inconvenience some customers but the alternative is that the stock would not have been there anyway. Also, with the method discussed, the time wasters actually prevent real customers acquiring product. And of course there is the cost to your client of carrying extra stock to compensate for the lost 10-20% of stock. My logic my not be applicable depending on the actual numbers, but this may be food for thought. Let us know how you tackle it. Mark On Mon, 20 Dec 2004 01:11:13 -0500, Steve Conklin (Developer at UltraDNT) <Developer at ultradnt.com> wrote: > Some of the details of this should be altered depending on whether or > not you want to fully track "shopping -cart abandonment." Either your > InventoryTransaction table line-items can have 4 statuses (IN, OUT, > Pending OUT, Pending (back) IN), or you have 2 tables, Inventory Txn and > Inventory Txns Pending. Either setup can then be queried for current > inventory (which should be a calculation, anyway), such as Product 1 > inventory = Product 1's (INs + Pend INs) LESS (OUTs + Pend OUTs). > Lets assume you also generate a unique order id, and keep that in the > inventory transactions, and the order has a status of either complete > or pending, and the date-time of order start. > Run a job on the SQL server that executes every N minutes, and selects > the Inv. Txns of Pend Out status for all orders that are pending with a > date-time of less than Now minus N. Set all those orders to status > ABANDONED and insert a matching Pend IN for each of the Pend OUTs. > (Completed orders already have done the appropriate inventory OUTs.) > > Hth > Steve > > > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur > Fuller > Sent: Monday, December 20, 2004 12:05 AM > To: dba-sqlserver at databaseadvisors.com > Subject: [dba-SQLServer] TicketMaster-like Timer > > I have a requirement that I have virtually no experience in handling. I > named the subject of this message due to the similarity of my > requirement and the functionality of that site. The general idea is > this. You want to grab some inventory and hold it for N minutes, making > the available inventory equal P - Requested Qty. Then another sproc will > > perhaps fire that seals the grab and sets Actual Inventory to P - > Requested Qty (i.e. the credit card was authenticated and the qty was > sold). Failing that, upon expiry of N minutes the Request is cancelled > and the Available Inventory is reset to P. I hope this is clear. Here is > > an example: > > Client wants 4 units. > Inventory is 10 units. > Set the timer to N minutes, and show available as 6 units. Client > completes transaction, then set actual inventory to 6 units. Timer > expires without completion, reset available inventory to 10 units. > > Note: > It could happen that Q clients request units (i.e. 10 available, Client > 1 requests 4, Client 2 requests 2, Client 3 requests 2), so I need in > this case 3 timers and also the ability to display the available > inventory as the actual inventory minus the sum of the requests. 10 - (4 > > + 2 + 2) = 2. > > My colleagues and I have kicked this problem around and we've decided > this is a back-end challenge. How convenient for them! I really have no > clue how to attack this. Suggestions most gratefully accepted. > > TIA, > Arthur > > Arthur Fuller wrote: > > > Always happy to help, Francisco, especially given the number of times > > you have helped me. But on this one I think I need a little more > > explanation. Are you wanting an empty row to appear for the months > > that are missing, or what? > > > > Arthur > > > > Francisco Tapia wrote: > > > >> perhaps someone here can help me out so this reporting goes a lot > >> smoother :) > >> > >> Here is a sample snapshot of my data > >> > >> 8/1/2003, 1, SampleText, Qty1 > >> 10/1/2003, 1, SampleText, Qty1 > >> 11/1/2003, 1, SampleText, Qty1 > >> 1/1/2004, 1, SampleText, Qty1 > >> 8/1/2004, 1, SampleText, Qty1 > >> > >> There is only data for these months... would you create a temp table > >> to fill in the rest of the months within the criteria? > >> > >> ex: 8/1/2003 - 8/31/2004 > >> > >> > >> > >> > >> > > > > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.296 / Virus Database: 265.6.0 - Release Date: 12/17/2004 > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >