Home » SQL & PL/SQL » SQL & PL/SQL » Time difference report using date range
Time difference report using date range [message #197144] Tue, 10 October 2006 04:44 Go to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Hi,

I would like to know a clue as below:

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 441132533793, to_date('04102006 14:30','ddmmyyyy HH24:MI ') from dual union all
6 select 441132533793, to_date('05102006 12:20','ddmmyyyy HH24:MI ') from dual union all
7 select 441142373223, to_date('04102006 10:30','ddmmyyyy HH24:MI ') from dual union all
8 select 441142373223, to_date('05102006 13:50','ddmmyyyy HH24:MI ') from dual union all
9 select 441227763301, to_date('02102006 16:30','ddmmyyyy HH24:MI ') from dual union all
10 select 441227763301, to_date('04102006 11:20','ddmmyyyy HH24:MI ') from dual union all
11 select 441227763301, to_date('05102006 17:20','ddmmyyyy HH24:MI ') from dual union all
12 select 441227763301, to_date('08102006 17:30','ddmmyyyy HH24:MI ') from dual
13 /

Re: Time difference report using date range [message #197146 is a reply to message #197144] Tue, 10 October 2006 04:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you not work out how to doing this for yourself, useing one of the two previous solutions to problems almose identical to this that I've posted (to your questions) in the last week?
Re: Time difference report using date range [message #197211 is a reply to message #197146] Tue, 10 October 2006 08:48 Go to previous message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Hi,

Yes, I am doing at the same time alomg with posting this query so that i can compare the result. Anyway, I'll get back to you if any difficulty.

thanks
jayesh
Previous Topic: validate address
Next Topic: SQL PROBLEM !!! PLEASE HELP
Goto Forum:
  


Current Time: Wed Dec 07 16:08:32 CST 2016

Total time taken to generate the page: 0.05200 seconds