Re: This one for an SQL guru!!!

My apologies if the text wraps poorly I tried to make it readable,

Select product_code, price, effective_from_date, effective_to_date From Products
Where product_code in

     (select product_code
      From Products p1
      Where exists
            (select 'Y'
             from products p2
             Where p1.product_code = p2.product_code
               AND p2.Effective_From_Date =
                       (Select Min(p3.Effective_from_Date)
                        From products p3
                        Where p3.product_code = p2.product_code
                          And p3.effective_from_Date >
p1.effective_from_Date)
               AND (p2.effective_from_date - p1.effective_to_date) > 1)

I did not do an explain plan so I dont know how this will perform on really large tables. My test table had 17K rows. Also my table had different column names so I had to translate to your example. Sorry if there are any typos.

Larry

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.
>

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