Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    14

    Unanswered: Time difference report using date range

    Hi,

    I would like to know a clue as below in the same thread as it is more relavant to this, if you do not mind.

    I have one more query using the same table and set of data for one of the report.

    Here the the column call_date is timestamp.

    Calls by n min interval
    Parameters should be startDate and endDate.

    This is actually 2 queries. The simplest one, should return a list of games with the start and end time, similar to previous report.
    The second, should return number of calls every n minutes interval. So, if n is 1, should return something like this:

    Period Calls
    starting (Nos.)
    21:00 34
    21:01 42
    21:02 55
    ... ...
    22:58 88
    22:59 63

    Ideally, the number of minutes in an interval should be dynamic, (can be passed as a parameter).

    Many thanks in advance.

    Jayesh

    Please find the attached script for generating table and populating data.

    create table calls
    2 as
    3 select 441132394629 cli, to_date('02102006 17:30','ddmmyyyy HH24:MI') call_date from dual union all
    4 select 441132394629, to_date('05102006 17:00','ddmmyyyy HH24:MI ') from dual union all
    5 select 421132533793, to_date('04102006 14:30','ddmmyyyy HH24:MI ') from dual union all
    6 select 421132533793, to_date('05102006 12:20','ddmmyyyy HH24:MI ') from dual union all
    7 select 421142373223, to_date('04102006 10:30','ddmmyyyy HH24:MI ') from dual union all
    8 select 421142373223, to_date('05102006 13:50','ddmmyyyy HH24:MI ') from dual union all
    9 select 421227763301, to_date('02102006 16:30','ddmmyyyy HH24:MI ') from dual union all
    10 select 421227763301, to_date('04102006 11:20','ddmmyyyy HH24:MI ') from dual union all
    11 select 421227763301, to_date('05102006 17:20','ddmmyyyy HH24:MI ') from dual union all
    12 select 421227763301, to_date('08102006 17:30','ddmmyyyy HH24:MI ') from dual
    13 /

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Something like this would work :

    Code:
    select to_char(to_date(NbMinutes*60,'SSSSS'),'HH24:MI') Time, NbCalls
    from
    (
    select trunc(to_number(to_char(start_date, 'SSSSS'))/60) NbMinutes, count(*) NbCalls
    from calls_table
    where start_date >= to_date('10/09/2006 13:45:00','DD/MM/YYYY HH24:MI:SS') 
    and start_date <= to_date('10/09/2006 13:49:00','DD/MM/YYYY HH24:MI:SS') 
    group by trunc(to_number(to_char(start_date, 'SSSSS'))/60)
    );
    Code:
    rbaraer@Ora10g> create table calls_table(start_date date);
    
    Table created.
    
    rbaraer@Ora10g> insert into calls_table(start_date) values (to_date('10/09/2006 13:45:30','DD/MM/YYYY HH24:MI:SS'));
    
    1 row created.
    
    rbaraer@Ora10g> insert into calls_table(start_date) values (to_date('10/09/2006 13:45:46','DD/MM/YYYY HH24:MI:SS'));
    
    1 row created.
    
    rbaraer@Ora10g> insert into calls_table(start_date) values (to_date('10/09/2006 13:45:46','DD/MM/YYYY HH24:MI:SS'));
    
    1 row created.
    
    rbaraer@Ora10g> insert into calls_table(start_date) values (to_date('10/09/2006 13:47:19','DD/MM/YYYY HH24:MI:SS'));
    
    1 row created.
    
    rbaraer@Ora10g> insert into calls_table(start_date) values (to_date('10/09/2006 13:47:59','DD/MM/YYYY HH24:MI:SS'));
    
    1 row created.
    
    rbaraer@Ora10g> insert into calls_table(start_date) values (to_date('10/09/2006 13:48:00','DD/MM/YYYY HH24:MI:SS'));
    
    1 row created.
    
    rbaraer@Ora10g> select to_char(to_date(NbMinutes*60,'SSSSS'),'HH24:MI') Time, NbCalls
      2  from
      3  (
      4  select trunc(to_number(to_char(start_date, 'SSSSS'))/60) NbMinutes, count(*) NbCalls
      5  from calls_table
      6  where start_date >= to_date('10/09/2006 13:45:00','DD/MM/YYYY HH24:MI:SS')
      7  and start_date <= to_date('10/09/2006 13:49:00','DD/MM/YYYY HH24:MI:SS')
      8  group by trunc(to_number(to_char(start_date, 'SSSSS'))/60)
      9  );
    
    TIME     NBCALLS
    ----- ----------
    13:45          3
    13:47          2
    13:48          1
    
    rbaraer@Ora10g>
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Oct 2006
    Posts
    14
    Hi Rbaraer,

    Thanks a lot for your hlelp.

    Jayesh

Posting Permissions

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