Solved

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

Posted on 2007-03-19
5
184 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Login 17 40
sql server query from excel 3 57
Update SQL to SP1 on SCCM server 7 13
Need SQL that flips  value 5 10
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

920 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

17 Experts available now in Live!

Get 1:1 Help Now