Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2007
    Posts
    5

    Unanswered: 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!

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    basic idea is below. I had to make some assumptions about your table DDL obviously, since you didn't provide it.
    Code:
    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

  3. #3
    Join Date
    Jan 2007
    Posts
    5
    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?
    Last edited by megajam02; 01-14-07 at 21:37.

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    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.

  5. #5
    Join Date
    Jan 2007
    Posts
    5
    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?

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    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.
    Last edited by jezemine; 01-14-07 at 22:32.

  7. #7
    Join Date
    Jan 2007
    Posts
    5
    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.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    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.

  9. #9
    Join Date
    Jan 2007
    Posts
    5
    Gotcha. Makes sense. Thanks for your help, and your patience. You've taught me alot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •