On 07/10/12 14:30, Jasen Betts wrote:
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;
Cunning, also much more elegant and concise than my solutions!
Cheers,
Gavin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
