Re: checking the gaps in intervals - Mailing list pgsql-sql

<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> If the periods _NEVER_ overlap, you can also use this this approach<br /> (N.B. The indexing of the
periodtable here, can be used in my previous solution where I had not considered the indexing seriously!)<br /><br />
Cheers,<br/> Gavin<br /><br /><small><small><font face="monospace">DROP TABLE IF EXISTS period;<br /> DROP TABLE IF
EXISTStarget;<br /><br /> CREATE TABLE period<br /> (<br />     start_date  date,<br />     end_date    date,<br />    
<br/>     PRIMARY KEY (start_date, end_date)<br /> );<br /><br /> CREATE INDEX ON period (end_date);<br /><br /><br />
INSERTINTO period (start_date, end_date) VALUES<br /> ('2012-11-21', '2012-11-29'),<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 /> TABLE period;<br /><br /><br /> CREATE TABLE target<br /> (<br />     start_date  date,<br />
   end_date    date<br /> );<br /><br /><br /> INSERT INTO target (start_date, end_date) VALUES<br /> ('2012-12-01',
'2012-12-01'),<br/> ('2012-12-02', '2012-12-02'),<br /> ('2012-12-09', '2012-12-09'),<br /> ('2012-12-10',
'2012-12-10'),<br/> ('2012-12-01', '2012-12-09'),<br /> ('2012-12-01', '2012-12-10'),<br /> ('2012-12-01',
'2012-12-12'),<br/> ('2012-12-01', '2012-12-13'),<br /> ('2012-12-02', '2012-12-09'),<br /> ('2012-12-02',
'2012-12-12'),<br/> ('2012-12-03', '2012-12-11'),<br /> ('2012-12-02', '2012-12-13'),<br /> ('2012-12-02',
'2012-12-15'),<br/> ('2012-12-01', '2012-12-18');<br /><br /> SELECT<br />     t.start_date,<br />     t.end_date<br />
FROM<br/>     target t<br /> ORDER BY<br />     t.start_date,<br />     t.end_date    <br /> /**/;/**/<br /><br /><br
/>SELECT<br />     t1.start_date AS "Target Start",<br />     t1.end_date AS "Target End",<br />     (t1.end_date -
t1.start_date)+ 1 AS "Duration",<br />     p1.start_date AS "Period Start",<br />     p1.end_date AS "Period End"<br />
FROM<br/>     target t1,<br />     period p1<br /> WHERE<br />     (<br />         SELECT<br />             SUM<br />
           (<br />                 CASE <br />                     WHEN p2.end_date > t1.end_date <br />
                       THEN p2.end_date - (p2.end_date - t1.end_date)<br />                         ELSE p2.end_date<br
/>                END<br />                 -<br />                 CASE <br />                     WHEN p2.start_date
<t1.start_date<br />                         THEN p2.start_date + (t1.start_date - p2.start_date)<br />
                       ELSE p2.start_date<br />                 END <br />                 + 1<br />             ) <br
/>        FROM<br />             period p2<br />         WHERE<br />                 p2.start_date <= t1.end_date<br
/>            AND p2.end_date >= t1.start_date<br />     ) = (t1.end_date - t1.start_date) + 1<br />     AND
p1.start_date<= t1.end_date<br />     AND p1.end_date >= t1.start_date<br /> ORDER BY<br />     t1.start_date,<br
/>    t1.end_date,<br />     p1<small><small><font
face="monospace">.start_date</font></small></small></font></small></small><br/><small><small><font
face="monospace">/**/;/**/<br/></font></small></small><br />