Volian/SourceCode
8
0
Fork 0
Code Issues Pull Requests 1 Projects Releases Wiki Activity
SourceCode/PROMS/SQL/FindSample.sql

173 lines
9.1 KiB
SQL
Raw Permalink Blame History

This file contains invisible Unicode characters

This file contains invisible Unicode characters that are indistinguishable to humans but may be processed differently by a computer. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

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
*/
Reference in New Issue View Git Blame Copy Permalink