Arthur Fuller
artful at rogers.com
Fri Dec 24 22:44:09 CST 2004
Most definitely food for thought, and thank you. Mark Breen wrote: >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 >> >> >> >> >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > > > > > -- 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