Return the "Next" Record from a Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Return the "Next" Record from a Table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-07 : 07:42:58

Adam writes "I have a table that holds a list of all Sales Calls received, and who took them. It has become more important to distribute these calls properly. I have an app that inserts rows in to the table.
SalesPerson, DateOfCall, Company, Notes

I need a stored procedure that figures out who should receive the next call. It doesn't need to go in order. For example:

If SP-A and SP-B have received 3 calls each, and SP-C, SP-D have received 2 calls each, return EITHER SP-C or SP-D

I can then use the returned value to update the table with a new row containing the correct person.

I just can't figure this one out. I am using VB.NET with MSDE.

Is this even possible to do? Thanks,

Adam"

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-07 : 08:11:22

Sounds like you need the count of calls of each SP-X, and the minimum value is the winner of the next call?

Without posting your table / column names, it leaves me to my imagina-shun.

SELECT SalesPerson, Count(*) as CallCount

FROM MyTable

GROUP BY SalesPerson

Now you need to pick the Salesperson with the minimum CallCount

SELECT MIN(CallCount)

FROM (
-- INSERT THE ABOVE QUERY HERE
) B

Now you've got the minimum, you can select just that one in the first query

SELECT Top 1 SalesPerson, CallCount

FROM (

SELECT SalesPerson, Count(*) as CallCount

FROM MyTable

GROUP BY SalesPerson

) C

WHERE CallCount = (

SELECT MIN(CallCount)

FROM (

SELECT SalesPerson, Count(*) as CallCount

FROM MyTable

GROUP BY SalesPerson

) B

)

The Top 1 guarrantees one selection if there are two or more minimums that match.

This solution seems wordy to me and I suspect there's a shorter solution.

Maybe Arnold or Jeff can cut it back.

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-07 : 08:51:03

Sam, nice explaination !!

I think you can make it shorter, though:

SELECT TOP 1 salesperson
FROM
table
GROUP BY salesperson
ORDER BY COUNT(*) ASC

that should return the salesperson with the fewest calls. You will probably need to add logic, though, because you may want to check only a certain time period (i.e., in the last year) and there may be cases where certain salespeople become "inactive" so you would have to exclude them, etc.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-07 : 08:58:27

Argh !

Thanks. It's not often I find it easy to say in fewer words what I usually take longer to communicate clearly because it's in thinking through my writings follow my thoughts? Brevity is not my most *robust* strong point of character noteworth !

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources