Activity grouping and selection according to order and completion requirements
up vote
1
down vote
favorite
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];
sql sql-server
edited 2 days ago
200_success
127k15148410
asked 2 days ago
user184607
61
New contributor
user184607 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
1
down vote
favorite
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];
sql sql-server
edited 2 days ago
200_success
127k15148410
asked 2 days ago
user184607
61
New contributor
user184607 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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];
sql sql-server
edited 2 days ago
200_success
127k15148410
asked 2 days ago
user184607
61
New contributor
user184607 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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];
sql sql-server
sql sql-server
edited 2 days ago
200_success
127k15148410
asked 2 days ago
user184607
61
New contributor
user184607 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 2 days ago
200_success
127k15148410
asked 2 days ago
user184607
61
New contributor
user184607 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 2 days ago
200_success
127k15148410
edited 2 days ago
200_success
127k15148410
edited 2 days ago
200_success
127k15148410
127k15148410
asked 2 days ago
user184607
61
New contributor
user184607 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
user184607
61
asked 2 days ago
user184607
61
61
New contributor
user184607 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
user184607 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
user184607 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
user184607 is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
user184607 is a new contributor. Be nice, and check out our Code of Conduct.
user184607 is a new contributor. Be nice, and check out our Code of Conduct.
user184607 is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f207584%2factivity-grouping-and-selection-according-to-order-and-completion-requirements%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
