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 -> Another tricky SQL

Another tricky SQL

From: Graham <graham.parsons_at_reflective.com>
Date: 10 Feb 2006 09:56:02 -0800
Message-ID: <1139594162.787306.158810@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 Received on Fri Feb 10 2006 - 11:56:02 CST

Original text of this message

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