<div class="moz-cite-prefix">On 06/10/12 11:42, Anton Gavazuk wrote:<br /></div><blockquote
cite="mid:-3205649711969780110@unknownmsgid"type="cite"><pre wrap="">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
</pre></blockquote><font face="Courier New, Courier, monospace">How about something like the following?<br /><br />
Cheers,<br/> Gavin</font><br /><font face="Courier New, Courier, monospace"><br /> DROP TABLE IF EXISTS period;<br
/><br/> CREATE TABLE period<br /> (<br /> id serial PRIMARY KEY,<br /> start_date date,<br />
end_date date<br /> );<br /><br /><br /> INSERT INTO period (start_date, end_date) VALUES<br /> ('2012-12-01',
'2012-12-10'),<br/> ('2012-12-11', '2012-12-13'),<br /> ('2012-12-17', '2012-12-19'),<br /> ('2012-12-20',
'2012-12-25');<br/><br /><br /> WITH RECURSIVE <br /> slot (start_date, end_date) AS<br /> (<br />
SELECT<br /> p1.start_date, <br /> p1.end_date<br /> FROM<br />
period p1<br /> WHERE<br /> NOT EXISTS<br /> (<br />
SELECT<br /> 1<br /> FROM<br />
periodp2<br /> WHERE<br /> p1.start_date = p2.end_date + 1<br />
)<br /> UNION ALL<br /> SELECT <br /> s1.start_date, <br />
p3.end_date<br /> FROM<br /> slot s1,<br /> period p3<br />
WHERE<br /> p3.start_date = s1.end_date + 1<br /> AND p3.end_date >
s1.end_date<br/> )<br /><br /> SELECT<br /> s3.start_date, <br /> MIN(s3.end_date)<br /> FROM<br />
slots3<br /> WHERE<br /> s3.start_date <= '2012-12-01'<br /> AND s3.end_date >= '2012-12-18'<br />
GROUPBY<br /> s3.start_date<br /> /**/;/**/</font><font face="Courier New, Courier, monospace">.</font><br /><br />