USE VEPROMS
/* -- Children have Transitions
select * from vefn_AllHighLevelStepTransitions() where ExternalChildTransitions > 0 ;
-- 6991	`0POP05-EO-EC32`PROCEDURE STEPS`17	ExternalChildTrans = 1 `0POP05-EO-EC32`PROCEDURE STEPS`14`2`RNO``2
-->9392	`0POP05-EO-FRP1`PROCEDURE STEPS`18	ExternalChildTrans = 2 `0POP05-EO-FRP1`PROCEDURE STEPS`25`RNO``1 `0POP05-EO-FRP1`PROCEDURE STEPS`25`RNO``4
/* -- External Transitions to step and no nextstep
select II.ItemID,dbo.ve_GetShortPath(II.ItemID) Path, dbo.ve_GetChildCount(II.ItemID) ChildCount
, (Select count(*) from vefn_FindExternalTransitions(II.ItemID)) ExternalTransCount
from Items II join Transitions TT on II.ContentID = TT.FromID 
left join Items PP on II.ItemID =  PP.PreviousID 
where PP.ItemID is null AND dbo.ve_GetChildCount(II.ItemID) > 1
AND (Select count(*) from vefn_FindExternalTransitions(II.ItemID)) > 0
-->4984	`0POP05-EO-EC01`PROCEDURE STEPS`14`2 ExternalTrans = 1 `0POP05-EO-EC01`PROCEDURE STEPS`14`1`RNO``1
-- 2025	`0POP05-EO-ES11`PROCEDURE STEPS`12`2 ExternalTrans = 1 `0POP05-EO-ES11`PROCEDURE STEPS`12`1`RNO``1
-- 3308	`0POP05-EO-EO30`PROCEDURE STEPS`28`2 ExternalTrans = 1 `0POP05-EO-EO30`PROCEDURE STEPS`28`1`RNO``1
-- 6116	`0POP05-EO-EC21`PROCEDURE STEPS`24`2 ExternalTrans = 1 `0POP05-EO-EC21`PROCEDURE STEPS`24`1`RNO``1
*/
/* -- Update next item – Step with next item
select II.ItemID,dbo.ve_GetShortPath(II.ItemID) Path,dbo.ve_GetShortPath(PP.ItemID) NextPath, dbo.ve_GetChildCount(II.ItemID) ChildCount
, (Select count(*) from vefn_FindExternalTransitions(II.ItemID)) ExternalTransCount
from Items II join Transitions TT on II.ContentID = TT.FromID 
left join Items PP on II.ItemID =  PP.PreviousID 
where PP.ItemID is not null AND dbo.ve_GetChildCount(II.ItemID) > 1
AND (Select count(*) from vefn_FindExternalTransitions(II.ItemID)) > 0
-- 1406	`0POP05-EO-ES05`PROCEDURE STEPS`25`4	Next	`0POP05-EO-ES05`PROCEDURE STEPS`25`5	ChildCount=2	ExternalTrans=2
-- 2350	`0POP05-EO-ES12`PROCEDURE STEPS`12	Next	`0POP05-EO-ES12`PROCEDURE STEPS`13	ChildCount=2	ExternalTrans=2
-- 3079	`0POP05-EO-EO30`PROCEDURE STEPS`7`9	Next	`0POP05-EO-EO30`PROCEDURE STEPS`7`10	ChildCount=4	ExternalTrans=1
-->5167	`0POP05-EO-EC02`PROCEDURE STEPS`12	Next	`0POP05-EO-EC02`PROCEDURE STEPS`13	ChildCount=3	ExternalTrans=2
-- 8563	`0POP05-EO-FRH1`PROCEDURE STEPS`7	Next	`0POP05-EO-FRH1`PROCEDURE STEPS`8	ChildCount=2	ExternalTrans=2
-- 8614	`0POP05-EO-FRH1`PROCEDURE STEPS`14	Next	`0POP05-EO-FRH1`PROCEDURE STEPS`15	ChildCount=2	ExternalTrans=1
-- 9541	`0POP05-EO-FRZ1`PROCEDURE STEPS`2	Next	`0POP05-EO-FRZ1`PROCEDURE STEPS`3	ChildCount=2	ExternalTrans=2*/
/* -- Update DocVersion ItemID – First Procedure in set
select ItemID,dbo.ve_GetFolderPath(VersionID) FolderPath from DocVersions where ItemID is not null
-->1	Active Plant DataSTPNOC - South TexasEmergency ProceduresWorking Draft
-- 51	Active Plant DataSTPNOC - South TexasTest Approval with TransitionsTest Approval with Transitions
*/
/* -- Update Parts – First Section / Step / Sub-step
select * from (
select PP.ItemID,dbo.ve_GetShortPath(PP.ItemID) Path,dbo.ve_GetSiblingCount(PP.ItemID) Siblings , PP.FromType, CC.Type,
row_number()over(partition by FromType order by dbo.ve_GetSiblingCount(PP.ItemID) desc) RowNumber
from Parts PP
join Items II on PP.ItemID = II.ItemID
join Contents CC on CC.ContentID = II.ContentID
) t1 
where RowNumber < 3
order by FromType,RowNumber
-- ItemID	Path	Siblings	FromType	Type	RowNumber
-- 2969	`0POP05-EO-EO30`COVER	18	2	10001	1
-- 1145	`0POP05-EO-ES05`COVER	16	2	10001	2
-- 7682	`0POP05-EO-FRS1`PROCEDURE STEPS`15`Caution`1	3	3	20006	1
-- 7416	`0POP05-EO-EC33`PROCEDURE STEPS`32`Caution`1	3	3	20006	2
-- 4188	`0POP05-EO-EC00`PROCEDURE STEPS`1`Note`1	7	4	20007	1
-- 1014	`0POP05-EO-ES03`PROCEDURE STEPS`1`Note`1	4	4	20007	2
-- 2869	`0POP05-EO-EO20`ADDENDUM 3`2`RNO`	1	5	20040	1
-- 2863	`0POP05-EO-EO20`ADDENDUM 3`1`RNO`	1	5	20040	2
-->389	`0POP05-EO-EC21`PROCEDURE STEPS`1	46	6	20002	1
-- 224	`0POP05-EO-EO30`PROCEDURE STEPS`1	46	6	20002	2
-- 8736	`0POP05-EO-FRH1`PROCEDURE STEPS`28`2`Table`	1	7	20008	1
-- 6911	`0POP05-EO-EC32`PROCEDURE STEPS`11`2`Table`	1	7	20008	2
*/
/* -- Delete Parts – Only Step / Sub-step
select * from (
select PP.ItemID,dbo.ve_GetShortPath(PP.ItemID) Path,dbo.ve_GetSiblingCount(PP.ItemID) Siblings , PP.FromType, CC.Type,
row_number()over(partition by FromType order by dbo.ve_GetSiblingCount(PP.ItemID) desc) RowNumber
from Parts PP
join Items II on PP.ItemID = II.ItemID
join Contents CC on CC.ContentID = II.ContentID
where II.ItemID not in(Select PreviousID from Items where PreviousID is not null)
) t1 
where RowNumber < 3
order by FromType,RowNumber
-- ItemID	Path	Siblings	FromType	Type	RowNumber
-- 273	`0POP05-EO-EO00`PROCEDURE STEPS`21`Caution`1	1	3	20006	1
-- 393	`0POP05-EO-ES00`PROCEDURE STEPS`2`Caution`1	1	3	20006	2
-- 9685	`0POP05-EO-FRZ3`PROCEDURE STEPS`3`Note`1	1	4	20007	1
-- 9658	`0POP05-EO-FRZ2`PROCEDURE STEPS`1`Note`1	1	4	20007	2
-->182	`0POP05-EO-EO00`PROCEDURE STEPS`9`1`RNO`	1	5	20040	1
-- 183	`0POP05-EO-EO00`PROCEDURE STEPS`9`2`RNO`	1	5	20040	2
-- 9912	`0POP05-EO-FRI3`PROCEDURE STEPS`2`4`2`RNO``1	1	6	20024	1
-- 9914	`0POP05-EO-FRI3`PROCEDURE STEPS`2`4`1`RNO``1	1	6	20024	2
-- 383	`0POP05-EO-ES00`PROCEDURE STEPS`1`Table`	1	7	20008	1
-- 2362	`0POP05-EO-ES12`PROCEDURE STEPS`13`2`Table`	1	7	20008	2
*/
/* -- External Transitions & next step (add Annotations)
select II.ItemID,dbo.ve_GetShortPath(II.ItemID) Path,dbo.ve_GetShortPath(PP.ItemID) NextPath, dbo.ve_GetChildCount(II.ItemID) ChildCount
, (Select count(*) from vefn_FindExternalTransitions(II.ItemID)) ExternalTransCount
from Items II join Transitions TT on II.ContentID = TT.FromID 
left join Items PP on II.ItemID =  PP.PreviousID 
where PP.ItemID is not null AND dbo.ve_GetChildCount(II.ItemID) > 1
AND (Select count(*) from vefn_FindExternalTransitions(II.ItemID)) > 0
-- 1406	`0POP05-EO-ES05`PROCEDURE STEPS`25`4	`0POP05-EO-ES05`PROCEDURE STEPS`25`5	ChildCount=2	ExternalTrans=2
-- 2350	`0POP05-EO-ES12`PROCEDURE STEPS`12	`0POP05-EO-ES12`PROCEDURE STEPS`13	ChildCount=2	ExternalTrans=2
-- 3079	`0POP05-EO-EO30`PROCEDURE STEPS`7`9	`0POP05-EO-EO30`PROCEDURE STEPS`7`10	ChildCount=4	ExternalTrans=1
-->5167	`0POP05-EO-EC02`PROCEDURE STEPS`12	`0POP05-EO-EC02`PROCEDURE STEPS`13	ChildCount=3	ExternalTrans=2
-- 8563	`0POP05-EO-FRH1`PROCEDURE STEPS`7	`0POP05-EO-FRH1`PROCEDURE STEPS`8	ChildCount=2	ExternalTrans=2
-- 8614	`0POP05-EO-FRH1`PROCEDURE STEPS`14	`0POP05-EO-FRH1`PROCEDURE STEPS`15	ChildCount=2	ExternalTrans=1
-- 9541	`0POP05-EO-FRZ1`PROCEDURE STEPS`2	`0POP05-EO-FRZ1`PROCEDURE STEPS`3	ChildCount=2	ExternalTrans=2*/
/* -- Steps or Children with Annotations
select top 5 ItemID, dbo.ve_GetShortPath(ItemID) Path, Annotations from (
select ItemID , 
(select Count(*) from vefn_ChildItems(II.ItemID) CI join Annotations AA on AA.ItemID = CI.ItemID) Annotations 
from vefn_AllHighLevelSteps() II
) T1
ORDER BY Annotations DESC
-- ItemID	Path	Annotations
-->105	`0POP05-EO-EC00`PROCEDURE STEPS`1	1
-- 505	`0POP05-EO-ES01`PROCEDURE STEPS`6	1
-- 740	`0POP05-EO-ES02`PROCEDURE STEPS`3	1
-- 1198	`0POP05-EO-ES05`PROCEDURE STEPS`8	1
-- 1731	`0POP05-EO-EO10`PROCEDURE STEPS`13	1
*/
/* -- Steps or Children with Details
-- No Data
*/
/* -- Steps or Children with Entries
select top 5 ItemID, dbo.ve_GetShortPath(ItemID) Path
from Items II
join Entries EE on EE.ContentID = II.ContentID
-- ItemID	Path
-->377	`0POP05-EO-EO00`ADDENDUM 4
-- 1926	`0POP05-EO-EO10`ADDENDUM 4
-- 2569	`0POP05-EO-ES12`ADDENDUM 9
-- 2648	`0POP05-EO-ES13`ADDENDUM 2
-- 3756	`0POP05-EO-ES31`ADDENDUM 2
*/
/* -- Steps or Children with ROUsages
select top 5 * From ( 
select ItemID, dbo.ve_GetShortPath(ItemID) Path, (Select Count(*) from ROUsages RO where RO.ContentID = II.ContentID) ROs
From Items II 
WHERE ContentID in(Select ContentID from ROUsages)
) T1 Order by ROs Desc
-- ItemID	Path	ROs
-->4940	`0POP05-EO-EC01`PROCEDURE STEPS`9`1`RNO``1	5
-- 5115	`0POP05-EO-EC02`PROCEDURE STEPS`7`1`RNO``1	5
-- 5118	`0POP05-EO-EC02`PROCEDURE STEPS`7`1`RNO``4	5
-- 2429	`0POP05-EO-ES12`PROCEDURE STEPS`17`4	4
-- 2433	`0POP05-EO-ES12`PROCEDURE STEPS`17`8	4
*/
/* -- Steps or Children with internal Transitions
select top 5 ItemID,dbo.ve_GetShortPath(ItemID) Path, InternalTransitions 
from vefn_AllHighLevelStepTransitions() order by InternalTransitions Desc
-- ItemID	Path	InternalTransitions
-->2984	`0POP05-EO-EO30`PROCEDURE STEPS`7	5
-- 4454	`0POP05-EO-EC00`PROCEDURE STEPS`16	5
-- 774	`0POP05-EO-ES02`PROCEDURE STEPS`10	4
-- 898	`0POP05-EO-ES02`PROCEDURE STEPS`19	4
-- 1064	`0POP05-EO-ES03`PROCEDURE STEPS`6	4
*/
/* -- Steps or Children with Parts
select top 5 * from (
select ItemID,dbo.ve_GetShortPath(ItemID) Path ,ExternalTransitions, ExternalChildTransitions, dbo.ve_GetChildCount(ItemID) Children
from vefn_AllHighLevelStepTransitions() 
) T1 order by Children Desc
-- ItemID	Path	ExternalTransitions	ExternalChildTransitions	Children
-->5624	`0POP05-EO-EC12`PROCEDURE STEPS`5	3	0	74
-- 389	`0POP05-EO-EC21`PROCEDURE STEPS`1	2	0	70
-- 7528	`0POP05-EO-FRS1`PROCEDURE STEPS`4	1	0	66
-- 5645	`0POP05-EO-EC12`PROCEDURE STEPS`4	1	0	65
-- 4840	`0POP05-EO-EC01`PROCEDURE STEPS`4	2	0	64
*/
/* -- Steps or Children with XContents
--Any will do
*/
/* -- Steps or Children with PreviousID
--Steps with Children
*/
/* -- Steps or Children with Items
--Any will do
*/
/* -- Steps or Children with Contents
--Any will do
*/