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