Query Needed to find Intersection Periods with minimum price

Database

Query Needed to find Intersection Periods with minimum price

Hi,

I have the following table with columns:

id     fr_dt       to_dt      price

The idea is to find minimum price for overlapping periods and for non-overlapping periods, show the price. The final output should contain non-overlapping periods spanning from minimum of the fr_dt and maximum of to_dt.

e.g.

id     fr_dt                to_dt                 price

1     2014/04/18     9999/12/31       200

1     2014/04/18     9999/12/31       100

1     2014/05/09     9999/12/31       300

output

id     fr_dt                to_dt                 price

1     2014/04/18     2014/05/08       100

1     2014/05/09     9999/12/31       300

2nd example (more complex scenario)

id     fr_dt                to_dt                 price

1      2014/05/09     9999/12/31       300

1      2014/06/09     9999/12/31       200

1      2014/07/09     2014/08/09       400

output

id     fr_dt                to_dt                 price

1      2014/05/09     2014/06/08       300

1      2014/06/09     2014/07/08       200

1      2014/07/09     2014/08/09       400

1      2014/08/09     9999/12/31       200

The output now contains one extra row indicating that from 2014/08/09 till 9999/12/31, the lowest price was 200 that actually came from 2nd row (though both 1st and 2nd row in the input were active, but 2nd row had lesser price than 1st row, so it was picked)

Tags (2)
1 REPLY
Enthusiast

Re: Query Needed to find Intersection Periods with minimum price

http://manibharataraju.blogspot.in/2015/07/removing-overlaps-in-records.html

you might need to do some changes in the method given in my blog. Try it!!

Cheers,

Mani