Sql: Find Continuous Date Ranges Across Multiple Rows?

I'm trying to get a start and end date combination for continuous spans of time worked. The spans can cross multiple rows, where the end date of the first row is the same as the en

Solution 1:

Sample data

createtable tbl (person int, startdate datetime, enddate datetime, hours decimal(10,2));
insert tbl values
(5163 ,'2013-04-29 07:00:00.000' ,'2013-04-29 11:00:00.000', 4.00),
(5163 ,'2013-04-29 11:30:00.000' ,'2013-04-29 15:30:00.000', 4.00),
(5163 ,'2013-04-29 15:30:00.000' ,'2013-04-29 19:06:00.000', 3.60),
(5851 ,'2013-05-02 19:00:00.000' ,'2013-05-02 23:00:00.000', 4.00),
(5851 ,'2013-05-02 23:00:00.000' ,'2013-05-03 00:00:00.000', 1.00),
(5851 ,'2013-05-03 00:00:00.000' ,'2013-05-03 00:31:00.000', 0.52);

The query

;with cte as (
    select person, startdate, enddate, hours
    from tbl
    unionallselect t.person, cte.startdate, t.enddate, cast(cte.hours + t.hours asdecimal(10,2))
    from cte
    join tbl t on cte.person = t.person and cte.enddate = t.startdate
), cte2 as (
    select*, rn =row_number() over (partitionby person, enddate orderby startdate)
    from cte
)
select person, startdate, max(enddate) enddate, max(hours) hours
from cte2
where rn=1groupby person, startdate
orderby person, startdate;

Results

personstartdateenddatehours----------------------------------------------------------------5163        2013-04-29 07:00:00.000 2013-04-29 11:00:00.000 4.005163        2013-04-29 11:30:00.000 2013-04-29 19:06:00.000 7.605851        2013-05-02 19:00:00.000 2013-05-03 00:31:00.000 5.52