Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    3

    Unanswered: Assigning records by percentage

    Hi,
    There is a concept that I need to apply to the SQL Server database that I develop.
    To keep it simple, lets say we have a database storing customers in a customer table. Each customer is assigned to an agent, who looks after their needs. So each record in customer table has an agent_id - a foreign key to an Agent table.
    We have a requirement to assign customer records to agents on a percentage basis. For example (sorry for loss of spacing):

    Agent table:
    agent_id agent_name percentage_assignment_of_customers
    1 john 25
    2 mary 35
    3 mark 15
    4 jane 25

    Each new customer that registers should be automatically assigned to an agent in such a way that the above figures are adhered to.
    My trouble is coming up with a system that can automatically assign an agent_id to each new record that is added to the customer table.
    I'm thinking I should have some kind of running reference counter that is checked and gets incremented each time a new assignment is made. But how to get it to stick to the percentages is running me in circles.

    If anyone has done anything similar before or can point me direction in the right direction that would be great.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I haven't tested it, but I'm pretty sure that you want:
    Code:
    SELECT TOP 1 c.agent_id
       FROM dbo.Customer AS c
       JOIN dbo.Agent AS a
          ON (a.agent_id = c.agent_id)
       GROUP BY c.agent_id
       HAVING a.percentage_assignement_of_customers < (SELECT COUNT(c.agent_id)
    /     1e2 * (SELECT COUNT(*) FROM dbo.Client AS z1))
       ORDER BY COUNT(c.agent_id), c.agent_id
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2011
    Posts
    3
    Thanks Pat,
    I'm trying that out now. However I get the following error:

    Column 'dbo.Agent.percentage_assignement_of_customers' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.


    thanks,
    Brian

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT TOP 1 c.agent_id
       FROM dbo.Customer AS c
       GROUP BY c.agent_id
       HAVING (SELECT a.percentage_assignement_of_customers
          FROM dbo.Agent AS a WHERE a.agent_id = c.agent_id) < (SELECT 
          COUNT(c.agent_id) / 1e2 * (SELECT COUNT(*) FROM dbo.Client AS z1))
       ORDER BY COUNT(c.agent_id), c.agent_id
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2011
    Posts
    3
    Thanks again Pat - almost there I think.
    I'm getting nothing back yet - but I think I need to do a convert to float on those calculations to them to work. I'll try it further tomorrow and let you know how I get on.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh pooh! I think I see the problem now. I normally store percentages "as used" instead of "as displayed" so I muffed the conversion.
    Code:
    SELECT TOP 1 c.agent_id
       FROM dbo.Customer AS c
       GROUP BY c.agent_id
       HAVING (SELECT a.percentage_assignement_of_customers
          FROM dbo.Agent AS a WHERE a.agent_id = c.agent_id) < (SELECT 
          1e2 * COUNT(c.agent_id) / (SELECT COUNT(*) FROM dbo.Client AS z1))
       ORDER BY COUNT(c.agent_id), c.agent_id
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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