Find gap between start and end dates for multiple data ranges with overlaps
- by sqlint
Need to find gap between start and end dates more than 20 days for multiple data ranges with overlaps.
One Id has multiple start dates and end dates. Following Id 1 has two gaps less that 20 day. It should be considered as one range from 10/01/2012 to 10/30/2014 without any gap.
1 10/01/2012 02/01/2013
1 01/01/2013 01/31/2013
1 02/10/2013 03/31/2013
1 04/15/2013 10/30/2014
Id 2 has a gap more than 20 days between end date 01/30/2013 and start date 05/01/2013. It has to be captured.
2 01/01/2013 01/30/2013
2 05/01/2013 06/30/2014
2 07/01/2013 02/01/2014
Id 3 should be considered as one range from 01/01/2012 to 06/01/2014 without any gap. The gap between end date 02/28/2013 and start date 07/01/2013 should be ignored because range from 01/01/2012 to 01/01/2014 cavers a gap.
3 01/01/2012 01/01/2014
3 01/01/2013 02/28/2013
3 07/01/2013 06/01/2014
The cursor can do it but it works extremely slow and not acceptable.
SQL fiddle http://sqlfiddle.com/#!3/27e3f/2/0
