Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    2

    Unanswered: Query 5% of each other

    Hi:

    Currently i am doing an application which administers sales of certain products. I am trying to make a report that shows all pairs of salesman whose sales were 5% of each other on every product, any ideas on how to do this??

    the tables look something like this
    Inventory:
    ___________________
    |ID-I | Description |
    -----------------------------
    |1a | Pen |
    |2a | Pencil |
    |3a | Eraser |
    -----------------------------

    Sales:
    ___________________
    |ID-S | ID-I | Quantity|
    -----------------------------
    |1 | 1a | 40.00 |
    |1 | 2a | 24.00 |
    |1 | 3a | 30.00 |
    |2 | 1a | 40.00 |
    |2 | 2a | 22.00 |
    |2 | 3a | 30.00 |
    |3 | 2a | 30.00 |
    ------------------------------
    Employee
    __________________________
    |ID-S | Name |
    ------------------
    |1 | John |
    |2 | Jane |
    |3 | Moe |

    So the result that i am looking would look something like
    (John, Jane)
    (Jane, John)

    Moe wouldn't be answer since he didnt sale the same products as john and jane he still needs to sale pens and erasers even though he sold the same quantity as John and Jane for erasers

    thanks
    lonrot

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What school is this for?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by lonrot
    ... all pairs of salesman whose sales were 5% of each other on every product
    Try a self-join with these two conditions (equality on product, and "sales1 > 1.05*sales2") as the join condition.
    You'll probably also need some kind of "group by" (& having count(*) = 3) if by "every product" you really mean all 3 products.
    Try building up the query logic step by step, by using CTEs.
    Last edited by Peter.Vanroose; 10-26-08 at 06:34.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Peter.Vanroose
    Try a self-join with these two conditions (equality on product, and "sales1 > 1.05*sales2") as the join condition.
    You'll probably also need some kind of "group by" (& having count(*) = 3) if by "every product" you really mean all 3 products.
    Try building up the query logic step by step, by using CTEs.
    What makes you think he/she is using DB2?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Oct 2008
    Posts
    2
    thanks Peter.Vanroose that gave me a good start and i can figure it out from there. I am going to do a subquery where i get only the salesman who sold the same products to get the for "every product"

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Marcus_A
    What makes you think he/she is using DB2?
    Because this is the DB2 forum.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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