Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004

    Unanswered: loop query for dates I think


    I wish to count the number of events per day starting at 0700 each day.

    I am able to count the events with the following

    Select count(*)
    From table_name
    Where time_date between to_date(’01-feb-2004 07’,’dd-mon-yyyy hh24’) and
    to_date(’02-feb-2004 07’,’dd-mon-yyyy hh24’) and

    But I wish to run the query for a month at a time – adding the date to the select statement causes those events in the period 0000 to 0700 to be included in the following days count.

    I think I am after a way of stating in the query use the date of ’01-feb-2004’ but the time period of 0700-0700.

    I have tried adding the fraction of day

    Where time_date between to_date(’01-feb-2004’,’dd-mon-yyyy ’) + 0.25 and
    to_date(’02-feb-2004’,’dd-mon-yyyy’) + 0.25 and

    But get the same result

    I believe the answer may lie in a loop query where each day is selected and the count carried out but I am at a loss of where to start.

    Any assistance would be gratefully appreciated

  2. #2
    Join Date
    Mar 2002
    Reading, UK

    select trunc(time_date), count(*)
    from table
    where to_number(to_char(time_date,'HH24')>6
    group by trunc(time_date)

Posting Permissions

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