Declare @TopSections Table
(
	VersionID int,
	ProcID int,
	ItemID int Primary Key,
	ContentID int,
	Level int,
	FormatID int,
	Type int
);
	with Itemz([VersionID], ProcID, [ItemID], [ContentID], [Level]) as 
	(Select DV.VersionID, DV.ItemID ProcID, [I].[ItemID], [I].[ContentID], 0
		FROM [Items] I
		JOIN vefn_DocVersionSplit('') DV ON I.[ItemID] = DV.[ItemID]
	Union All
	-- Children
		select Z.VersionID, Z.ProcID, I.[ItemID], I.[ContentID], Z.Level+1
		from Itemz Z
		join Parts P on P.ContentID = Z.ContentID
		join Items I on I.ItemID = P.ItemID
		join Contents C ON I.ContentID = C.contentID
		Where Type < 20000
	Union All
	-- Siblings
		select Z.VersionID, case when z.ProcID = z.ItemID then I.ItemID else Z.ProcID end ProcID, I.[ItemID], I.[ContentID], Z.Level
		from Itemz Z
		join Items I on I.PreviousID = Z.ItemID
	)
	insert into @TopSections
	select ZZ.VersionID, ZZ.ProcID, ZZ.[ItemID], ZZ.[ContentID], ZZ.[Level], VI.[FormatID], CC.Type
	 from ItemZ ZZ
	 Join vefn_GetVersionFormatItems('') VI ON ZZ.ItemID=VI.ItemID
	 Join Contents CC ON ZZ.ContentID=CC.ContentID
	 Where Level > 0
	OPTION (MAXRECURSION 10000)

DECLARE @Procedures TABLE (
	VersionID int,
	ProcID int primary key,
	PContentID int ,
	SectionStart int
)
Insert into @Procedures
Select VersionID, ItemId, ContentID, xProcedure.value('@SectionStart','int') SectionStart From
(Select VI.VersionID,VI.ItemID,VI.ContentID,Cast(Config as xml) xConfig
 from VEFN_GetVersionItems('') VI
Join Contents CC ON CC.ContentID = VI.ContentID
where type = 0) T1
outer apply xConfig.nodes('//Procedure') tProcedure(xProcedure)

DECLARE @SectionFormats TABLE
(
	FormatID int,
	Name nvarchar(50),
	Description nvarchar(255),
	Type int,
	IsStepSection	nvarchar(10),
	SectionFormat nvarchar(255),
	NumberingSequence int
)
Insert Into @SectionFormats
Select FormatID, Name, Description
,xDocStyle.value('@Index','int')+10000 Type
,xDocStyle.value('@IsStepSection','nvarchar(10)') IsStepSection
,xDocStyle.value('@Name','nvarchar(255)') SectionFormat
,xDocStyle.value('@NumberingSequence','int') NumberingSequence
 from Formats 
 outer apply data.nodes('//DocStyle') tDocStyle(xDocStyle)
where FormatID in(Select formatid from @TopSections)

DECLARE @OriginalSteps Table
(
	VersionID int,
	ProcID int,
	ItemID int,
	ContentID int,
	Level int,
	FormatID int,
	Type int
--	,OriginalSteps char(1)
)
Insert Into @OriginalSteps
Select VersionID,	ProcID,	ItemID,	ContentID,	Level,	FormatID,Type
--,isnull(xSection.value('@OriginalSteps','char(1)'),'N') OriginalSteps 
From
(Select TS.*, cast(config as xml) xConfig From @TopSections TS
Join Contents CC ON TS.ContentID=CC.ContentID) T1
Outer apply xConfig.nodes('//Section') tSection(xSection)
where isnull(xSection.value('@OriginalSteps','char(1)'),'N') ='Y'

--Select * from @OriginalSteps
Declare @MultipleOriginalSteps TABLE
(
	ProcID int primary Key,
	HowMany int
)
insert into @MultipleOriginalSteps
select ProcID, Count(*) Howmany from @OriginalSteps Group by ProcID Having count(*) > 1


