August 8, 2014 at 12:46 am
Dear All,
Table having data like:
(Employee) (Project) (Allocation) (Allocated_From) (Allocated_To)
(E1)(P1) ( 36) ( 2014-08-08) (2014-10-30)
(E1)(P2) ( 40) ( 2014-09-08) (2014-11-30)
Scenario-1
If i search E1 Employee From(2014-08-08) to (2014-11-30)
want output like
(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)
Scenario-2
If i search E1 Employee From(2014-05-01) to (2014-12-15)
output like
(Employee)(Allocated_From) (Allocated_To) ( Allocation)
(E1)(2014-05-01)(2014-08-07)(0)
(E1)(2014-08-08)(2014-09-07)(36)
(E1)(2014-09-08)(2014-10-30)(76)
(E1)(2014-11-01)(2014-11-30)(40)
(E1)(2014-12-01)(2014-12-15)(0)
Scenario-3
If i search E1 Employee From(2014-05-01) to (2014-10-15)
output like
(Employee) (Allocated_From) (Allocated_To) (Allocation)
(E1)(2014-05-01)(2014-08-07)(0)
(E1)(2014-08-08)(2014-09-07)(36)
(E1)(2014-09-08)(2014-10-15)(76)
Regards
Narayanan
August 8, 2014 at 2:01 am
Looks like a "groups'n'islands" scenario with the additional challenge of overlapping groups.
Solutions for this scenario can be found here.
Side note: I'm sorry for being lazy. But without ready to use sample data I just didn't feel to write any code...
August 14, 2014 at 3:00 am
Thanks for giving hint:)
Still I am facing this issues. dear all pls help any one.
August 14, 2014 at 12:10 pm
The "hint" might not have put you in the right direction.
So here's a straight advice: please read and follow the instructions provided in the first link in my signature.
Ready to use sample data together with the expected result set will help us help you.
Pleae remember, we're all volunteers here willing to help. Some of us base their decision regarding the thread they'll put their effort to on the effort the person that opened the thread did invest.
The less time you invest to provide information the less are the chances to get an coded solution.
September 2, 2014 at 6:42 am
Provide proper data
September 2, 2014 at 9:29 am
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
September 2, 2014 at 2:55 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply