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
WHEN 0 THEN 'contiguous'
WHEN 1 THEN CAST(ABS(datedifference) AS VARCHAR) + ' days overlap'
ELSE CAST(ABS(datedifference) AS VARCHAR) + ' days gap'
SELECT ScheduleID=MAX(CASE WHEN rn2 = 2 THEN ScheduleID END)
,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)
,MAX(CASE rn2 WHEN 2 THEN CalculatedEndDate ELSE [Date] END)
,MAX(CASE rn2 WHEN 2 THEN [Date] END))
,CalculatedEndDate=CASE WHEN rn2=1 THEN DATEADD(day, durationDays, [Date]) END
,rn=ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY startDate)/2
FROM Schedules a
SELECT 1, startDate UNION ALL
SELECT 2, DATEADD(day, durationDays, startDate)
) b (rn2, [Date])
GROUP BY PersonID, rn
HAVING COUNT(*) = 2
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.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]