Move Through Table 1

[jbroyles]

I have a Static Table with Sales People that's used for assigning new leads. The leads are manually assigned to account for vacations, specialties, etc.. But I want the database to suggest the next sales person in line as there are multiple people fielding the leads. My example is below:

ID SalesPerson
1 Bob
2 Steve
3 Mark
4 Brian

A Customer Service Rep assigns a new lead to (1)Bob. When another Customer Service Rep gets a lead, I want the database to suggest (2)Steve. I'm O.K. making this work by referring to the ID value, plus 1 ([ID] + 1). However, in the example once (4)Brian is assigned, how do I get the next suggestion to be (1)Bob again.

 

[fluteplr]

How about adding a datetime field that us updated each time you assign a lead to a person. Then to get the next person you do:

select top 1 id, salesperson from salespeople order by lastleaddate

 

[jbroyles]

My underlying data is actually more complex, encompassing both multiple divisions and multiple disciplines so I plan to store the last assigned salesperson for each in it's own table. Also, in the client application, the CSR (Customer Service Rep) can re-assign a lead if the salesperson doesn't respond within a certain time period. They would manually pick the next salesperson from the list but I don't want the next salesperson to be assigned affected by reassignments. This would create a situation where a salesperson chooses not to respond to a lead that they think may not be as "prime" as others and the next salesperson would get stuck with that "not prime" lead and miss the opportunity for the next good lead. Sound confusing?

I just figured out how to do it using the example:

Table1 - Salesperson
Table2 - LastSalesperson
Table1 Data as above example
Table2 Data is one record with the following columns
ID LastSalespersonID LastSalesperson
1 4 Brian

If (Select (([ID])+ 1) FROM Table1 WHERE [ID] = (SELECT LastSalespersonID FROM Table2 WHERE [ID] = 1)) > (SELECT COUNT (ID) FROM Table1)
BEGIN
SELECT TOP 1 ID, Salesperson FROM Table1
END
ELSE
BEGIN
Select ID, Salesperson FROM Table1 WHERE [ID] = (SELECT LastSalespersonID FROM Table2 WHERE [ID] = 1)+ 1
End

This Query results in ID 1 being returned if LastSalespersonID is 4. It will accomodate all my divisions and specialties because it uses the count so I'll use it as a stored procedure and pare down the recordset it is applied to first.

Thanks for your help. If anyone has any improvements, let me know.

Thanks again Feeling silly :~/,

Jason

 

[jbroyles]

Also,

I've decided to update Table2 in the above example from a client side script using if...then so if the CSR decides to overide the suggested salesperson it doesn'update Table2 therby allowing the next salesperson to remain in line for the next lead. As long as we're dilligent at keeping Table1 up to date it should work out marvelously.

I think I'm actually getting the hang of this stuff ;-)

Thanks Again