select SectionStartStatus, OriginalStepStatus, Count(*) Howmany from (
Select PP.*, dbo.ve_getShortPath(SectionStart) PathToSectionStart, SSTS.ItemID, SSTS.FormatID, SSSF.IsStepSection, SSTS.Level
, isnull(OSExact.ItemID,OS.ItemID) OSItemID, MOS.ProcID MProcId,ossf.IsStepSection OSIsStepSection
,case when PP.SectionStart is null THEN 'None' 
      When SSTS.ItemID is null THEN 'NotSection'
			When SSTS.ProcID != PP.ProcID then 'NotInProc'
			When SSTS.Level > 1 then 'NotTopLevel'
			When SSSF.IsStepSection = 'false' then 'NotStepSection'
			When OSExact.ProcID is null THEN 'Different'
			else 'Exact' end SectionStartStatus
,case when isnull(OSExact.ItemID,OS.ItemID) is null then 'None'
			when MOS.ProcID is not null THEN 'MultipleOS'
			When OSSF.IsStepSection = 'false' then 'NotStepSection'
			When OSExact.ProcID is null THEN 'Different'
			else 'Exact' end OriginalStepStatus
 from @Procedures PP
Left Join @TopSections SSTS ON PP.SectionStart = SSTS.ItemID
Left Join @SectionFormats SSSF ON SSTS.FormatID= SSSF.FormatID and SSTS.Type=SSSF.Type
Left Join @MultipleOriginalSteps MOS ON pp.ProcID = MOS.ProcID 
Left Join @OriginalSteps OSExact ON OSExact.ProcID=pp.ProcID and OSExact.ItemID=pp.SectionStart
Left Join (select * from @OriginalSteps Where ProcID Not In (Select Procid from @MultipleOriginalSteps)) OS ON OS.ProcID = pp.procid
Left Join @SectionFormats OSSF ON isnull(OSExact.FormatID,OS.FormatID)= OSSF.FormatID and isnull(OSExact.Type,OS.Type)=OSSF.Type
) T1 Group by SectionStartStatus, OriginalStepStatus


Select PP.*, dbo.ve_getShortPath(SectionStart) PathToSectionStart, SSTS.ItemID, SSTS.FormatID, SSSF.IsStepSection, SSTS.Level
, isnull(OSExact.ItemID,OS.ItemID) OSItemID, MOS.ProcID MProcId,ossf.IsStepSection OSIsStepSection
,case when PP.SectionStart is null THEN 'None' 
      When SSTS.ItemID is null THEN 'NotSection'
			When SSTS.ProcID != PP.ProcID then 'NotInProc'
			When SSTS.Level > 1 then 'NotTopLevel'
			When SSSF.IsStepSection = 'false' then 'NotStepSection'
			When OSExact.ProcID is null THEN 'Different'
			else 'Exact' end SectionStartStatus
,case when isnull(OSExact.ItemID,OS.ItemID) is null then 'None'
			when MOS.ProcID is not null THEN 'MultipleOS'
			When OSSF.IsStepSection = 'false' then 'NotStepSection'
			When OSExact.ProcID is null THEN 'Different'
			else 'Exact' end OriginalStepStatus
 from @Procedures PP
Left Join @TopSections SSTS ON PP.SectionStart = SSTS.ItemID
Left Join @SectionFormats SSSF ON SSTS.FormatID= SSSF.FormatID and SSTS.Type=SSSF.Type
Left Join @MultipleOriginalSteps MOS ON pp.ProcID = MOS.ProcID 
Left Join @OriginalSteps OSExact ON OSExact.ProcID=pp.ProcID and OSExact.ItemID=pp.SectionStart
Left Join (select * from @OriginalSteps Where ProcID Not In (Select Procid from @MultipleOriginalSteps)) OS ON OS.ProcID = pp.procid
Left Join @SectionFormats OSSF ON isnull(OSExact.FormatID,OS.FormatID)= OSSF.FormatID and isnull(OSExact.Type,OS.Type)=OSSF.Type


/*
Select VersionID,GrandParentName,ParentName,FolderName,[Good],[None],[NotInProc],[NotSection],[NotTopLevel]
FROM (Select VN.*, PP.ProcID 
,case when PP.SectionStart is null THEN 'None' 
      When TS.ItemID is null THEN 'NotSection'
			When TS.ProcID != PP.ProcID then 'NotInProc'
			When Level > 1 then 'NotTopLevel'
			else 'Good' end SectionStartStatus
 from @Procedures PP 
Left Join @TopSections TS ON PP.SectionStart = TS.ItemID 
Join VEFN_GetVersionNames() VN ON VN.VersionID = PP.VersionID) P1
PIVOT(COUNT(ProcID) FOR SectionStartStatus IN ( [Good],[None],[NotInProc],[NotSection],[NotTopLevel]))
AS P2
Order By VersionID
*/