R: This one for an SQL guru!!!
Date: 2000/05/17
Message-ID: <8fulnc$fcm$1_at_nslave1.tin.it>#1/1
Try this and then try to explain why it works! (I named your table 'test')
select *
from test
where
product_code in (select t1.product_code
from test t1, test t2 where t1.product_code = t2.product_code and t1.effective_to_date < t2.effective_from_date-1 and not exists (select 1 from test t where t1.effective_to_date+1 between t.effective_from_date and t.effective_to_date and t.rowid != t1.rowid) and not exists (select 1 from test t where t2.effective_from_date-1 between t.effective_from_date and t.effective_to_date and t.rowid != t2.rowid) and not exists (select 1 from test t where t.effective_from_date betweent1.effective_to_date+1 and t2.effective_from_date-1)) order by product_code, effective_from_date, effective_to_date;
Marco Nicolazzo
<pol15_at_my-deja.com> wrote in message 8fufgi$cot$1_at_nnrp1.deja.com...
> 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.
Received on Wed May 17 2000 - 00:00:00 CEST