>Did you run this query and compare with desired result? I can tell right away it will not give you sum of elapsed times.

When I looked at Brandon's query, I had a different reaction. I slapped my head and thought, "oh, wow! All this needed was a SELF-JOIN. So I was pretty sure his would generate the same results.

I was curious if SQL Server would generate a different execution plan. Turns out the plans are identical (and after seeing both queries side by side, I can see why). Here's my code:

select  TimePhases.name, TimePhases.phase as FromPhase, Again.Phase as ToPhase, 
       TimePhases.starttime as StartTime, Again.EndTime as EndTime , 
    DateDiff(s, TimePhases.StartTime, Again.EndTime) as Elapsed
    from  TimePhases 
	    cross apply timePhases Again 
            where TimePhases.name = again.name and TimePhases.Phase <= again.Phase    
               order  by TimePhases.Name, FromPhase, ToPhase

And here's Brandon's again:

select a.name ,a.phase as fromphase  ,b.phase as tophase,a.starttime,b.endtime,DATEDIFF( S,a.starttime,b.endtime) as elapsed
	from timephases a 	inner join timephases b
	on a.name = b.name and a.phase<=b.phase 
	order by a.name,fromphase,tophase

After seeing this, I'd likely use a JOIN instead of a CROSS APPLY (just to keep it generic). But here's the part that surprised me a bit. Even though a full scan is involved either way, the CROSS APPLY runs a bit faster - sometimes it was 60 ms verus 70 ms, but had a few times where it was 45-50 ms versus 70 ms.

I want to look into that further, more for academic curiosity - but two thumbs up for Brandon (and I'm smacking my head for not realizing all I had to do was change the CROSS APPLY to a basic join)

Brandon, does this mean I have to abandon my love for my iPad? :)