Re: Another tricky SQL

Graham wrote:
> 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

In what version of Oracle? In 10g you might want to look at the model clause.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)