Not able to figure out how to handle the allocation. Can some one take it fwd to the final solution.
--(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') ,
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)
select c1.Emp, c2.Allocation,c1.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