SQL find effective price of the products based on the date

Question

I have a table with four columns : id,validFrom,validTo and price. This table contains the price of an article and the duration when that price is effective.

| id| validFrom |  validTo  | price
|---|-----------|-----------|---------  
| 1 | 01-01-17  | 10-01-17  | 30000   
| 1 | 04-01-17  | 09-01-17  | 20000  

Now, for this inputs in my table my query output should be :

| id| validFrom | validTo  | price  
|---|-----------|----------|-------
| 1 | 01-01-17  | 03-01-17 | 30000  
| 1 | 04-01-17  | 09-01-17 | 20000  
| 1 | 10-01-17  | 10-01-17 | 30000  

I can compare the dates and check if products with same id have overlapping dates but I have no idea how to split those dates into non-overlapping dates. Also I am not allowed to use PL/SQL.
Is this possible using only SQL ?

Answer 1

Oracle Setup:

CREATE TABLE prices ( id, validFrom, validTo, price ) AS
  SELECT 1, DATE '2017-01-01', DATE '2017-01-10', 30000 FROM DUAL UNION ALL
  SELECT 1, DATE '2017-01-04', DATE '2017-01-09', 20000 FROM DUAL UNION ALL
  SELECT 1, DATE '2017-01-11', DATE '2017-01-15', 10000 FROM DUAL UNION ALL
  SELECT 1, DATE '2017-01-16', DATE '2017-01-18', 15000 FROM DUAL UNION ALL
  SELECT 1, DATE '2017-01-17', DATE '2017-01-20', 40000 FROM DUAL UNION ALL
  SELECT 1, DATE '2017-01-21', DATE '2017-01-24', 28000 FROM DUAL UNION ALL
  SELECT 1, DATE '2017-01-23', DATE '2017-01-26', 23000 FROM DUAL UNION ALL
  SELECT 1, DATE '2017-01-26', DATE '2017-01-26', 17000 FROM DUAL;

Query:

WITH daily_prices ( id, dt, price, duration ) AS (
  -- Unroll the price ranges to individual days
  SELECT id,
         d.COLUMN_VALUE,
         price,
         validTo - validFrom
  FROM   prices p,
         TABLE(
           CAST(
             MULTISET(
               SELECT p.validFrom + LEVEL - 1
               FROM   DUAL
               CONNECT BY p.validFrom + LEVEL - 1 <= p.validTo
             )
             AS SYS.ODCIDATELIST
           )
         ) d
),
min_daily_prices ( id, dt, price ) AS (
  -- Where a day falls between multiple ranges group them so the price
  -- is for the shortest duration offer and if there are two equally short
  -- durations then take the minimum price
  SELECT id,
         dt,
         MIN( price ) KEEP ( DENSE_RANK FIRST ORDER BY duration )
  FROM   daily_prices
  GROUP BY id, dt
),
group_changes ( id, dt, price, has_changed_group ) AS (
  -- Find when the price changes or a day is skipped which means a new price
  -- group is beginning
  SELECT id,
         dt,
         price,
         CASE WHEN dt    = LAG( dt    ) OVER ( PARTITION BY id ORDER BY dt ) + 1
              AND  price = LAG( price ) OVER ( PARTITION BY id ORDER BY dt )
              THEN 0
              ELSE 1
              END
  FROM   min_daily_prices
),
groups ( id, dt, price, grp ) AS (
  -- Calculate unique indexes (per id) for each group of price ranges
  SELECT id,
         dt,
         price,
         SUM( has_changed_group ) OVER ( PARTITION BY id ORDER BY dt )
  FROM   group_changes
)
SELECT id,
       MIN( dt ) AS validFrom,
       MAX( dt ) AS validTo,
       MIN( price ) AS price
FROM   groups
GROUP BY id, grp
ORDER BY id, validFrom;

Output:

        ID VALIDFROM            VALIDTO                   PRICE
---------- -------------------- -------------------- ----------
         1 01-JAN-2017 00:00:00 03-JAN-2017 00:00:00      30000
         1 04-JAN-2017 00:00:00 09-JAN-2017 00:00:00      20000
         1 10-JAN-2017 00:00:00 10-JAN-2017 00:00:00      30000
         1 11-JAN-2017 00:00:00 15-JAN-2017 00:00:00      10000
         1 16-JAN-2017 00:00:00 18-JAN-2017 00:00:00      15000
         1 19-JAN-2017 00:00:00 20-JAN-2017 00:00:00      40000
         1 21-JAN-2017 00:00:00 22-JAN-2017 00:00:00      28000
         1 23-JAN-2017 00:00:00 25-JAN-2017 00:00:00      23000
         1 26-JAN-2017 00:00:00 26-JAN-2017 00:00:00      17000