Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2008
    Posts
    6

    Unanswered: help with PL/SQL query?

    HI all

    i am trying to create a simulator query in oracle pl/sql for assigning customers with agents:

    My agent table is as follows

    agent_num,agent_name,agent_score,customer_num
    1,John,35,12
    2,Kelly,25,12
    3,Jackson,45,12

    1,John,55,13
    2,Kelly,65,13
    3,Jackson,75,13

    1,John,95,14
    2,Kelly,15,14
    3,Jackson,25,14

    here agent score is related to customer number..

    Now when i add the agent score to customer_score it should be compared only on the customer number
    So my customer table :

    Customer_name,Customer_num, customer_score
    John,12,35,
    James,13,55
    harvard,14,25

    So now you add the customer_score with the agent_score based on customer number and who ever has highest score that agent is assigned to the Customer and so..on

    for example:

    John,12,35,- Agent3 jackson is assigned since customerscore 35+agent_score 45 =80 -rank 1
    James,13,55 - agent 2 Kelly since 55+55=110-rank 1( agent 3 is not considered since he is already assigned with a customer)
    harvard,14,25 - agent 1 since he is remaining agent....


    here is the SQL for create and insert for testing:


    CREATE TABLE Customer(
    Customer_name varchar2,Customer_num number, customer_score number
    ) ;


    CREATE TABLE Agent(
    Agent_number number,agent_name varchar2,Agent_Score number,Customer_num number

    );

    INSERT INTO Customer VALUES ('John',12,35);

    INSERT INTO Customer VALUES ('James',13,55);

    INSERT INTO Customer VALUES ('harvard',14,25);


    INSERT INTO Agent VALUES (1,'Dennis',95,14);

    INSERT INTO Agent VALUES (2,'Kelly',12,14);

    INSERT INTO Agent VALUES (3,'Jackson',25,14);


    INSERT INTO Agent VALUES (1,'Dennis',35,12);

    INSERT INTO Agent VALUES (2,'Kelly',25,12);

    INSERT INTO Agent VALUES (3,'Jackson',45,12);


    INSERT INTO Agent VALUES (1,'Dennis',55,13);

    INSERT INTO Agent VALUES (2,'Kelly',65,13);

    INSERT INTO Agent VALUES (3,'Jackson',75,13);


    thank you in advance

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2008
    Posts
    6
    Sorry ...thought some one might help me in this forumm with another idea....kind of urgent working on it even now friday night....

Posting Permissions

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