Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id D8006B5D91F for ; Wed, 15 Jun 2011 13:38:00 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 40934-07 for ; Wed, 15 Jun 2011 16:37:54 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id CF8EDB5D8BD for ; Wed, 15 Jun 2011 13:37:53 -0300 (ADT) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id M36PV826; Wed, 15 Jun 2011 17:37:51 +0100 Message-ID: From: "Oliveiros d'Azevedo Cristina" To: "Jira, Marcel" , References: Subject: Re: Merge overlapping time-periods Date: Wed, 15 Jun 2011 17:37:51 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001A_01CC2B82.F35DB7D0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201106/53 X-Sequence-Number: 35519 This is a multi-part message in MIME format. ------=_NextPart_000_001A_01CC2B82.F35DB7D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Howdy, Marcel, In the example output you provided the ID =3D 2 should have just one = record...Ain't I right? Best, Oliveiros ----- Original Message -----=20 From: Jira, Marcel=20 To: 'pgsql-sql@postgresql.org'=20 Sent: Wednesday, June 15, 2011 4:23 PM Subject: [SQL] Merge overlapping time-periods Hi! =20 Although I try for some time, I am not able to write an SQL-Query that = can do the following: =20 I have a very big table (let's call it "mytable") with information = like this: =20 ID BEG END 1 2000-01-01 2000-03-31 1 2000-04-01 2000-05-31 1 2000-04-15 2000-07-31 1 2000-09-01 2000-10-31 2 2000-02-01 2000-03-15 2 2000-01-15 2000-03-31 2 2000-04-01 2000-04-15 3 2000-06-01 2000-06-15 3 2000-07-01 2000-07-15 =20 There's an ID and time periods defined by a start value (BEG) and an = end value (END) =20 I want to merge all periods belonging to the same ID, iff their time = periods are overlapping or in a direct sequence. =20 Therefore the result should somehow look like this: =20 ID BEG END1 2000-01-01 2000-07-311 2000-09-01 = 2000-10-312 2000-01-15 2000-03-312 2000-04-01 2000-04-153 = 2000-06-01 2000-06-153 2000-07-01 2000-07-15=20 I tried using "WITH RECURSIVE" but I didn't succeed. =20 My server is PostgreSQL 8.4. Unfortunately I can't do anything like = update or install some fancy module. =20 Thank you for your help! =20 Best regards, =20 Marcel Jira ------=_NextPart_000_001A_01CC2B82.F35DB7D0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Howdy, Marcel,
 
In the example output you provided the = ID =3D 2=20 should have just one record...Ain't I right?
 
Best,
Oliveiros
----- Original Message -----
From:=20 Jira,=20 Marcel
Sent: Wednesday, June 15, 2011 = 4:23=20 PM
Subject: [SQL] Merge = overlapping=20 time-periods

Hi!

 

Although I try for some time, = I am not=20 able to write an SQL-Query that can do the = following:

 

I have a very big table = (let=92s call it=20 =93mytable=94) with information like this:

 

ID =20 BEG         =20 END

1  =20 2000-01-01   2000-03-31

1  =20 2000-04-01   2000-05-31

1  =20 2000-04-15   2000-07-31

1  =20 2000-09-01   2000-10-31

2  =20 2000-02-01   2000-03-15

2  =20 2000-01-15   2000-03-31

2  =20 2000-04-01   2000-04-15

3  =20 2000-06-01   2000-06-15

3  =20 2000-07-01   2000-07-15

 

There=92s an ID and time = periods defined by=20 a start value (BEG) and an end value (END)

 

I want to merge all periods = belonging to=20 the same ID, iff their time periods are overlapping or in a direct=20 sequence.

 

Therefore the result should = somehow look=20 like this:

 

ID  =
BEG          =
END
1   2000-01-01   =
2000-07-31
1   2000-09-01   =
2000-10-31
2   2000-01-15   =
2000-03-31
2   2000-04-01   =
2000-04-15
3   2000-06-01   =
2000-06-15
3   2000-07-01   =
2000-07-15

 

I tried using =93WITH = RECURSIVE=94 but I=20 didn=92t succeed.

 

My server is PostgreSQL 8.4. = Unfortunately=20 I can=92t do anything like update or install some fancy=20 module=85

 

Thank you for your=20 help!

 

Best = regards,

 

Marcel=20 Jira

------=_NextPart_000_001A_01CC2B82.F35DB7D0--