[dba-SQLServer] TicketMaster-like Timer

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





More information about the dba-SQLServer mailing list