Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: query: "count" each minute between start/end

Re: query: "count" each minute between start/end

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 28 Jul 2007 10:38:25 +0200
Message-ID: <46ab0081$0$1245$426a34cc@news.free.fr>

"Nikolas Tautenhahn" <virtual_at_gmx.de> a écrit dans le message de news: f8dm6f$qpk$01$1_at_news.t-online.com...
| Hi there...
|
| I have a table with access statistics with columns representing the
| [date] when a user first connected and the duration[seconds] they stayed.
|
| For a graphical report I need to create a view which shows how many
| users were online each minute of the day, i.e. say we have this raw data:
|
| user1: entered today, 9:30:17, stayed for 200 seconds (so he needs to be
| counted for 9:31, 9:32, 9:33 as he left 9:30:37).
| user2: entered today, 9:28:10, stayed for 180 seconds (so he needs to be
| counted for 9:29, 9:30, 9:31) and what I would like to get from this raw
| data is something like:
|
| 9:29, 1
| 9:30, 1
| 9:31, 2
| 9:32, 1
| 9:33, 1
| Let's name the connect date when a user entered "c_date" and the
| duration simply "duration"
| how could I do something? I can calculate the [date] when a user left by
| c_date + (1/86400)*duration and I guess I would need something like
| between, but I can't put my finger on how I could increment the counter
| for all whole minutes between c_date and the end-date...
|
| thanks for all help,
| N.

If I understand you want the number of sessions that: - starts before the beginning of the current minute and - ends during the current minute or after

SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> select * from t;

C_DATE DURATION

------------------- --------
28/07/2007 09:30:17      200
28/07/2007 09:28:10      180

2 rows selected.

SQL> def start='28/07/2007 09:26'
SQL> def duration=10
SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI';

Session altered.

SQL> with cal as
  2 ( select to_date('&start','DD/MM/YYYY HH24:MI')+(rownum-1)/1440 tim

  3      from dual
  4      connect by level <= &duration )
  5 select cal.tim, sum(decode(t.c_date,null,0,1)) "nbIn"   6 from cal, t
  7 where t.c_date (+) < cal.tim and (t.c_date(+)+duration(+)/86400) >= cal.tim   8 group by cal.tim
  9 order by cal.tim
 10 /

TIM nbIn

---------------- -----
28/07/2007 09:26     0
28/07/2007 09:27     0
28/07/2007 09:28     0
28/07/2007 09:29     1
28/07/2007 09:30     1
28/07/2007 09:31     2
28/07/2007 09:32     1
28/07/2007 09:33     1
28/07/2007 09:34     0
28/07/2007 09:35     0

10 rows selected.

Regards
Michel Cadot Received on Sat Jul 28 2007 - 03:38:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US