Re: Concatenating records according to dates

From: Marc <marc.hast_at_dexia.be>
Date: 6 Oct 2004 01:10:12 -0700
Message-ID: <c552081f.0410060010.17fe53bf@posting.google.com>

Up to now, a simple SELECT with a group by was all I needed. It works only for simple cases where one manager follows another and never comes back in that subsidiary again.

SELECT sub, mgr, MIN(startdate), MAX(enddate) FROM A GROUP BY sub,mgr

SUB	MGR	MIN(STARTDATE)	MAX(ENDDATE)
100	1	1/01/2003	30/11/2003
200	1	1/01/2003	1/02/2004
200	2	1/07/2003	1/01/2004
210	1	1/01/2003	1/10/2003
210	2	1/01/2004	1/06/2005
210	3	1/02/2004	1/03/2004

with a self-join construction like

SELECT A1.sub sub, A1.mgr mgr, A1.startdate startdate,

         A2.enddate enddate
  FROM A A1, A A2
  WHERE A1.sub = A2.sub AND A1.mgr = A2.mgr     AND A1.enddate = A2.startdate

SUB	MGR	STARTDATE	ENDDATE
100	1	1/01/2003	30/11/2003
210	1	1/01/2003	1/10/2003
210	2	1/04/2004	1/06/2005
210	2	1/03/2004	1/05/2004

it is possible to link 2 consecutive periods. However, I do not find a solution to link all consecutive periods.

Also by using analytical functions I don't find the right partition or means to collect only consecutive rows for a given subsidiary/manager.

SELECT sub, mgr,

         first_value(startdate)
           OVER (PARTITION BY sub, mgr ORDER BY startdate) startdate, 
  	 MAX(enddate) OVER (PARTITION BY sub, mgr) enddate

FROM A
ORDER BY 1,3

SUB	MGR	STARTDATE	ENDDATE
100	1	1/01/2003	30/11/2003
100	1	1/01/2003	30/11/2003
200	1	1/01/2003	1/02/2004
200	1	1/01/2003	1/02/2004
200	2	1/07/2003	1/01/2004
210	1	1/01/2003	1/10/2003
210	1	1/01/2003	1/10/2003
210	2	1/01/2004	1/06/2005
210	2	1/01/2004	1/06/2005
210	2	1/01/2004	1/06/2005
210	2	1/01/2004	1/06/2005
210	3	1/02/2004	1/03/2004

Received on Wed Oct 06 2004 - 03:10:12 CDT