Steve Conklin (Developer@UltraDNT)
Developer at UltraDNT.com
Mon Dec 20 00:11:13 CST 2004
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