Oracle SQL - compare date with range of dates

Question

I have problem with compare one date from partition by product_id. I need to check whether last date_to ordered by date_to desc is between date_from and date_to in range of dates.

Here's example of partition:

product_id | date_from | date_to

 1     | 2017-01-01| 2019-05-01
 1     | 2017-04-15| 2017-06-10
 1     | 2017-03-15| 2017-03-25
 1     | 2017-01-19| 2017-02-01

How can I check whether date in last row is between any range of date in partition by product_id. This order have to be intact. I tried to with LAG function but it will check only previous range, I tried with min(date_from) and max(date_to) even here will be problem because min is from first row and max from the second and the qualification is false because I need to check every range of dates by product not whole range. The solution will check:

*2017-02-01 between (first row of partition) 2017-01-01 and 2019-05-01 (TRUE)

*2017-02-01 between (second row of partition) 2017-04-15 and 2017-06-10 (FALSE)

*2017-02-01 between (third row of partition) 2017-03-15 and 2017-03-25 (FALSE)

Result: YES! The date is between some range of dates ;) OK just flag 1 will be enough :)

I will be grateful for any help you can provide and sorry for my english :)

EDIT: Current problem is how to check every date_to in order with previous range. E.g.

(second date_to) 2017-06-10 between (first row) 2017-01-01| 2019-05-01

(third date_to) 2017-03-25 between (first row) 2017-01-01| 2019-05-01 and (third date_to) 2017-03-25 between (second row) 2017-04-15| 2017-06-10

etc.

Answer 1

Try:

SELECT t1.*,
       CASE WHEN first_value( date_to ) OVER (Partition by product_id Order by date_to )
                 BETWEEN date_from AND date_to
                 THEN 'Y' ELSE 'N'
       END As my_flag
FROM table1 t1
ORDER BY date_to DESC

Demo: http://sqlfiddle.com/#!4/b351f/4

| PRODUCT_ID |             DATE_FROM |               DATE_TO | MY_FLAG |
|------------|-----------------------|-----------------------|---------|
|          1 | 2017-01-01 00:00:00.0 | 2019-05-01 00:00:00.0 |       Y |
|          1 | 2017-04-15 00:00:00.0 | 2017-06-10 00:00:00.0 |       N |
|          1 | 2017-03-15 00:00:00.0 | 2017-03-25 00:00:00.0 |       N |
|          1 | 2017-01-19 00:00:00.0 | 2017-02-01 00:00:00.0 |       Y |

Answer 2

Try this:

WITH results AS (
  SELECT
    t.product_id,
    max(case when t.min_date_to BETWEEN t.date_from AND t.date_to
      then 1
      else 0
    end) AS result
  FROM (
    SELECT
      x.*,
      min(x.date_to) over (partition by x.product_id) AS min_date_to
    FROM
      your_table x
  ) t
)
SELECT DISTINCT
  x.product_id,
  r.result
FROM
  your_table x
JOIN
  results r ON r.product_id = x.product_id;

The result will be as follow:

| PRODUCT_ID | RESULT  |
|------------|---------|
|        111 |       1 |
|        222 |       1 |
|        333 |       0 |
|        444 |       1 |
|        554 |       0 |

This query checks minimum date_to of every product with previous date ranges for that specific product. As a result you get '1' for those products which have minimum date_to matches at least one date range for this product, or '0' - in other case.