Billy Pang
tuxedo_man at hotmail.com
Sat Dec 25 14:41:19 CST 2004
Assuming that there is a column called expired_datetime, how about creating a view that displays all the tickets that are currently locked up and cannot be touched. when a user logs on at 10:02am, a record is created with expiry datetime five minutes into the future. The view will have a where clause that filters out expired out records. This way you can kill expired tickets without the fuss of a sql server agent. If a pair of tickets is not displayed in this view, it means that it is not locked up and therefore is available for acquisition. If a second user logs on at 10:04am, the tickets held up by the first user cannot be acquird by this user. However, if the second user logs on at 10:08am, assuming first user did not purchase tickets, the second user can acquire the same tickets since those tickets will no longer show up in the view (in which case, another record is created with expiry date five minutes into the future). When second user does buy the tickets, you don't have to do anything to that record you created from the second acquisition since the tickets do not get deposited back into the inventory pool and the acquisition record will automatically disappear after expiry datetime has passed. Hope that helps. Billy >From: Arthur Fuller <artful at rogers.com> >Reply-To: dba-sqlserver at databaseadvisors.com >To: dba-sqlserver at databaseadvisors.com >Subject: Re: [dba-SQLServer] TicketMaster-like Timer >Date: Fri, 24 Dec 2004 23:42:44 -0500 > >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 > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com >