This one for an SQL guru!!!
From: <pol15_at_my-deja.com>
Date: 2000/05/17
Message-ID: <8fufgi$cot$1_at_nnrp1.deja.com>#1/1
EFFECTIVE_FROM_DATE DATE
EFFECTIVE_TO_DATE DATE I am trying to write a query that will return product info. where there [Quoted] [Quoted] is a gap between the "Effective To" date of one price change and the [Quoted] [Quoted] "Effective From" date of the next price change.
Date: 2000/05/17
Message-ID: <8fufgi$cot$1_at_nnrp1.deja.com>#1/1
[Quoted] [Quoted] 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 [Quoted] [Quoted] is a gap between the "Effective To" date of one price change and the [Quoted] [Quoted] "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.
[Quoted] Points to bear in mind .....
- If a Product has the above problem, then I want ALL rows for the Product returned (not just the row that triggered the problem).
- The number of rows (price changes) for each Product is variable and there is no maximum.
- The results should be ordered by Product, Eff. From, Eff. To
[Quoted] [Quoted] 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