Oracle SQL - compare date with range of dates -

i have problem compare 1 date partition product_id. need check whether last date_to ordered date_to desc between date_from , 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 check whether date in last row between range of date in partition product_id. order have intact. tried lag function check previous range, tried min(date_from) , max(date_to) here problem because min first row , max second , qualification false because need check every range of dates product not whole range. solution check:

*2017-02-01 between (first row of partition) 2017-01-01 , 2019-05-01 (true)

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

*2017-02-01 between (third row of partition) 2017-03-15 , 2017-03-25 (false)

result: yes! date between range of dates ;) ok flag 1 enough :)

i grateful can provide , sorry english :)

edit: current problem how check every date_to in order 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 , (third date_to) 2017-03-25 between (second row) 2017-04-15| 2017-06-10

etc.

try:

select t1.*,        case when first_value( date_to ) on (partition product_id order date_to )                  between date_from , date_to                  'y' else 'n'        end my_flag table1 t1 order 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 |