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

Home -> Community -> Usenet -> c.d.o.server -> Re: Another tricky SQL

Re: Another tricky SQL

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 10 Feb 2006 20:38:36 +0100
Message-ID: <43ecebbd$0$17064$626a54ce@news.free.fr>

"Graham" <graham.parsons_at_reflective.com> a écrit dans le message de news: 1139594162.787306.158810_at_z14g2000cwz.googlegroups.com...
| Firstly,
|
| Can I thank all those who have helped me in the past.
|
| I have another tricky requirement and I am hoping someone has the
| knowledge to assist me.
|
| Problem: We have a table written into by monitors during a "run". The
| monitors can be active for all or just part of the run. We need to
| know the number of monitors that were running for each minute of the
| run period.
|
| TABLE
| monitor_id
| start_date_time (to ms)
| end_date_time (to ms)
|
| Example data:
|
| 1, 01/01/06 12:00:30, 01/01/06 12:02:28
| 2, 01/01/06 12:00:56, 01/01/06 12:03:01
| 3, 01/01/06 12:02:10, 01/01/06 12:02:90
| 4, 01/01/06 12:05:10, 01/01/06 12:09:58
| 5, 01/01/06 12:06:00, 01/01/06 12:07:24
|
| The output I am looking for is:
|
| 01/01/06 12:00, 2
| 01/01/06 12:01, 2
| 01/01/06 12:02, 3
| 01/01/06 12:03, 1
| 01/01/06 12:04, 0
| 01/01/06 12:05, 1
| 01/01/06 12:06, 2
| 01/01/06 12:07, 2
| 01/01/06 12:08, 1
| 01/01/06 12:09, 1
|
| I want to show that the number can go up and down. The requirement to
| show rows with zero is nice but not mandatory if it proves too much of
| a pain.
|
| Views, helper tables, etc. and all in scope if they help!
|
| Many thanks
|
| Graham
|

SQL> select id, to_char(start_date,'HH24:MI:SS') start_time,   2 to_char(end_date,'HH24:MI:SS') end_time   3 from t
  4 order by 1
  5 /

        ID START_TI END_TIME
---------- -------- --------

         1 12:00:30 12:02:28
         2 12:00:56 12:03:01
         3 12:02:10 12:02:50
         4 12:05:10 12:09:58
         5 12:06:00 12:07:24

5 rows selected.

SQL> def start='01/01/2006 12:00'
SQL> def duration=11
SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI';

Session altered.

SQL> select cal.tim, count(t.start_date) "nbActive"   2 from ( select to_date('&start','DD/MM/YYYY HH24:MI')+(rownum-1)/1440 tim

  3         from dual
  4         connect by level <= &duration ) cal,
  5       t
  6  where ( t.start_date >= cal.tim and t.start_date < cal.tim+1/1440 )
  7     or ( t.end_date >= cal.tim and t.end_date < cal.tim+1/1440 )
  8     or ( t.start_date < cal.tim and t.end_date >= cal.tim+1/1440 )
  9 group by cal.tim
 10 order by cal.tim
 11 /
TIM                nbActive
---------------- ----------
01/01/2006 12:00          2
01/01/2006 12:01          2
01/01/2006 12:02          3
01/01/2006 12:03          1
01/01/2006 12:05          1
01/01/2006 12:06          2
01/01/2006 12:07          2
01/01/2006 12:08          1
01/01/2006 12:09          1

9 rows selected.

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, nvl(t.nb,0) "nbActive"   6 from cal,
  7       ( select cal.tim, count(t.start_date) nb
  8         from cal, t
  9         where ( t.start_date >= cal.tim and t.start_date < cal.tim+1/1440 )
 10            or ( t.end_date >= cal.tim and t.end_date < cal.tim+1/1440 )
 11            or ( t.start_date < cal.tim and t.end_date >= cal.tim+1/1440 )
 12         group by cal.tim ) t

 13 where t.tim (+) = cal.tim
 14 order by cal.tim
 15 /
TIM                nbActive
---------------- ----------
01/01/2006 12:00          2
01/01/2006 12:01          2
01/01/2006 12:02          3
01/01/2006 12:03          1
01/01/2006 12:04          0
01/01/2006 12:05          1
01/01/2006 12:06          2
01/01/2006 12:07          2
01/01/2006 12:08          1
01/01/2006 12:09          1
01/01/2006 12:10          0

11 rows selected.

Regards
Michel Cadot Received on Fri Feb 10 2006 - 13:38:36 CST

Original text of this message

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