Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-03-19
5
Medium Priority
?
200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1500 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

604 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