Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Lightbulb Unanswered: calculate commision and salary

    below are 2 tables-> shail_customer and shail_employee

    SQL> desc shail_customer;
    Name Null? Type
    ----------------------- -------- -------------
    CID NUMBER(5)
    CNAME VARCHAR2(20)
    CAMOUNT NUMBER(20)



    select * from shail_customer;

    CID CNAME CAMOUNT
    ------ -------------------- ----------
    1 shailesh 30000
    2 amit 400000
    3 utkarsh 35000
    4 satyajit 45000
    5 amol 44000



    SQL> desc shail_employee;
    Name Null? Type
    ----------------------- -------- ----------------
    EID NUMBER(10)
    ENAME VARCHAR2(20)
    BASIC_SALARY NUMBER(20)
    COMMISION NUMBER(20)
    INSENTIVES NUMBER(20)
    TOTAL_SAL NUMBER(20)


    primary key eid& cid
    *Commision is calculated as camount*.30
    *Total_sal is calculated as basic_salary+commision+insentives

    plz suggest a procedure to calculate commision and total salary

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    how do determine which employee sold to which customer?
    can 1 customer buy from multiple employees?
    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
    Feb 2013
    Posts
    3

    Arrow

    hey anacedent,

    for time being you can take one customer at a time..

    means i making a java prgm to calculate employee salary .
    Employee salary is based on his fixsalary+commision+insentives
    commision is calculated based on customers bill amount
    if customer bill amount is 30,000 on that amount 30% will be employee's referal commision

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    which row in customer table is associated with which row in employee table?
    Last edited by anacedent; 02-02-13 at 11:20.
    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.

  5. #5
    Join Date
    Feb 2013
    Posts
    3

    Arrow

    you can take 1st row of customer able and link with 1st row of employee...
    here you can put some value in employee table that will be done from front end...

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    rows in a table are like balls in a basket.

    which ball in a basket is the "first" ball?

    rows in a table have NO inherent order.

    >you can take 1st row of customer able and link with 1st row of employee...

    what is result when customer table has more rows than employee table?

    Realize & understand that this "design" is fatally flawed!
    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.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You need a third table that will tie the employee to the customer a shail_sales table. It will have at a minimum CID, EID
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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