1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to query the greatest number of calls per Salesman and at what hour?

Discussion in 'SQL PL/SQL' started by Alan_S, Apr 30, 2012.

  1. Alan_S

    Alan_S Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    I'm new to the forum. I've been trying to find ways to query this but it's difficult for me :(

    Here is the data in a table (Calls):

    SALESMAN 10:00 AM 11:00 AM 12:00 PM 01:00 PM 02:00 PM
    SALESMAN1 12465 11653 8692 9914 9557
    SALESMAN2 213 196 167 183 218
    SALESMAN3 265 328 331 301 309

    and here are the results I'm expecting

    SALESMAN TIME #_OF_CALLS
    SALESMAN1 10:00 AM 12465
    SALESMAN2 02:00 PM 218
    SALESMAN3 12:00 PM 331

    Is there a way to do this?

    I'm running Oracle 9

    HINT: Using "Greatest" function of the time fields will yield the max value however I'm having trouble with linking this max value of each salesman to their column_name from all_tab_columns.

    Let me know if you have any questions

    Thanks!
     
  2. Alan_S

    Alan_S Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Nothing beats solving a problem on your own. I solved my own question and able to extract the hour which had the greatest # of calls by salesman using a series of CASE WHEN functions :)
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Yes you are right my friend... Solving the problem yourself gives a great amount of satisfaction. It would also be great if you posted your final query here so that others can refer to the solution in the future.

    Regards
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi alan,

    Can you please post the answer!