Re: checking the gaps in intervals

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