1
\$\begingroup\$

The problem consists of two categories of events (Work and Rest) which together form a complete set. The number of Work events is open-ended. The number of Rest events is fixed at three (3). The events must be completed in the order as indicated for each category. Each attempt at an event is recorded as an Activity and the order of the attempt is designated as ActivityOrder. An Activity consisting of a Rest event establishes the termination of all Work events associated with the set. An Activity consisting of a Work event establishes the termination of all Rest events associated with the previous set and initiates a new set. Each (Work/Rest) set is designated as a Group.

The final result must consist of at least one (1) Group. The final result must include the ActivityOrder for only those events which were completed in order. Each Group must present the full (Work/Rest) set even if no events which qualified were completed in order. It is possible that a Group consist of no events which were completed in order.

What I have seems to do what I intend but I imagine that there is some way to produce the desired result more efficiently. Therefore, I welcome any improvements to the following code:

declare @Work table (
    ID int identity,
    [Order] tinyint,
    EventID int
    );

declare @Rest table (
    ID int identity,
    Event01 int,
    Event02 int,
    Event03 int
    );

declare @Activity table (
    ID int identity,
    EventID int,
    [Order] int,
    IsComplete bit
    );

insert @Work ([Order], EventID)
values
    (1, 4),
    (2, 9),
    (3, 1),
    (4, 3);

insert @Rest (Event01, Event02, Event03)
values
    (2, 5, 7);

--Work events must be unique
--Rest events must be unique
--Sequence of work order must initiate with 1 and be complete
--Work and Rest events may not coincide

insert @Activity (EventID, [Order], IsComplete)
values
    (3, 1, 1), --Test
    (6, 2, 1),
    (7, 3, 1), --FA
    (1, 4, 0), --Test
    (4, 5, 1), --Test
    (4, 6, 0), --Test
    (4, 7, 1), --Test
    (9, 8, 1), --Test
    (2, 9, 1), --FA
    (2, 10, 1), --FA
    (8, 11, 0),
    (5, 12, 1), --FA
    (1, 13, 1), --Test
    (7, 14, 1), --FA
    (9, 15, 0), --Test
    (5, 16, 1), --FA
    (4, 17, 1); --Test

