Re: complex? SQL time series problem

From: Geoff M <gmuldoonnospam_at_scu.edu.au>
Date: Mon, 21 Feb 2005 16:44:19 +1100
Message-ID: <MPG.1c84203e724d26169896b7@news.individual.net>

In article <1108737483.685588.220970_at_c13g2000cwb.googlegroups.com>, Mark.Powell_at_eds.com says...
> Geoff, I suspect that you have not described the problem correctly.
> The answer I gave should work for the problem as you described it.
> Obviously neither I nor Daniel understand correctly what it is you need
> to do.

OK trying again ..

CREATE TABLE TEST3
(

  C_SET   VARCHAR2(25),
  C_ID    VARCHAR2(25),
  STTS    VARCHAR2(25),

  S_DATE DATE,
  E_DATE DATE
)

INSERT INTO TEST3 ( C_SET, C_ID, STTS, S_DATE, E_DATE ) VALUES ( 
'a2', '125', 'A', '01-JAN-2000', '15-MAR-2000'); 
INSERT INTO TEST3 ( C_SET, C_ID, STTS, S_DATE, E_DATE ) VALUES ( 
'a2', '125', 'B', '16-MAR-2000', '16-JUL-2000'); 
INSERT INTO TEST3 ( C_SET, C_ID, STTS, S_DATE, E_DATE ) VALUES ( 
'a2', '125', 'A', '17-JUL-2000', '01-JAN-3000'); 
INSERT INTO TEST3 ( C_SET, C_ID, STTS, S_DATE, E_DATE ) VALUES ( 
'a2', '889', 'B', '03-FEB-2000', '13-MAY-2000'); 
INSERT INTO TEST3 ( C_SET, C_ID, STTS, S_DATE, E_DATE ) VALUES ( 
'a2', '889', 'A', '14-MAY-2000', '01-JAN-3000'); 

COMMIT; A simple select/group by of:

select c_set, stts, s_date, e_date, count(c_id) from test3
group by c_set, stts, s_date, e_date
order by c_set, stts, s_date

gives (note date overlaps here, the crux of my problem):

c_set   stts     s_date      e_date       count(c_id)
a2       A    01-JAN-2000  15-MAR-2000       1
a2       A    14-MAY-2000  01-JAN-3000       1   
a2       A    17-JUL-2000  01-JAN-3000       1
a2       B    03-FEB-2000  13-MAY-2000       1
a2       B    16-MAR-2000  16-JUL-2000       1

What I need is:

c_set   stts     s_date      e_date       count(c_id)
a2       A     01-JAN-2000  15-MAR-2000      1
a2       A     14-MAY-2000  16-JUL-2000      1
a2       A     17-JUL-2000  01-JAN-3000      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

Geoff M Received on Sun Feb 20 2005 - 23:44:19 CST