Query to find gap in begin date/end date series

[MMock]

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.

[techgnome]

here's a query I've used before to get a list of days between a range...

declare @STARTDATE datetime
declare @ENDDATE datetime

set @STARTDATE = '2013-01-01'
set @ENDDATE = '2013-01-31'
;with DATELIST as (
	select @STARTDATE as DATEITEM
	union all
	select DATEADD(day, 1, DATEITEM) as DATEITEM from DATELIST
	where DATEITEM < @ENDDATE 
)
select * from DATELIST

As is, it goes by days... but could also be set to go by months or years even...

-tg

[MMock]

I also found this (I actually googled to see if I could find my own post from what keywords I was entering and I ended up getting a lot of hit, which I should've done before I posted. I didn't realize this is a common programming problem).

CREATE TABLE Dates (
 dt DATETIME NOT NULL PRIMARY KEY);
 
INSERT INTO Dates VALUES('20081204'); 
INSERT INTO Dates VALUES('20081205');
INSERT INTO Dates VALUES('20090608');
INSERT INTO Dates VALUES('20090609');

-- missing ranges
SELECT DATEADD(DAY, 1, prev) AS start_gap,
       DATEADD(DAY, -1, next) AS end_gap,
       DATEDIFF(MONTH, DATEADD(DAY, 1, prev), 
                       DATEADD(DAY, -1, next)) AS month_diff
FROM (
SELECT dt AS prev, 
      (SELECT MIN(dt) 
       FROM Dates AS B
       WHERE B.dt > A.dt) AS next
FROM Dates AS A) AS T
WHERE DATEDIFF(DAY, prev, next) > 1

which gives you:
start_gap---------------------end_gap-------------------month_diff
2008-12-06 00:00:00.000------2009-06-07 00:00:00.000--6