I want to find records that exist that demonstrate a certain problem we are having with some of our data. But only if it's easy to find these records. In other words, if this is at all complicated and not worth your time trying to explain it to me, that's fine. (That is because there is another developer working on another related issue which when he fixes, could possibly make this problem go away also).

So I have records with an effective date and an end date. They should be consecutive. If I select a group of these records and order them by effective date, they should go 5/1/2010 to 4/30/2011, then 5/1/2011 to 4/30/2012, then 5/1/2012 to 4/30/2013 and finally 5/1/2013 to 12/31/9999. What I want to find are the ones that go 5/1/2010 to 9/30/2010 then 5/1/2011 to 4/30/12. So I want to look at the current record and examine the next and if the next record's effective date is not equal to the current record's end date plus one day, I would pull that record as a good test case. The effective date can be anything; I chose chose 5/1 as an example.

Again, this is just a one-shot query to find data, so it doesn't have to be perfect and shouldn't take too long to develop or explain. What happened was users found examples by working with the data in the front-end, and then a manual update query was run to fix the data so it is in sequence, when what we want to do is prevent the out of sequence scenario in the first place. But I need to find bad data to understand how it got out of sequence to know where to direct the fix.