All Forums Database
rajneesharora80 1 post Joined 09/15
10 Sep 2015
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)
 

manib0907 61 posts Joined 04/15
10 Sep 2015

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

Cheers,
Mani

You must sign in to leave a comment.