Re: This one for an SQL guru!!!

Thomas,

[Quoted] Thank you very much for your help, it is greatly appreciated.

I've studied your query, and am I right in saying that this will not work if the Product has at least one "correct" price change that is NOT [Quoted] the LAST price change.

[Quoted] It works for your data because the "correct" price change is the "latest" one for the Product, and you exclude this row with the line ...

"and eff_to < ( select max(eff_to)"

I may be missing something, if so please correct me.

Thanks again,

Pol.

In article <8fut5j$t8i$1_at_nnrp1.deja.com>,   Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> 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 );
> Table created.
>
> 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 )
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 17 2000 - 00:00:00 CEST