• I believe there is a better way to this.

    --(Employee) (Allocated_From) (Allocated_To) (Allocation)

    --(E1) (2014-08-08 ) (2014-09-07) (36)

    --(E1) (2014-09-08 ) (2014-10-30) (76)

    --(E1) (2014-11-01 ) ( 2014-11-30) (40)

    ;with Employee(Emp,Project,Allocation,Allocated_From,Allocated_To)

    as (select 'E1','P1',36,'2014/08/08','2014/10/30'

    union all

    select 'E1','P2',40,'2014/09/08','2014/11/30')

    select * from Employee

    order by Allocated_From

    GO

    ;with Employee(Emp,Project,Allocation,Allocated_From,Allocated_To)

    as (select 'E1','P1',36,'2014/08/08','2014/10/30'

    union all

    select 'E1','P2',40,'2014/09/08','2014/11/30') ,

    cteAnchor

    AS (

    select Emp,Allocated_From = MIN(Allocated_From), Allocated_To = MAX(Allocated_To)

    from Employee

    group by emp)

    , cteChanges AS

    (select * ,

    Sequence_id = ROW_NUMBER() OVER (

    PARTITION BY dt.emp ORDER BY dt.Allocated_From

    )

    from (select a.Emp,e.Allocated_From,a.Allocated_To,e.Allocation from cteAnchor a

    inner join Employee e

    on a.Emp = e.Emp

    and e.Allocated_To between a.Allocated_From and a.Allocated_To

    union

    select a.Emp,dateadd(day, 1, e.Allocated_To) ,a.Allocated_To,e.Allocation from cteAnchor a

    inner join Employee e

    on a.Emp = e.Emp

    and e.Allocated_To between a.Allocated_From and a.Allocated_To

    and e.Allocated_To < a.Allocated_To

    where e.Emp is not null

    )dt)

    ,CTEAllocation as

    (select c1.Emp,null as Allocation, c1.Allocated_From, Allocated_To = (

    CASE

    WHEN c2.Emp IS NULL

    THEN c1.Allocated_To

    ELSE dateadd(day, - 1, c2.Allocated_From)

    END

    ) from cteChanges c1

    LEFT OUTER JOIN cteChanges c2

    on c1.Emp = c2.Emp

    and c2.Sequence_id = c1.Sequence_id +1 )

    select t1.Emp,t1.Allocated_From,t1.Allocated_To ,sum(isnull(t2.Allocation,0)) from CTEAllocation t1

    left outer join Employee t2

    on t1.Emp = t2.Emp

    and t1.Allocated_From between t2.Allocated_From and t2.Allocated_To

    and t1.Allocated_To between t1.Allocated_From and t2.Allocated_To

    group by t1.Emp,t1.Allocated_To,t1.Allocated_From