And here is (I think) another way to do it in SQL 2005 that avoids the self-join:

SELECT ScheduleID, PersonID, startDate, durationDays

,row2StartDate, CalculatedEndDate, datedifference

,analysis=CASE SIGN(datedifference)

WHEN 0 THEN 'contiguous'

WHEN 1 THEN CAST(ABS(datedifference) AS VARCHAR) + ' days overlap'

ELSE CAST(ABS(datedifference) AS VARCHAR) + ' days gap'

END

FROM

(

SELECT ScheduleID=MAX(CASE WHEN rn2 = 2 THEN ScheduleID END)

,PersonID

,startDate=MIN(startDate)

,durationDays=MAX(CASE WHEN rn2 = 2 THEN durationDays END)

,row2StartDate=MAX(CASE rn2 WHEN 2 THEN CalculatedEndDate ELSE [Date] END)

,CalculatedEndDate=MAX(CASE rn2 WHEN 2 THEN [Date] END)

,datedifference=DATEDIFF(day

,MAX(CASE rn2 WHEN 2 THEN CalculatedEndDate ELSE [Date] END)

,MAX(CASE rn2 WHEN 2 THEN [Date] END))

FROM

(

SELECT ScheduleID

,PersonID

,startDate

,durationDays

,CalculatedEndDate=CASE WHEN rn2=1 THEN DATEADD(day, durationDays, [Date]) END

,[Date]

,rn=ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY startDate)/2

,rn2

FROM Schedules a

CROSS APPLY

(

SELECT 1, startDate UNION ALL

SELECT 2, DATEADD(day, durationDays, startDate)

) b (rn2, [Date])

) a

GROUP BY PersonID, rn

HAVING COUNT(*) = 2

) a

ORDER BY PersonID;

This one assumes though that a row does not overlap two or more following rows.

The benefit of course of not doing a self-join is that the query does a single table or index scan (depending on indexing) instead of two.