Re: checking the gaps in intervals

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