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
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--