Re: checking the gaps in intervals

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