Page Comparison

 
   WITH ChildOrgList As (
      	SELECT
1 as OrgLevel,
		CASE
			WHEN LEN(ChildOrg.[CHART_CD],ChildOrg.[ORG_UCROLLUP_LEVELLEVEL06_CD], ChildOrg.[ORG_CD],ChildOrg.[ORG_NM],
ChildOrg.[ORG_PARENT_ORG_CD], ) = 4 THEN 6
			WHEN LEN(ChildOrg.[ORG_ROLLUP_LEVEL01LEVEL05_CD], ) = 4 THEN 5
			WHEN LEN(ChildOrg.[ORG_ROLLUP_LEVEL02LEVEL04_CD], 
) = 4 THEN 4
			WHEN LEN(ChildOrg.[ORG_ROLLUP_LEVEL03_CD], ) = 4 THEN 3
			WHEN LEN(ChildOrg.[ORG_ROLLUP_LEVEL04LEVEL02_CD], ChildOrg.[ORG_ROLLUP_LEVEL05_CD], 
) = 4 THEN 2
			WHEN LEN(ChildOrg.[ORG_ROLLUP_LEVEL06LEVEL01_CD]) = 4 THEN 1
		END as       FROM [dwhs_ods].[dbo].[kfs_d_org] as ChildOrg WHERE
ChildOrg.CHART_CD = <kfsChartCode>
and ChildOrg.ORG_CD = <kfsOrgCode>
       UNION ALL
       SELECT Child.OrgLevel+1 as OrgLevel,
ParentOrgORG_LEVEL,
		ChildOrg.[CHART_CD],ChildOrg.[ORG_UC_LEVEL_CD], ChildOrg.[ORG_CD],ChildOrg.[ORG_NM],
		ChildOrg.[ORG_PARENT_ORG_CD], ChildOrg.[ORG_ROLLUP_LEVEL01_CD], ChildOrg.[ORG_ROLLUP_LEVEL02_CD],
		ChildOrg.[ORG_ROLLUP_LEVEL03_CD], ChildOrg.[ORG_ROLLUP_LEVEL04_CD], ChildOrg.[ORG_ROLLUP_LEVEL05_CD],
		ChildOrg.[ORG_ROLLUP_LEVEL06_CD]
	FROM [dwhs_ods].[dbo].[kfs_d_org] as ChildOrg
	WHERE ChildOrg.CHART_CD = 'IR' and ChildOrg.ORG_CD = '9023'
	UNION ALL
	SELECT
		CASE
			WHEN LEN(ChildOrg.[ORG_ROLLUP_LEVEL06_CD]) = 4 THEN 6
			WHEN LEN(ChildOrg.[ORG_ROLLUP_LEVEL05_CD]) = 4 THEN 5
			WHEN LEN(ChildOrg.[ORG_ROLLUP_LEVEL04_CD]) = 4 THEN 4
			WHEN LEN(ChildOrg.[ORG_ROLLUP_LEVEL03_CD]) = 4 THEN 3
			WHEN LEN(ChildOrg.[ORG_ROLLUP_LEVEL02_CD]) = 4 THEN 2
			WHEN LEN(ChildOrg.[ORG_ROLLUP_LEVEL01_CD]) = 4 THEN 1
		END as ORG_LEVEL,
		ChildOrg.[CHART_CD],ParentOrgChildOrg.[ORG_UC_LEVEL_CD], ParentOrgChildOrg.[ORG_CD],ParentOrgChildOrg.[ORG_NM],
ParentOrg		ChildOrg.[ORG_PARENT_ORG_CD], ParentOrgChildOrg.[ORG_ROLLUP_LEVEL01_CD], ParentOrgChildOrg.[ORG_ROLLUP_LEVEL02_CD],

ParentOrg		ChildOrg.[ORG_ROLLUP_LEVEL03_CD], ParentOrgChildOrg.[ORG_ROLLUP_LEVEL04_CD], ParentOrgChildOrg.[ORG_ROLLUP_LEVEL05_CD],

ParentOrg		ChildOrg.[ORG_ROLLUP_LEVEL06_CD]
            	FROM [dwhs_ods].[dbo].[kfs_d_org] as ParentOrg
              ChildOrg
	INNER JOIN ChildOrgList as Child
               Child ON ChildChildOrg.[ORG_PARENT_ORG_CD] = ParentOrgChild.ORG_CD
    
       /*WHERE  ParentOrg.ORG_CD  <> 'UNIV'*/  )
   
SELECT
ML.MLv-L.OrgLevel+1 OrgLevNo	L.[ORG_LEVEL], L.[ORG_UC_LEVEL_CD], L.[CHART_CD], L.[ORG_CD],
	L.[ORG_NM], L.[ORG_PARENT_ORG_CD], L.[ORG_ROLLUP_LEVEL01_CD], L.[ORG_ROLLUP_LEVEL02_CD],

	L.[ORG_ROLLUP_LEVEL03_CD], L.[ORG_ROLLUP_LEVEL04_CD], L.[ORG_ROLLUP_LEVEL05_CD], 
L.[ORG_ROLLUP_LEVEL06_CD] 
from ChildOrgList L
    INNER JOIN
    (SELECT  [CHART[ORG_ROLLUP_LEVEL05_CD],
MAX(OrgLevel) MLv from ChildOrgList     group
by [CHART	L.[ORG_ROLLUP_LEVEL06_CD])
MLFROM  ONChildOrgList L.CHART_CD
= ML.CHART_CD order by 1ORDER BY 1