Does anyone have any T-SQL to test a time series recordset for overlaps or gaps in dates?

Does anyone have any SQL to test a time series, with columns start_date and end_date, for any violations of continuous dates:
   •  Overlap in Dates  -->  client_id=99, 7-1-11 to 7-15-11
   •  Gaps in dates  -->  client_id=101, 7-1-11 to 7-30-11

client_id=42 is a correct series of dates, with whatever the first start date is there are no gaps or overlaps in dates.  
Client uses 12-31-9999 as a 'High Date' for current record.

Example:
client_id, start_date, end_date
42, 1-1-2011 00:00:000, 6-30-2011 23:59:59
42, 7-1-2011 00:00:000, 11-30-2011 23:59:59
42, 12-1-2011 00:00:00, 12-31-9999 00:00:000

99, 1-1-2011 00:00:000, 7-15-2011 23:59:59
99, 7-1-2011 00:00:000, 12-31-9999 00:00:000

101, 1-1-2011 00:00:000, 6-30-2011 23:59:59
101, 8-1-2011 00:00:000, 11-30-2011 23:59:59

Thanks in advance.
Jim
LVL 67
Jim HornMicrosoft SQL Server Data DudeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Christopher GordonSenior Developer AnalystCommented:
See attached.  Basically I'm creating a unique number for each client transaction and comparing the data with current row to next row.

You can just paste entire snippet into ssms to follow along.  Hopefully you can follow my comments.
declare @client_range table (clientId int, start_date datetime, end_date datetime)


insert into @client_range values(42, '1-1-2011 00:00:000', '6-30-2011 23:59:59')

insert into @client_range values(42, '7-1-2011 00:00:000', '11-30-2011 23:59:59')
insert into @client_range values(42, '12-1-2011 00:00:00', '12-31-9999 00:00:000')

insert into @client_range values(99, '1-1-2011 00:00:000', '7-15-2011 23:59:59')
insert into @client_range values(99, '7-1-2011 00:00:000', '12-31-9999 00:00:000')

insert into @client_range values(101, '1-1-2011 00:00:000', '6-30-2011 23:59:59')
insert into @client_range values(101, '8-1-2011 00:00:000', '11-30-2011 23:59:59');

--get a unique counter for each client in order of start date end date ascending
with Ranges_With_Instance_Counters as
(
	select	
			*
	
		,	ROW_NUMBER() over (Partition By ClientId Order by start_date asc, end_date asc) as Instance_Counter
	from	@client_range
)

--compare the current row end date with the next row start date in order
--to calculate a variance using the instance_counter

--note: i'm using mintues, but you can change this to hours/days depending on your data.
, Ranges_With_Overlaps as
(
	select	base.*
		,	compare.start_date	as	Next_Start_Date
		,	abs(DATEDIFF(MINUTE, base.end_date, compare.start_date)) as Abs_Time_Variance
			
	from	Ranges_With_Instance_Counters base

	--compare dates with previous rows
	left outer join Ranges_With_Instance_Counters compare on
			base.clientId = compare.clientId
		and	base.Instance_Counter = (compare.Instance_Counter - 1)
)

--return set of all client transactions that have a overlap.
select	*
from	Ranges_With_Overlaps
where	Time_Variance > 1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Data DudeAuthor Commented:
:: looking ::
Jim HornMicrosoft SQL Server Data DudeAuthor Commented:
Outstanding!
Jim HornMicrosoft SQL Server Data DudeAuthor Commented:
Here's the solution, based on your code, and some cosmetic changes.  Thanks again.  -Jim
______________

SET ANSI_WARNINGS OFF
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
      DROP TABLE #tmp
GO

CREATE TABLE #tmp (clientId int, start_date datetime, end_date datetime)
GO

insert into #tmp values(42, '1-1-2011 00:00:000', '6-30-2011 23:59:59')
insert into #tmp values(42, '7-1-2011 00:00:000', '11-30-2011 23:59:59')
insert into #tmp values(42, '12-1-2011 00:00:00', '12-31-9999 00:00:000')
insert into #tmp values(99, '1-1-2011 00:00:000', '7-15-2011 23:59:59')
insert into #tmp values(99, '7-1-2011 00:00:000', '12-31-9999 00:00:000')
insert into #tmp values(101, '1-1-2011 00:00:000', '6-30-2011 23:59:59')
insert into #tmp values(101, '8-1-2011 00:00:000', '11-30-2011 23:59:59');
GO

-- Get a unique counter for each client in order of start date-end date ascending
with Ranges_With_Instance_Counters as
(
      SELECT *, ROW_NUMBER() over (Partition By ClientId Order by start_date asc, end_date asc) as Instance_Counter
      FROM #tmp
)

-- Compare the current row end date with the next row start date in order to calculate a variance using the instance_counter

, Ranges_With_Overlaps as
(
      SELECT      base.*
            ,      compare.start_date as Next_Start_Date
            ,      DATEDIFF(SECOND, base.end_date, compare.start_date) as time_variance
      FROM      Ranges_With_Instance_Counters base
      -- Compare dates with previous rows
      LEFT OUTER JOIN Ranges_With_Instance_Counters compare
            ON base.clientId = compare.clientId AND base.Instance_Counter = (compare.Instance_Counter - 1)
)

-- Return set of all client transactions that have a overlap.
select      
      clientId,
      CASE WHEN time_variance > 1 then 'Gap' WHEN time_variance < 1 then 'Overlap' else 'Match' end as diff,
      start_date,
      end_date,
      Next_Start_Date,
      Instance_Counter
from      Ranges_With_Overlaps
where      ABS(time_variance)  > 1
Christopher GordonSenior Developer AnalystCommented:
Nice,  

Thanks for posting the final solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.