Help on query needed

Ok guys.

Lets say i have 2 tables. One called 'Leads' and the other called 'Sales_Reps'. When a new lead comes in would like to assign it to the sales rep who has the least number of active leads. If there is a tie, I'll just assign it alphabetically between the sales reps who are tied with the least.

Can someone point me in the right direction here? I would assume I'd need to do some sort of select count, but I'm not sure how to begin.

Thanks!basic idea is below. I had to make some assumptions about your table DDL obviously, since you didn't provide it.

select top 1
sr.sales_rep_id, count(*)
from sales_reps sr
inner join leads l on l.sales_rep_id=sr.sales_rep_id
group by sr.sales_rep_id
order by count(*) asc|||Sorry. Let me be a bit more descriptive. I havent built the tables yet, but here's what I was thinking.

Sales Rep Table
Sales_Rep_ID
Sales_Rep_Name
Sales_Group

Leads Table
Lead_ID
Customer_Name
Customer_Phone
Lead_Description
Sales_Rep_Name
Sales_Rep_ID

When the lead comes in, the Sales_Rep_Name field will either be blank, or have a default value in it. Then, I'd like to have SQL automatically assign the sales rep like I mentioned in the initial post, but (I guess) using an insert trigger.

Does this help?|||if those are your tables, then the query i posted should work.

btw, you should get rid of the Leads.Sales_Rep_Name column. it's redundant and breaks normalization as it's already in Sales_Rep.

also I wouldn't use a trigger. assuming you are inserting leads using a sproc, you can have that sproc assign the lead as well.|||OK. That helps alot. I'm pretty new to SQL, so here's another quick question if you don't mind..

The application that is managing all my leads is a proprietary app and I don't have the ability to call a sproc directly from the app. Therefore, I thought that the trigger was the way to go. I assumed the sproc must be called by the app, and that it can't invoke itself, am I right?|||if the app is calling a sproc, are you in control of the sproc? if so you could modify it to assign a lead to a sales rep.

If the app is inserting directly into a table with an ad-hoc insert statement, then you'll have to use a trigger I guess.

it sounds like you need to understand your situation better though, and figure out which of the two cases above is happening. or something else entirely perhaps.|||I'm definitely not in control of how the application is inserting the value into the table, so it sounds like the trigger is the way to go.|||not necessarily. if you own the database, and the app is calling a sproc, then you can just modify the sproc and there is no need of a trigger.

stored procedures live in sql server, not in your client app.|||Gotcha. Makes sense. Thanks for your help, and your patience. You've taught me alot.