Re: complex? SQL time series problem

Geoff M wrote:

> Hi all,
>
> Oracle 9.2.0.6 on Linux ....
>
> I have a table, a log over time of the status of clients in client sets
> resembling the following:
>
> s_date e_date c_set c_id stts
>
> 01-jan-2000 15-mar-2000 a2 125 A
> 16-mar-2000 16-jul-2000 a2 125 B
> 17-jul-2000 sysdate a2 125 A
> ....
> 03-feb-2000 13-may-2000 a2 158 B
> 14-may-2000 sysdate a2 158 A
> ....
>
> I need to group by c_set, stts and time interval, giving the count of
> c_id, eg:
>
> c-set stts s_date e_date count(c_id)
>
> a2 A 01-jan-2000 15-mar-2000 1
> a2 A 16-mar-2000 13-may-2000 0
> a2 A 14-may-2000 16-jul-2000 1
> a2 A 17-jul-2000 sysdate 2
> a2 B 03-feb-2000 15-mar-2000 1
> a2 B 16-mar-2000 13-may-2000 2
> a2 B 14-may-2000 16-jul-2000 1
> a2 B 17-jul-2000 sysdate 0
>
> Can anyone suggest a way this could be achieved in SQL without resorting
> to PL/SQL, as I want if possible to create a materialized view of this
> aggregation.
>
> TIA
>
> Geoff M

And exactly what is it about GROUP BY that won't work?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)