complex? SQL time series problem

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 Received on Sun Feb 13 2005 - 20:47:13 CST