R: This one for an SQL guru!!!

From: Marco Nicolazzo <marco.nicolazzo_at_niva.it>
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 between

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