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