Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TKcOH-0006zn-Mp for pgsql-sql@postgresql.org; Sat, 06 Oct 2012 21:58:34 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TKcOB-0007MA-9y for pgsql-sql@postgresql.org; Sat, 06 Oct 2012 21:58:29 +0000 Received: from [10.1.1.3] (60-234-150-59.bitstream.orcon.net.nz [60.234.150.59]) (authenticated bits=0) by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id q96LwIJ4002385 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Sun, 7 Oct 2012 10:58:19 +1300 Message-ID: <5070A979.4070208@archidevsys.co.nz> Date: Sun, 07 Oct 2012 10:58:17 +1300 From: Gavin Flower Organization: ArchiDevSys User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:15.0) Gecko/20120911 Thunderbird/15.0.1 MIME-Version: 1.0 To: Anton Gavazuk CC: pgsql-sql@postgresql.org Subject: Re: checking the gaps in intervals References: <-3205649711969780110@unknownmsgid> In-Reply-To: <-3205649711969780110@unknownmsgid> Content-Type: multipart/alternative; boundary="------------040102030209000103020306" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201210/30 X-Sequence-Number: 36901 This is a multi-part message in MIME format. --------------040102030209000103020306 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 06/10/12 11:42, Anton Gavazuk wrote: > Hi dear community, > > Have probably quite simple task but cannot find the solution, > > Imagine the table A with 2 columns start and end, data type is date > > start end > 01 dec. 10 dec > 11 dec. 13 dec > 17 dec. 19 dec > ..... > > If I have interval, for example, 12 dec-18 dec, how can I determine > that the interval cannot be fully covered by values from table A > because of the gap 14-16 dec? Looking for solution and unfortunately > nothing has come to the mind yet... > > Thanks, > Anton > > If the periods _NEVER_ overlap, you can also use this this approach (N.B. The indexing of the period table here, can be used in my previous solution where I had not considered the indexing seriously!) Cheers, Gavin DROP TABLE IF EXISTS period; DROP TABLE IF EXISTS target; CREATE TABLE period ( start_date date, end_date date, PRIMARY KEY (start_date, end_date) ); CREATE INDEX ON period (end_date); INSERT INTO period (start_date, end_date) VALUES ('2012-11-21', '2012-11-29'), ('2012-12-01', '2012-12-10'), ('2012-12-11', '2012-12-13'), ('2012-12-17', '2012-12-19'), ('2012-12-20', '2012-12-25'); TABLE period; CREATE TABLE target ( start_date date, end_date date ); INSERT INTO target (start_date, end_date) VALUES ('2012-12-01', '2012-12-01'), ('2012-12-02', '2012-12-02'), ('2012-12-09', '2012-12-09'), ('2012-12-10', '2012-12-10'), ('2012-12-01', '2012-12-09'), ('2012-12-01', '2012-12-10'), ('2012-12-01', '2012-12-12'), ('2012-12-01', '2012-12-13'), ('2012-12-02', '2012-12-09'), ('2012-12-02', '2012-12-12'), ('2012-12-03', '2012-12-11'), ('2012-12-02', '2012-12-13'), ('2012-12-02', '2012-12-15'), ('2012-12-01', '2012-12-18'); SELECT t.start_date, t.end_date FROM target t ORDER BY t.start_date, t.end_date /**/;/**/ SELECT t1.start_date AS "Target Start", t1.end_date AS "Target End", (t1.end_date - t1.start_date) + 1 AS "Duration", p1.start_date AS "Period Start", p1.end_date AS "Period End" FROM target t1, period p1 WHERE ( SELECT SUM ( CASE WHEN p2.end_date > t1.end_date THEN p2.end_date - (p2.end_date - t1.end_date) ELSE p2.end_date END - CASE WHEN p2.start_date < t1.start_date THEN p2.start_date + (t1.start_date - p2.start_date) ELSE p2.start_date END + 1 ) FROM period p2 WHERE p2.start_date <= t1.end_date AND p2.end_date >= t1.start_date ) = (t1.end_date - t1.start_date) + 1 AND p1.start_date <= t1.end_date AND p1.end_date >= t1.start_date ORDER BY t1.start_date, t1.end_date, p1.start_date /**/;/**/ --------------040102030209000103020306 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 06/10/12 11:42, Anton Gavazuk wrote:
Hi dear community,

Have probably quite simple task but cannot find the solution,

Imagine the table A with 2 columns start and end, data type is date

start          end
01 dec.     10 dec
11 dec.     13 dec
17 dec.     19 dec
.....

If I have interval, for example, 12 dec-18 dec, how can I determine
that the interval cannot be fully covered by values from table A
because of the gap 14-16 dec? Looking for solution and unfortunately
nothing has come to the mind yet...

Thanks,
Anton
If the periods _NEVER_ overlap, you can also use this this approach
(N.B. The indexing of the period table here, can be used in my previous solution where I had not considered the indexing seriously!)

Cheers,
Gavin

DROP TABLE IF EXISTS period;
DROP TABLE IF EXISTS target;

CREATE TABLE period
(
    start_date  date,
    end_date    date,
   
    PRIMARY KEY (start_date, end_date)
);

CREATE INDEX ON period (end_date);


INSERT INTO period (start_date, end_date) VALUES
('2012-11-21', '2012-11-29'),
('2012-12-01', '2012-12-10'),
('2012-12-11', '2012-12-13'),
('2012-12-17', '2012-12-19'),
('2012-12-20', '2012-12-25');

TABLE period;


CREATE TABLE target
(
    start_date  date,
    end_date    date
);


INSERT INTO target (start_date, end_date) VALUES
('2012-12-01', '2012-12-01'),
('2012-12-02', '2012-12-02'),
('2012-12-09', '2012-12-09'),
('2012-12-10', '2012-12-10'),
('2012-12-01', '2012-12-09'),
('2012-12-01', '2012-12-10'),
('2012-12-01', '2012-12-12'),
('2012-12-01', '2012-12-13'),
('2012-12-02', '2012-12-09'),
('2012-12-02', '2012-12-12'),
('2012-12-03', '2012-12-11'),
('2012-12-02', '2012-12-13'),
('2012-12-02', '2012-12-15'),
('2012-12-01', '2012-12-18');

SELECT
    t.start_date,
    t.end_date
FROM
    target t
ORDER BY
    t.start_date,
    t.end_date   
/**/;/**/


SELECT
    t1.start_date AS "Target Start",
    t1.end_date AS "Target End",
    (t1.end_date - t1.start_date) + 1 AS "Duration",
    p1.start_date AS "Period Start",
    p1.end_date AS "Period End"
FROM
    target t1,
    period p1
WHERE
    (
        SELECT
            SUM
            (
                CASE
                    WHEN p2.end_date > t1.end_date
                        THEN p2.end_date - (p2.end_date - t1.end_date)
                        ELSE p2.end_date
                END
                -
                CASE
                    WHEN p2.start_date < t1.start_date
                        THEN p2.start_date + (t1.start_date - p2.start_date)
                        ELSE p2.start_date
                END
                + 1
            )
        FROM
            period p2
        WHERE
                p2.start_date <= t1.end_date
            AND p2.end_date >= t1.start_date
    ) = (t1.end_date - t1.start_date) + 1
    AND p1.start_date <= t1.end_date
    AND p1.end_date >= t1.start_date
ORDER BY
    t1.start_date,
    t1.end_date,
    p1.start_date
/**/;/**/

--------------040102030209000103020306--