Em 15/12/08, Osvaldo Kussama<[email protected]> escreveu:
> 2008/12/15, Rogério A Bassete <[email protected]>:
>> Pessoal,
>>
>> dados da tabela vigencia:
>>
>> Data_Inicial Data_Final
>> 01/11/2008 03/11/2008
>> 04/11/2008 30/11/2008
>> 01/12/2008 03/12/2008
>> 05/12/2008 31/12/2008
>>
>>
>> Gostaria de um select que me retorna true caso o intervalo pesquisado
>> seja: 01/11/2008 a 30/11/2008 e false caso o intervalo seja 01/12/2008 a
>> 31/12/2008 (falta o dia 04/12/2008).
>>
Verifique se é isto:
bdteste=# CREATE TEMP TABLE foo(
bdteste(# Data_Inicial date,
bdteste(# Data_Final date);
CREATE TABLE
bdteste=#
bdteste=# INSERT INTO foo VALUES('2008-11-01'::date,
'2008-11-03'::date), ('2008-11-04'::date, '2008-11-10'::date),
('2008-12-01'::date, '2008-12-03'::date), ('2008-12-05'::date,
'2008-12-10'::date);
INSERT 0 4
bdteste=#
bdteste=# SELECT * FROM foo;
data_inicial | data_final
--------------+------------
2008-11-01 | 2008-11-03
2008-11-04 | 2008-11-10
2008-12-01 | 2008-12-03
2008-12-05 | 2008-12-10
(4 registros)
bdteste=# \set per_ini '\'2008-11-01\'::date'
bdteste=# \set per_fim '\'2008-11-10\'::date'
bdteste=#
bdteste=# SELECT
(Data_Inicial+generate_series(0,Data_Final-Data_Inicial))::date FROM
foo WHERE Data_Inicial >= :per_ini AND Data_Final <= :per_fim;
date
------------
2008-11-01
2008-11-02
2008-11-03
2008-11-04
2008-11-05
2008-11-06
2008-11-07
2008-11-08
2008-11-09
2008-11-10
(10 registros)
bdteste=#
bdteste=# SELECT (count(*) > 0) AS "Tem buraco" FROM
bdteste-# (SELECT (:per_ini+(generate_series(0,:per_fim-:per_ini)))::date
bdteste(# EXCEPT
bdteste(# SELECT
(Data_Inicial+generate_series(0,Data_Final-Data_Inicial))::date
bdteste(# FROM foo WHERE Data_Inicial >= :per_ini AND Data_Final
<= :per_fim) AS bar;
Tem buraco
------------
f
(1 registro)
bdteste=#
bdteste=# \set per_ini '\'2008-12-01\'::date'
bdteste=# \set per_fim '\'2008-12-10\'::date'
bdteste=# SELECT
(Data_Inicial+generate_series(0,Data_Final-Data_Inicial))::date FROM
foo WHERE Data_Inicial >= :per_ini AND Data_Final <= :per_fim;
date
------------
2008-12-01
2008-12-02
2008-12-03
2008-12-05
2008-12-06
2008-12-07
2008-12-08
2008-12-09
2008-12-10
(9 registros)
bdteste=#
bdteste=# SELECT (count(*) > 0) AS "Tem buraco" FROM
bdteste-# (SELECT (:per_ini+(generate_series(0,:per_fim-:per_ini)))::date
bdteste(# EXCEPT
bdteste(# SELECT
(Data_Inicial+generate_series(0,Data_Final-Data_Inicial))::date
bdteste(# FROM foo WHERE Data_Inicial >= :per_ini AND Data_Final
<= :per_fim) AS bar;
Tem buraco
------------
t
(1 registro)
Osvaldo
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
