Oracle SQL: Detecting breaks in continual spans

Question

I have the following table and I'm trying to detect products that have a break in its spans.

Product     | unit_Cost | price start date |    price end date
--------------------------------------------------------------------------
product 1     15.00         01/01/2011      03/31/2011
product 1     15.00         04/01/2011      06/31/2011
product 1     15.00         07/01/2011      09/31/2011
product 1     15.00         10/01/2011      12/31/2011

product 2     10.00         01/01/2011      12/31/2011

product 3     25.00         01/01/2011      06/31/2011
product 3     25.00         10/01/2011      12/31/2011

So here I want it to report back product3 because we are missing the span

07/01/2011 - 09/31/2011

Any ideas on how I can do this?

EDIT: Oracle Ver: 10g

Create Table Statement

CREATE TABLE Sandbox.TBL_PRODUCT
(
  PRODUCT_ID        VARCHAR2(13 BYTE),   
  PRODUCT           VARCHAR2(64 BYTE),
  UNIT_COST         NUMBER,
  PRICE_START_DATE  DATE,
  PRICE_END_DATE    DATE
)

EDIT 2 start dates and end dates cannot overlap

EDIT 3 a span can be any two dates as long as price_end_date >= price_start_date. Equal is included since a product can be on sale for one day.

Answer 1

Try this (using LEAD analytic function):

SELECT *
  FROM (
                SELECT a.*, LEAD(price_start_date,1,NULL) OVER(PARTITION BY product ORDER BY price_end_date) next_start_date 
         FROM Product a
       )
WHERE (price_end_date + 1)<> next_start_date

Example with Setup

        CREATE TABLE PRODUCT
          (
            PRODUCT   VARCHAR2(100 BYTE),
            UNIT_COST NUMBER,
            START_DATE DATE,
            END_DATE DATE
          );

        INSERT INTO Product VALUES('product 1','15.00',TO_DATE('01/01/2011','MM/DD/RRRR'),TO_DATE('03/31/2011','MM/DD/RRRR'));
        INSERT INTO Product VALUES('product 1','15.00',TO_DATE('04/01/2011','MM/DD/RRRR'),TO_DATE('06/30/2011','MM/DD/RRRR'));
        INSERT INTO Product VALUES('product 1','15.00',TO_DATE('07/01/2011','MM/DD/RRRR'),TO_DATE('09/30/2011','MM/DD/RRRR'));
        INSERT INTO Product VALUES('product 1','15.00',TO_DATE('10/01/2011','MM/DD/RRRR'),TO_DATE('12/31/2011','MM/DD/RRRR'));
        INSERT INTO Product VALUES('product 2','10.00',TO_DATE('01/01/2011','MM/DD/RRRR'),TO_DATE('12/31/2011','MM/DD/RRRR'));
        INSERT INTO Product VALUES('product 3','25.00',TO_DATE('01/01/2011','MM/DD/RRRR'),TO_DATE('06/30/2011','MM/DD/RRRR'));
        INSERT INTO Product VALUES('product 3','25.00',TO_DATE('10/01/2011','MM/DD/RRRR'),TO_DATE('12/31/2011','MM/DD/RRRR'));

SELECT *
  FROM (
                SELECT a.*, LEAD(start_date,1,NULL) OVER(PARTITION BY product ORDER BY start_date) next_start_date 
                 FROM Product a
              )
WHERE (end_date + 1)<> next_start_date

EDIT:Updated the query to consider the next start_date and current end_date to avoid issues with the distribution of the data.

Answer 2

You could also use this technique. It uses an inner query (chronological_record) to assign a rank to each record in the TBL_PRODUCT table (the rank being sorted on start_date within each product).

WITH
  chronological_record AS
  (
    SELECT
      product,
      unit_cost,
      start_date,
      end_date,
      (DENSE_RANK() OVER (PARTITION BY product ORDER BY start_date))
          AS chronological_order
    FROM
      TBL_PRODUCT
  )

SELECT
  earlier.product,
  (earlier.end_date + 1) AS missing_period_start_date,
  (later.start_date - 1) as missing_period_end_date
FROM
  CHRONOLOGICAL_RECORD earlier
  INNER JOIN
  CHRONOLOGICAL_RECORD later
    ON
        earlier.product = later.product
      AND
        (earlier.chronological_order + 1) = later.chronological_order
WHERE
  (earlier.end_date + 1) <> later.start_date

In your example, the subquery (chronological_record) would yield something like this:

Product   | unit_Cost | start date | end date   | chronological_order
--------------------------------------------------------------------------
product 1    15.00      01/01/2011   03/31/2011    1
product 1    15.00      04/01/2011   06/31/2011    2
product 1    15.00      07/01/2011   09/31/2011    3
product 1    15.00      10/01/2011   12/31/2011    4

product 2    10.00      01/01/2011   12/31/2011    1

product 3    25.00      01/01/2011   06/31/2011    1
product 3    25.00      10/01/2011   12/31/2011    2

The main query's INNER JOIN effectively matches earlier records up with their next (chronologically speaking) records.