Solved

Taking action in SQL Server PER-Record - is it possible?

Posted on 2007-03-19
5
179 Views
Last Modified: 2012-06-27
Dear Experts,

I am making a marketing application which will allow the marketer to call a list of contacts who meet the criteria setup by the marketing manager. The manager can setup multiple queues for each marketing user.  The marketing user should start calling from the queue which has the highest priority, and as it runs out, they should keep moving down the list of queues assigned to him.  I have everything working, except for the Stored procedure which determines WHICH contact to call.  It should have one input parameter which is @UserID and should return one parameter, which is @ContactID

For example, user 104 logs in and requests a contact to call.  What *Should* happen is the stored procedure looks in the [AppMarketing].QueuesUsers table to determine which queue has the highest priority assigned to this user. Lets say the following information is returned from the QueuesUsers table for user 104:

FK_User_ID  FK_Queue_ID   Priority
------------------------------------------
104               2000                 1
104               2001                 2
104               2002                 3
etc...

First, the procedure should pull the FK_Queue_ID with the highest priority (lowest number in the priority field) and then determine if this queue has any contacts in the buffer. If there is a contact in the buffer, it should update QueuesBuffer.FK_LockedBy_User_ID and return the ContactID of the record we just locked... However, a few things could come into play, and this is where I Have the problem...

If there are no records in the buffer (Queues.CurrentBufferSize = 0) AND (LastQueryRun > 1 hour ago) THEN we need to execute [AppMarketing].INS_PreloadQueueBuffer to load the buffer.  This stored procedure will load 15 contacts into the buffer for this queue AND SET LastQueryRun = GetUtcNow(). After doing this, we need to check this Queue record (again - because it just changed) to see if it has any available records in the buffer.  If it does, then return one like stated above.  However, if there are no records, then we go to the next queue and do the same thing...  Phew...  Next time the marketer requests a contact it should skip over this queue because the buffer is empty and the LastQueryRun < 1 hour ago.  This ensures we don't do a lot of processing and select statements on ten different tables when we don't need to.

------------------------------------------------
Three fields have importance in the Queues table:
1. LastQueryRun - this is the last time the stored procedure [AppMarketing].INS_PreloadQueueBuffer was executed.
2. CurrentPoolSize - This is the integer count of all of the contacts meeting the criteria for the Queue.
3. CurrentBufferSize - this is a tinyint count of all of the contacts related to this queue in the QueuesBuffer table.  It is updated via a trigger and is always accurate.
------------------------------------------------

For a visual layout of what I am talking about, please view this screenshot:

http://75.33.104.230/public/db_queues.png

I hope this makes sense - I have been trying to do it for a few days but I don't know if it is even possible.  Since SQL server sends an entire result set I can't work with each individual record, as I could in C# with say, a DataSet.  PLease help!  

Thanks in advance,

StankStank
0
Comment
Question by:stankstank
  • 4
5 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 18746776
you can loop and process throuh the tables with a While loop (preferred) or a cursor (non preferrred)

however I think your basic design flaw is that you are confusing the requirement of a user to Get a contact
to call with the system requirement to keep the queues organised...

the User requires a contact ... give him one! he shouldn't wait for his top prority queue to fill (unless he's got absolutely no work to do at all..)

you need another tread/ or a scheduled job to maintain the queues...
(you could trigger the queue fill from an empty queue positions via a trigger...)

what you need to ensure is that you have asynchronous processing in place
and an effient / short transaction length.


 
   
 

   
0
 

Author Comment

by:stankstank
ID: 18748668
>> you need another tread/ or a scheduled job to maintain the queues...
(you could trigger the queue fill from an empty queue positions via a trigger...) <<

I thought about using a trigger, but the problem comes when the queue totally runs out of contacts.  Then what would happen is that the buffers table would never 'fire' the trigger because a contact will never be pulled...

>> you can loop and process throuh the tables with a While loop <<
I would like to do this, but I don't know where to begin..

>> however I think your basic design flaw is that you are confusing the requirement of a user to Get a contact to call with the system requirement to keep the queues organized... <<
Queues are organized on a 'per user' basis.  The manager may choose to have users 2 & 3 on one queue, while the other users are on the other queues...  As the queues run out of contacts to call, the user returned contacts in queues which are of less importance to him/her.

>> the User requires a contact ... give him one! he shouldn't wait for his top priority queue to fill (unless he's got absolutely no work to do at all..) <<
Well, the user will always pull from the queue with the top priority - unless that queue is empty.  In this case, we should go to the queue with the next highest priority.  In our office our marketers have to call contacts who have just purchased from us - this is the highest importance...after they run out, then they start calling and doing surveys and other things of this nature...  These would be separate queues.  While user 1 is calling the surveys,  users 2 & 3 can be making sure that all new customers are called.  Likewise, user 4 can be calling the follow-up surveys.
0
 

Author Comment

by:stankstank
ID: 18748676
I am going to add an extra 500 point question for whoever helps me get this - please help me figure this thing out!
0
 

Author Comment

by:stankstank
ID: 18752596
Ugh...  The user has 2 queues assigned:  QueueID 5 & 7.  FOR EACH QUEUE THEY ARE ASSSIGNED, I need to check the Queues table and see if there are records in the CurrentBufferSize column... If not, I need to execute the INS_PreloadBuffer stored procedure.

Can it be that hard???  I can't do it, but I'm not an expert by any means - please help!  At least get me started???

Stank
0
 

Author Comment

by:stankstank
ID: 18769887
pff... I figured out the problem.  I just needed a while loop to look through the select statement.  I think I over complicated the question - free 500 pts. :)

Thanks,

StankityStank
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now