DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Not enjoying dates!

Hybrid View

  1. #1
    Join Date
    Mar 2003
    Posts
    6

    Not enjoying dates!

    I really am not having fun with this to_date function! I would like to figure out the busiest hour from my list of entries, though am a very long way away:

    I have created a table as such -

    CREATE TABLE table1(
    person INTEGER
    ,whn DATE
    ,code VARCHAR(10)
    ,PRIMARY KEY (person,whn)
    );

    And populated the table with -

    INSERT INTO table1 VALUES (1,TO_DATE('25 Feb 2003 06:10:00','dd Mon YYYY HH24:MI:SS'),'XE 0A DB');

    and so on!

    Yet I cannot figure out how I would go about finding the busiest hour for a single person. I understand MAX and MIN to find which would be the first and last entry hours... though to find which hour they have the most entries elludes me?!? i.e. a persons busiest hour!

    I am unhappy. The program to help me solve bookings has now become the biggest one!

    Thank you for any reply.

    Regards,
    Poot.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Something like:

    select person, to_char(whn, 'DD-MON-YYYY HH24'), count(*) counter
    from table1
    group by person, to_char(whn, 'DD-MON-YYYY HH24')

    will count "hits" per person per hour. Then use this as sub-query to find the peak period per person.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can use CASE statement.

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by tamilselvan
    You can use CASE statement.
    Perhaps I'm being dim, but I don't see how CASE will help
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

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


Click Here to Expand Forum to Full Width