Re: This one for an SQL guru!!!
Date: 2000/05/17
Message-ID: <8fut5j$t8i$1_at_nnrp1.deja.com>#1/1
[Quoted] In article <8fufgi$cot$1_at_nnrp1.deja.com>,
pol15_at_my-deja.com wrote:
> I have a Product Prices table as follows .....
>
> PRODUCT_CODE NUMBER
> PRICE NUMBER
> EFFECTIVE_FROM_DATE DATE
> EFFECTIVE_TO_DATE DATE
>
> I am trying to write a query that will return product info. where
there
> is a gap between the "Effective To" date of one price change and the
> "Effective From" date of the next price change.
>
> For example ...
>
> Product Price Eff. From Eff. To
> 12345 12.99 15-JUL-99 29-AUG-99
> 12345 15.99 14-SEP-99 01-NOV-99
>
> In the above example, there is a period of time where the product does
> not have a price, ie. between 30-AUG-99 and 13-SEP-99 inclusive. This
> is a problem, and therefore it is rows like these I am interested in.
>
> Points to bear in mind .....
>
> 1. If a Product has the above problem, then I want ALL rows for the
> Product returned (not just the row that triggered the problem).
>
> 2. The number of rows (price changes) for each Product is variable and
> there is no maximum.
>
> 3. The results should be ordered by Product, Eff. From, Eff. To
>
> Good Luck to anyone attempting this one!!!!!
>
> Thanks in advance for any help.
>
> Pol.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
ops$tkyte_at_8i> create table p ( product_code int, price number, eff_from
date, eff_to date );
[Quoted] Table created.
[Quoted] ops$tkyte_at_8i> alter session set nls_date_format = 'DD-MON-RR';
ops$tkyte_at_8i> insert into p values ( 12345, 12.99 ,'15-JUL-
99','29-AUG-99' );
ops$tkyte_at_8i> insert into p values ( 12345, 15.99 ,'14-SEP-
99','01-NOV-99' );
ops$tkyte_at_8i> insert into p values ( 12345, 15.99 ,'02-NOV-
99','01-JAN-00' );
ops$tkyte_at_8i> insert into p values ( 11111, 15.99 ,'30-AUG-
99','01-NOV-99' );
ops$tkyte_at_8i> insert into p values ( 11111, 12.99 ,'02-NOV-
99','29-AUG-00' );
ops$tkyte_at_8i> select *
2 from p
3 where product_code in
4 ( select product_code
5 from p 6 where not exists ( select null 7 from p p2 8 where p2.eff_from = p.eff_to+1 9 and p2.product_code = p.product_code ) [Quoted] 10 and eff_to < ( select max(eff_to) 11 from p p3 12 where p3.product_code = p.product_code )13 )
14 order by product_code, eff_from, eff_to 15 /
PRODUCT_CODE PRICE EFF_FROM EFF_TO
------------ ---------- --------- ---------
12345 12.99 15-JUL-99 29-AUG-99
12345 15.99 14-SEP-99 01-NOV-99
12345 15.99 02-NOV-99 01-JAN-00
-- Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://osi.oracle.com/~tkyte/index.html Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.Received on Wed May 17 2000 - 00:00:00 CEST