Arthur Fuller
artful at rogers.com
Fri Dec 24 22:42:44 CST 2004
I think I follow your logic Steve, but I don't see how it leads to running the job for each user. I.e. you log in at 10:00 am and request 4 tickets, I log in at 10:02 am and request 4 tickets. How do I kill you at 10:10am and me at 10:12am? That's the part I am having a tough time dealing with. Arthur Steve Conklin (Developer at UltraDNT) 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.298 / Virus Database: 265.6.4 - Release Date: 12/22/2004