Please help me out in this

  • vemula.narayanan

    SSC-Addicted

    Points: 467

    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

    SSC Guru

    Points: 107049

    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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • vemula.narayanan

    SSC-Addicted

    Points: 467

    Thanks for giving hint:)

    Still I am facing this issues. dear all pls help any one.

  • LutzM

    SSC Guru

    Points: 107049

    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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • rahul.rahuzz

    Right there with Babe

    Points: 774

    Provide proper data

  • rxm119528

    SSCrazy

    Points: 2960

    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

    SSCrazy

    Points: 2960

    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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply