find gap between stop date and next start date

[atarrqis]

I have records for each employee and their positions with a start_date and stop_date.
emp start_date stop_date
123 7/1/2010 9/13/2010
123 9/14/2010 7/14/2012
123 7/16/2012 1/1/2049
256 4/1/2009 1/1/2099
753 12/1/2011 6/8/2012
753 6/9/2012 6/20/2012
I need to find if there are any gaps.
I don't really want to compare to every date from the beginning, just the difference between the stop date of one to the start_date of the next for each employee.
In this case, it should find the gap between 7/14/2012 & 7/16/2012 for emp 123.

 

[taupirho]

This is quite straightforward using analytics and the LEAD or LAG function.

You will learn so much more if you have a quick read at the documentation and have a go yourself rather than me supplying the solution


In order to understand recursion, you must first understand recursion.