From Marcel.Jira@wu.ac.at Wed Jun 15 15:29:12 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id C749DB5DBC0 for ; Wed, 15 Jun 2011 12:29:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 36514-01 for ; Wed, 15 Jun 2011 15:29:05 +0000 (UTC) X-Greylist: delayed 00:06:25.420447 by SQLgrey-1.7.6 Received: from williams.wu-wien.ac.at (williams.wu-wien.ac.at [137.208.8.38]) by mail.postgresql.org (Postfix) with ESMTP id 7F48EB5DBBF for ; Wed, 15 Jun 2011 12:29:04 -0300 (ADT) Received: from exchange.ad.wu-wien.ac.at (caht3.wu-wien.ac.at [137.208.11.52]) by williams.wu-wien.ac.at (8.14.3/8.14.3/Debian-5+lenny1) with ESMTP id p5FFMZBv022932 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=FAIL) for ; Wed, 15 Jun 2011 17:22:36 +0200 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=wu.ac.at; s=sslmail2009; t=1308151356; bh=rn2ySu4jziWyRcchEwRTW5Z0MXneow2jHvl GRs5XtJs=; h=From:To:Date:Subject:Message-ID:Content-Type: MIME-Version; b=TyzfL7G35IQUdzE6N7gxmizFujadLZLXrhOZ6tm52mLQtvrIUC GgHOWnUnuCb8Fmgtf9tkrKXpS0nC9uhHbKhpWwzAU/6ky4+t7keZyyMuObzij0dztKc gJFBFGMOlgyq3jgAnH4hB5zdVyBmvAhZabnZQmFpegiUNQsLK1aVy0= Received: from mbx-b.ad.wu-wien.ac.at ([fe80:0000:0000:0000:1445:4987:120.122.206.98]) by caht3.ad.wu-wien.ac.at ([137.208.11.52]) with mapi; Wed, 15 Jun 2011 17:23:15 +0200 From: "Jira, Marcel" To: "'pgsql-sql@postgresql.org'" Date: Wed, 15 Jun 2011 17:23:15 +0200 Subject: Merge overlapping time-periods Thread-Topic: Merge overlapping time-periods Thread-Index: AcwrcCUp0kr8vzkrSPCI2sICxG+9ag== Message-ID: Accept-Language: de-DE Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: de-DE Content-Type: multipart/alternative; boundary="_000_D793F5C522F1DD40BB9DC43586C57637DE06389FE8MBXBadwuwiena_" MIME-Version: 1.0 X-AntiVirus: checked by Avira MailGate (version: 3.1.2; AVE: 8.2.5.14; VDF: 7.11.9.212; host: herpes); id=3311-5i82wM X-Virus-Scanned: clamav-milter 0.96.1 at pocken X-Virus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201106/50 X-Sequence-Number: 35516 --_000_D793F5C522F1DD40BB9DC43586C57637DE06389FE8MBXBadwuwiena_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi! Although I try for some time, I am not able to write an SQL-Query that can = do the following: I have a very big table (let's call it "mytable") with information like thi= s: 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 There's an ID and time periods defined by a start value (BEG) and an end va= lue (END) I want to merge all periods belonging to the same ID, iff their time period= s are overlapping or in a direct sequence. Therefore the result should somehow look 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 "WITH RECURSIVE" but I didn't succeed. My server is PostgreSQL 8.4. Unfortunately I can't do anything like update = or install some fancy module... Thank you for your help! Best regards, Marcel Jira --_000_D793F5C522F1DD40BB9DC43586C57637DE06389FE8MBXBadwuwiena_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi!

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

I have a very big table (let’s call it “mytable”) w= ith information like this:

ID=A0 BEG=A0=A0= =A0=A0=A0=A0=A0=A0=A0 END

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

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

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

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

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

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

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

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

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

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

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

Therefore the result should somehow look like this= :

<= /o:p>

ID=A0 BEG=A0=A0=A0=A0=A0=A0=A0=A0=A0 END1=A0=A0 2000-01-01=A0=A0 2000-07-31
1=A0=A0 20=
00-09-01=A0=A0 2000-10-31
2=A0=A0 2000-01-15=A0=A0 200=
0-03-31
2=A0=A0 2000-04-01=A0=A0 2000-04-15=
3=A0=A0 2000-06-01=A0=A0 2000-06-15
3=A0=A0=
 2000-07-01=A0=A0 2000-07-15
 
I tried using “WITH RECURSIVE” but I didn’t succeed.
 
My server is PostgreSQ=
L 8.4. Unfortunately I can’t do anything like update or install some =
fancy module…
 
T=
hank you for your help!
 
Best regards,
 
Marc=
el Jira

= --_000_D793F5C522F1DD40BB9DC43586C57637DE06389FE8MBXBadwuwiena_-- From sqlpro@club-internet.fr Wed Jun 15 15:44:06 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 4E2ABB5DBBF for ; Wed, 15 Jun 2011 12:44:06 -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 00708-02 for ; Wed, 15 Jun 2011 15:43:59 +0000 (UTC) X-Greylist: whitelisted by SQLgrey-1.7.6 Received: from smtp4-g21.free.fr (smtp4-g21.free.fr [212.27.42.4]) by mail.postgresql.org (Postfix) with ESMTP id EBCBBB5D803 for ; Wed, 15 Jun 2011 12:43:56 -0300 (ADT) Received: from [192.168.0.17] (unknown [78.237.44.8]) by smtp4-g21.free.fr (Postfix) with ESMTP id 078164C8143 for ; Wed, 15 Jun 2011 17:43:50 +0200 (CEST) Message-ID: <4DF8D332.30909@club-internet.fr> Date: Wed, 15 Jun 2011 17:43:46 +0200 From: "F. BROUARD / SQLpro" Organization: sqlpro.developpez.com User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Merge overlapping time-periods References: In-Reply-To: Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001 X-Spam-Level: X-Archive-Number: 201106/52 X-Sequence-Number: 35518 I write a paper on this topic comparing queries for PG, SQL Server and MySQL. Can you read french ? http://blog.developpez.com/sqlpro/p9821/langage-sql-norme/agregation-d-intervalles-en-sql-1/ The worst query is the RECURSIVE one ! A + Le 15/06/2011 17:23, Jira, Marcel a écrit : > Hi! > > Although I try for some time, I am not able to write an SQL-Query that > can do the following: > > I have a very big table (let’s call it “mytable”) with information like > this: > > 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 > > There’s an ID and time periods defined by a start value (BEG) and an end > value (END) > > I want to merge all periods belonging to the same ID, iff their time > periods are overlapping or in a direct sequence. > > Therefore the result should somehow look 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 “WITH RECURSIVE” but I didn’t succeed. > > My server is PostgreSQL 8.4. Unfortunately I can’t do anything like > update or install some fancy module… > > Thank you for your help! > > Best regards, > > Marcel Jira > -- Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence Audit, conseil, expertise, formation, modélisation, tuning, optimisation *********************** http://www.sqlspot.com ************************* From oliveiros.cristina@marktest.pt Wed Jun 15 16:38:00 2011 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
To: 'pgsql-sql@postgresql.org'=

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-- From oliveiros.cristina@marktest.pt Wed Jun 15 16:50:03 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 2738EB5DBC0 for ; Wed, 15 Jun 2011 13:50:03 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 43376-02 for ; Wed, 15 Jun 2011 16:49:56 +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 75B1CB5DBC9 for ; Wed, 15 Jun 2011 13:49:55 -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 M36PV8L6; Wed, 15 Jun 2011 17:49:54 +0100 Message-ID: From: "Oliveiros d'Azevedo Cristina" To: "Oliveiros d'Azevedo Cristina" , "Jira, Marcel" , References: Subject: Re: Merge overlapping time-periods Date: Wed, 15 Jun 2011 17:49:53 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002F_01CC2B84.A1F24B70" 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/54 X-Sequence-Number: 35520 This is a multi-part message in MIME format. ------=_NextPart_000_002F_01CC2B84.A1F24B70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello again, Marcel. I tried this and it seems to work on the example you provided, iif my = understanding is correct and you want the ID=3D2 to have just one record = on final output. That makes sense to me because=20 2 2000-01-15 2000-03-31 2 2000-04-01 2000-04-15 are in direct sequence (IMHO) as much as=20 1 2000-01-01 2000-03-31 1 2000-04-01 2000-05-31 are. Isn't my understanding correct? Best, Oliveiros (SELECT x."ID",x."BEG",x."END" FROM mytable x LEFT JOIN ( SELECT a."ID" as xid ,a."BEG" as xbeg,a."END" as xend,b."ID" as = yid,b."BEG" as ybeg,b."END" as yend FROM mytable a JOIN mytable b ON a."ID" =3D b."ID" AND (( ((a."BEG",a."END") OVERLAPS (b."BEG",b."END")) OR ((b."BEG" - a."END") =3D 1)) AND (a."BEG" <> b."BEG") AND (b."END" <> a."END")) ) y ON (((yid =3D x."ID") AND (ybeg =3D x."BEG") AND (yend =3D x."END")) OR ((xid =3D x."ID") AND (xbeg =3D x."BEG") AND (xend =3D x."END"))) WHERE yid IS NULL) UNION ( SELECT x."ID",MIN(x."BEG"),MAX(x."END") FROM mytable x LEFT JOIN ( SELECT a."ID" as xid,a."BEG" as xbeg,a."END" as xend,b."ID" as = yid,b."BEG" as ybeg,b."END" as yend FROM mytable a JOIN mytable b ON a."ID" =3D b."ID" AND (( ((a."BEG",a."END") OVERLAPS (b."BEG",b."END")) OR ((a."BEG" - b."END") =3D 1)) AND (a."BEG" <> b."BEG") AND (b."END" <> a."END")) ) y ON (((yid =3D x."ID") AND (ybeg =3D x."BEG") AND (yend =3D x."END")) OR ((xid =3D x."ID") AND (xbeg =3D x."BEG") AND (xend =3D x."END"))) WHERE yid IS NOT NULL GROUP BY x."ID" ) 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_002F_01CC2B84.A1F24B70 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hello again, Marcel.

I tried this and it seems to work on = the example=20 you provided, iif my understanding is correct and you want the ID=3D2 to = have just=20 one record on final output.

That makes sense to me because=20

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

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

are in=20 direct sequence (IMHO) as much as

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

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

are.=20 Isn't my understanding correct?

Best,

Oliveiros

(SELECT = x."ID",x."BEG",x."END"
FROM=20 mytable x
LEFT JOIN
(
SELECT a."ID" as xid ,a."BEG" as = xbeg,a."END" as=20 xend,b."ID" as yid,b."BEG" as ybeg,b."END" as yend
FROM mytable = a
JOIN=20 mytable b
ON a."ID" =3D b."ID"
AND (( ((a."BEG",a."END") = OVERLAPS=20 (b."BEG",b."END"))
OR ((b."BEG" - a."END") =3D 1))
AND (a."BEG" = <>=20 b."BEG")
AND (b."END" <> a."END"))
) y
ON (((yid =3D = x."ID")
AND=20 (ybeg =3D x."BEG")
AND (yend =3D x."END"))
OR ((xid =3D = x."ID")
AND (xbeg =3D=20 x."BEG")
AND (xend =3D x."END")))

WHERE yid IS NULL)
UNION = (
SELECT=20 x."ID",MIN(x."BEG"),MAX(x."END")
FROM mytable x
LEFT = JOIN
(
SELECT=20 a."ID" as xid,a."BEG" as xbeg,a."END" as xend,b."ID" as yid,b."BEG" as=20 ybeg,b."END" as yend
FROM mytable a
JOIN mytable b
ON = a."ID" =3D=20 b."ID"
AND (( ((a."BEG",a."END") OVERLAPS (b."BEG",b."END"))
OR = ((a."BEG"=20 - b."END") =3D 1))
AND (a."BEG" <> b."BEG")
AND (b."END" = <>=20 a."END"))
) y
ON (((yid =3D x."ID")
AND (ybeg =3D = x."BEG")
AND (yend =3D=20 x."END"))
OR ((xid =3D x."ID")
AND (xbeg =3D x."BEG")
AND (xend = =3D=20 x."END")))

WHERE yid IS NOT NULL
GROUP BY=20 x."ID"
)

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
To: 'pgsql-sql@postgresql.org'= =20

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=20 by 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 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_002F_01CC2B84.A1F24B70--