Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Please help me out in this Expand / Collapse
Author
Message
Posted Friday, August 8, 2014 12:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2014 8:07 AM
Points: 16, Visits: 35
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
Post #1601042
Posted Friday, August 8, 2014 2:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #1601065
Posted Thursday, August 14, 2014 3:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2014 8:07 AM
Points: 16, Visits: 35
Thanks for giving hint:)

Still I am facing this issues. dear all pls help any one.
Post #1603168
Posted Thursday, August 14, 2014 12:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #1603423
Posted Tuesday, September 2, 2014 6:42 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:10 AM
Points: 18, Visits: 162
Provide proper data
Post #1609593
Posted Tuesday, September 2, 2014 9:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:03 AM
Points: 293, Visits: 649

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

Post #1609669
Posted Tuesday, September 2, 2014 2:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:03 AM
Points: 293, Visits: 649
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

Post #1609811
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse