Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Another tricky SQL
"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
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
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