Just as I thought everything was going really well, I ran into a problem when developing my application... This part of the application deals with telemarketers and they are constantly updating and retrieving new contact's to call. Who they call is setup through what we call a Queue. Here is the meat of the problem:
Users are assigned to queues and each queue for the user to call has a priority. We want the queues with 1 to be called first, and the higher number to be called last. I don't want to try to Preload the buffer for the highest queue every time the telemarketer pushes the button to get a record if there are no contacts which meet the queue's criteria. For this reason, the columns [LastQueryRun], [CurrentPoolSize] and [CurrentBufferSize] come into play. Each time [AppMarketing].INS_Preload
r is called, it will update [LastQueryRun] and [CurrentPoolSize]. If the poolsize is 0 and the last query ran was less than 45 minutes ago, we should skip this queue. However, if the poolsize is 0 (or any number for that matter) and the lastqueryrun field was MORE than 45 minutes ago, we should execute [AppMarketing].INS_Preload
r. This will update LastQueryRun and CurrentPoolSize. So the problem is, when that happens, we don't know what the values are...we don't know if we should just return a contactID for this Queue or go to the next. How can we go BACK on a select?! This isn't a dataset or anything! What should happen is that we pull the [CurrentBufferSize] field and if it's <> 0 then we return a contactID from it... If not, we do the same thing to the next queue.
r has a trigger which counts it's records for the FK_Queue_ID which was inserted or deleted and updates that count in [AppMarketing].Queues.Curr
ize. This helps us to always have an accurate count of what is in the Buffer to know whether or not we should preload (populate) the buffer by calling [AppMarketing].INS_Preload
At this point, you are probably saying to yourself 'why does he have a QueuesBuffer table anyway?' Well, the reason I added this is because I want a quick pool of ContactID's to grab from when a telemarketer needs someone to call. Coming up with this information has quite a bit of overhead as the stored procedure [AppMarketing].INS_Preload
Buffer will have to search through anywhere between 3 to 15 tables depending on the criteria the administrative user has specified in the Queue.
In summary, I already have the most of this done, all I can't do is figure out how to loop through the assigned Queues for the selected user... I have started over on this portion of this stored procedure, but the big stored procedure [AppMarketing].INS_Preload
r is already completed! yay!
The only parameter this stored procedure (the one that loops through the queues) should accept is @UserID. it should determine the @QueueID parameter based on the QueueID of the current record.
Please see the screen shot at http://220.127.116.11/public/db_queues.png
for a clear picture of this.
Thank you 21 times,