with
    cteWR as (
        select
            'Test' as Category,
            NULL as [Type],
            [Order],
            EventID
        from @Work
        union
        select
            'FA' as Category,
            Name as [Type],
            case Name
                when 'Event01'
                    then 1
                when 'Event02'
                    then 2
                when 'Event03'
                    then 3
                end as [Order],
            Value as StationTypeID
        from (
            select
                Event01,
                Event02,
                Event03
            from @Rest
            ) as R
        unpivot (
            Value for Name in (
                Event01, Event02, Event03
                )
            ) as upR
        ),
    cteWRA as (
        select
            cteWR.Category,
            cteWR.[Type],
            cteWR.[Order],
            cteWR.EventID,
            oaA.[Order] as ActivityOrder,
            oaA.IsComplete
        from cteWR
        outer apply (
            select
                vA.[Order],
                vA.IsComplete
            from @Activity as vA
            where vA.EventID = cteWR.EventID
            ) as oaA
        ),
    cteWRA_R as (
        select
            1 as [Group],
            TestMin,
            isnull(TestMax, TestMin) as TestMax,
            FAMin,
            isnull(FAMax, FAMin) as FAMax
        from (
            select
                case
                    when WRA.TestMin > WRA.FAMin
                        then NULL
                    else WRA.TestMin
                end as TestMin,
                (select
                    sWRA.ActivityOrder
                from (
                    select
                        row_number() over (order by cteWRA.ActivityOrder desc) as RN,
                        cteWRA.ActivityOrder
                    from cteWRA
                    where 1 = 1
                        and cteWRA.Category = 'Test'
                        and WRA.TestMin < WRA.FAMin
                        and cteWRA.ActivityOrder < WRA.FAMin
                    ) as sWRA
                where sWRA.RN = 1) as TestMax,
                WRA.FAMin,
                (select
                    sWRA.ActivityOrder
                from (
                    select
                        row_number() over (order by cteWRA.ActivityOrder desc) as RN,
                        cteWRA.ActivityOrder
                    from cteWRA
                    outer apply (
                        select
                            row_number() over (order by cteWRA.ActivityOrder) as RN,
                            cteWRA.ActivityOrder
                        from cteWRA
                        where 1 = 1
                            and cteWRA.Category = 'Test'
                            and cteWRA.ActivityOrder > WRA.FAMin
                        ) as oaWRA
                    where 1 = 1
                        and cteWRA.Category = 'FA'
                        and (
                            1 <> 1
                            or (
                                oaWRA.ActivityOrder > cteWRA.ActivityOrder
                                and oaWRA.RN = 1
                                )
                            or oaWRA.RN is NULL
                            )
                    ) as sWRA
                where sWRA.RN = 1) as FAMax
            from (
                select
                    (select
                        ActivityOrder
                    from (
                        select
                            row_number() over (order by ActivityOrder) as RN,
                            ActivityOrder
                        from cteWRA
                        where Category = 'Test'
                        ) as WRA
                    where RN = 1) as TestMin,
                    (select
                        ActivityOrder
                    from (
                        select
                            row_number() over (order by ActivityOrder) as RN,
                            ActivityOrder
                        from cteWRA
                        where Category = 'FA'
                        ) as WRA
                    where RN = 1) as FAMin
                ) as WRA
            ) as WRA
        union all
        select
            [Group] + 1 as [Group],
            TestMin,
            isnull(TestMax, TestMin) as TestMax,
            FAMin,
            isnull(FAMax, FAMin) as FAMax
        from (
            select
                cteWRA_R.[Group],
                case
                    when caWRA_R.FAMin < caWRA_R.TestMin
                        then NULL
                    else caWRA_R.TestMin
                end as TestMin,
                (select
                    WRA.ActivityOrder
                from (
                    select
                        row_number() over (order by cteWRA.ActivityOrder desc) as RN,
                        cteWRA.ActivityOrder
                    from cteWRA
                    where 1 = 1
                        and cteWRA.Category = 'Test'
                        and caWRA_R.FAMin > caWRA_R.TestMin
                        and cteWRA.ActivityOrder < caWRA_R.FAMin
                    ) as WRA
                where WRA.RN = 1) as TestMax,
                caWRA_R.FAMin,
                (select
                    WRA.ActivityOrder
                from (
                    select
                        row_number() over (order by cteWRA.ActivityOrder desc) as RN,
                        cteWRA.ActivityOrder
                from cteWRA
                cross join (
                    select
                        row_number() over (order by cteWRA.ActivityOrder) as RN,
                        cteWRA.ActivityOrder
                    from cteWRA
                    where 1 = 1
                        and cteWRA.Category = 'Test'
                        and cteWRA.ActivityOrder > caWRA_R.FAMin
                    ) as cjWRA
                where 1 = 1
                    and cteWRA.Category = 'FA'
                    and (
                        1 <> 1
                        or (
                            cjWRA.ActivityOrder > cteWRA.ActivityOrder
                            and cjWRA.RN = 1
                            )
                        or cjWRA.RN is NULL
                        )
                ) as WRA
                where WRA.RN = 1) as FAMax
            from cteWRA_R
            cross apply (
                select
                    (select
                        WRA.ActivityOrder
                    from (
                        select
                            row_number() over (order by cteWRA.ActivityOrder) as RN,
                            cteWRA.ActivityOrder
                        from cteWRA
                        where 1 = 1
                            and cteWRA.Category = 'Test'
                            and cteWRA.ActivityOrder > cteWRA_R.FAMax
                        ) as WRA
                    where WRA.RN = 1) as TestMin,
                    (select
                        WRA.ActivityOrder
                    from (
                        select
                            row_number() over (order by cteWRA.ActivityOrder) as RN,
                            cteWRA.ActivityOrder
                        from cteWRA
                        where 1 = 1
                            and cteWRA.Category = 'FA'
                            and cteWRA.ActivityOrder > cteWRA_R.FAMax
                        ) as WRA
                    where WRA.RN = 1) as FAMin
                ) as caWRA_R
            where coalesce(caWRA_R.TestMin, caWRA_R.FAMin) is not NULL
            ) as WRA_R
        ),
    cteWRA_G as (
        select
            [Group],
            [Type] as Category,
            max([Min]) as [Min],
            max([Max]) as [Max]
        from (
            select
                [Group],
                [Type],
                case
                    when right(Names, 3) = 'Min'
                        then nullif(Name, 0)
                    end as [Min],
                case
                    when right(Names, 3) = 'Max'
                        then nullif(Name, 0)
                    end as [Max]
            from (
                select
                    cteWRA_R.[Group],
                    caType.Name as [Type],
                    isnull(cteWRA_R.TestMin, 0) as TestMin,
                    isnull(cteWRA_R.TestMax, 0) as TestMax,
                    isnull(cteWRA_R.FAMin, 0) as FAMin,
                    isnull(cteWRA_R.FAMax, 0) as FAMax
                from cteWRA_R
                cross apply (
                    values
                        ('Test'),
                        ('FA')
                    ) as caType (Name)
                ) as WRA_R
            unpivot (
                Name for Names in (
                    TestMin, TestMax, FAMin, FAMax
                    )
                ) as upWRA_R
            where left(Names, len([Type])) = [Type]
            ) as WRA_R
        group by
            [Group],
            [Type]
        ),
    cteWRA2 as (
        select
            [Group],
            Category,
            [Type],
            [Order],
            EventID,
            min(ActivityOrder) as ActivityOrder
        from (
            select
                cteWRA_G.[Group],
                cteWRA_G.Category,
                WRA.[Type],
                WRA.[Order],
                WRA.EventID,
                case
                    when WRA.ActivityOrder between cteWRA_G.[Min] and cteWRA_G.[Max]
                        or WRA.ActivityOrder is NULL
                    then WRA.ActivityOrder
                end as ActivityOrder
            from cteWRA_G
            join (
                select
                    cteWRA.Category,
                    cteWRA.[Type],
                    cteWRA.[Order],
                    cteWRA.EventID,
                    case
                        when isnull(ljWRA.ActivityOrder, 0) < cteWRA.ActivityOrder
                            and cteWRA.IsComplete = 1
                            then cteWRA.ActivityOrder
                    end as ActivityOrder
                from cteWRA
                left join (
                    select
                        Category,
                        [Type],
                        [Order],
                        ActivityOrder
                    from cteWRA
                    where IsComplete = 0
                    ) as ljWRA
                    on 1 = 1
                        and ljWRA.Category = cteWRA.Category
                        and (
                            ljWRA.[Type] = cteWRA.[Type]
                            or coalesce(ljWRA.[Type], cteWRA.[Type]) is NULL
                            )
                        and ljWRA.[Order] = cteWRA.[Order]
                ) as WRA
                on WRA.Category = cteWRA_G.Category
            ) as WRA2
        group by
            [Group],
            Category,
            [Type],
            [Order],
            EventID
        ),
    cteWRA_R2 as (
        select
            [Group],
            Category,
            [Type],
            [Order],
            EventID,
            ActivityOrder,
            case
                when ActivityOrder is NULL
                    then 1
                else 0
            end as IsDone
        from cteWRA2
        where [Order] = 1
        union all
        select
            cteWRA_R2.[Group],
            cteWRA_R2.Category,
            caWRA2.[Type],
            caWRA2.[Order],
            caWRA2.EventID,
            case cteWRA_R2.IsDone
                when 0
                    then caWRA2.ActivityOrder
            end as ActivityOrder,
            case
                when caWRA2.ActivityOrder is NULL
                    or cteWRA_R2.IsDone = 1
                    then 1
                else 0
            end as IsDone
        from cteWRA_R2
        cross apply (
            select
                cteWRA2.[Type],
                cteWRA2.[Order],
                cteWRA2.EventID,
                cteWRA2.ActivityOrder
            from cteWRA2
            where 1 = 1
                and cteWRA2.[Group] = cteWRA_R2.[Group]
                and cteWRA2.Category = cteWRA_R2.Category
                and cteWRA2.[Order] = cteWRA_R2.[Order] + 1

            ) as caWRA2
        )
    select
        [Group],
        Category,
        [Type],
        [Order],
        EventID,
        ActivityOrder
    from cteWRA_R2
    order by
        [Group],
        case Category
            when 'Test'
                then 1
            when 'FA'
                then 2
        end,
        [Order];
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.