On 2012-10-05, Anton Gavazuk <antongava...@gmail.com> 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...

perhaps you can do a with-recursive query ?

create temp table Gavazuk 
      (id serial primary key, start date ,fin date);
insert into Gavazuk (start,fin) 
values ('2012-12-01','2012-12-10')
      ,('2012-12-11','2012-12-13')
      ,('2012-12-17','2012-12-19');

-- this version treats ('2012-12-01','2012-12-10') ('2012-12-11','2012-12-13')
-- as contiguous   

with recursive a as (
   select max (fin) as f from Gavazuk  
   where ('2012-12-12') between start and fin
  union all
   select distinct (fin) from gavazuk,a 
   where a.f+1 between start and fin and start <= '2012-12-12'
)
select max(f) >= '2012-12-18' from a;

-- this version treats ('2012-12-01','2012-12-10') ('2012-12-11','2012-12-13')
-- as non-contiguous   

with recursive a as (
   select max (fin) as f from Gavazuk  
   where ('2012-12-12') between start and fin
  union all
   select distinct (fin) from gavazuk,a 
   where a.f between start and fin-1 and start <= '2012-12-12'
)
select max(f) >= '2012-12-18' from a;


-- 
⚂⚃ 100% natural



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply via email to