| Author |
Topic |
|
bendertez
Yak Posting Veteran
94 Posts |
 Posted - 2011-02-02 : 05:43:34
|
| HiI have a a bit of a tough request here as I want to be able to assign accounts in a table to a number of agents. The table is as follows:Account Name Amount AgentJonnys Pizas 100.00The Sawboxed 250.00The Ship Inn 150.00Nailed for U 300.00Grange Parke 130.00Trains For U 200.00What I would like would be able to run a script that would evenly assign the accounts to 3 agents which as you can see from the table above is an empty field currently. So from my example above each of the 3 agents would have 2 accounts allocated as below:Account Name Amount AgentJonnys Pizas 100.00 Agent1The Sawboxed 250.00 Agent1The Ship Inn 150.00 Agent2Nailed for U 300.00 Agent2Grange Parke 130.00 Agent3Trains For U 200.00 Agent3Could anybody point me in the right direction of how I might achieve this by creating a procedure that I could run once a week.Any advice would be appreciated.ThanksDave |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-02 : 11:47:10
|
Do you want to store the values in the 2nd table? You would do this using an UPDATE statement but we would need more information before advising further...how do the tables currently link together?
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
bendertez
Yak Posting Veteran
94 Posts |
Posted - 2011-02-02 : 11:56:17
|
| HiThanks for the replyI imagine I would have to create a new table to store this data and maybe truncate it everytime it is run, dropping the new data in with the assigned agent. The data is currently held in one table (without the agent field). I was thinking that i would probably need to create a procedure that would evenly assign the agent in to a new table updating a the agent field in the new table. Just to confirm each account a unique ID within the originating table which i didn't put in my data example above. Does this explain what I'm trying to achieve any better?Thanks |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-02 : 12:49:22
|
Not really. Where does the Agent ID come from?You can do it in a view if the assigments are in another table..which would be better and less I/O rewriting etc..
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
bendertez
Yak Posting Veteran
94 Posts |
Posted - 2011-02-03 : 15:43:31
|
| HiSorry for not being clearer.All I want is to assign some ficticious agent id's to the end of a table (or view - which ever is the easiest). The agent ids are not in the database and don't have to be as it's purely for a report that I could then run and distribute per agent. Just to make it clear the agent details are not held in the database at all and therefore cannot be linked.At the moment the table has the accountid, account_name and Amount.I want to somehow run a query that will tag on an agentid field and distribute the accounts evenly so there will be accountid, account_name, Amount and AgentID so effectively if there are 9 accounts and 3 agents each agent will get 3 accounts each, which will then be displayed agent1, agent2 and agent3 in the agentid field.As I said there is no agent table to link to as the agentid terminology would effectively be just words to use for my convenience.I just want to assing an agentid (or word) to an account but do so evenly so if there are 9 accounts and 3 agent each agent gets an equal amount of fields.I hope this helps Thanks again |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-03 : 16:14:57
|
Will there always be 3 agents?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
bendertez
Yak Posting Veteran
94 Posts |
Posted - 2011-02-04 : 06:07:04
|
| Hi At the moment there are 3 but i suppose this could change over time.Thanks again |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-04 : 07:33:11
|
That makes things rather difficult. 1) if there is an odd number of accounts, who gets less?2) How do you assign which agent gets assigned what? random? by dollars? alphabetical order?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
bendertez
Yak Posting Veteran
94 Posts |
Posted - 2011-02-04 : 08:01:14
|
| HiI wouldn't be bothered who got less as we would only be talking about the odd account so it wouldn't make much difference if someone got more accounts than someone else.I imagine it would need to be random but again I wouldn't be bothered. It would be great to incorporate the rate and allocat per total amount, but I imagine that would be difficult to do.Thanks again for your reply. |
|
|
|
|
Allocating Accounts to Agents