Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Please help me out in this


Please help me out in this

Author
Message
vemula.narayanan
vemula.narayanan
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 36
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
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7021 Visits: 13559
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...



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
vemula.narayanan
vemula.narayanan
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 36
Thanks for giving hintSmile

Still I am facing this issues. dear all pls help any one.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7021 Visits: 13559
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.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
rahul.rahuzz
rahul.rahuzz
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 169
Provide proper data
rxm119528
rxm119528
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 804
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


rxm119528
rxm119528
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 804
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search