[dba-SQLServer] TicketMaster-like Timer

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




More information about the dba-SQLServer mailing list