Page Comparison

WITH ChildOrgList As (
	SELECT
		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
		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
	INNER JOIN ChildOrgList as Child ON ChildOrg.[ORG_PARENT_ORG_CD] = Child.ORG_CD
		AND ChildOrg.[ORG_PARENT_CHART_CD] = Child.CHART_CD
	WHERE ChildOrg.CHART_CD = 'IR'
)
SELECT
	CASE
		WHEN LEN(L.[ORG_ROLLUP_LEVEL06_CD]) = 4 THEN 6
		WHEN LEN(L.[ORG_ROLLUP_LEVEL05_CD]) = 4 THEN 5
		WHEN LEN(L.[ORG_ROLLUP_LEVEL04_CD]) = 4 THEN 4
		WHEN LEN(L.[ORG_ROLLUP_LEVEL03_CD]) = 4 THEN 3
		WHEN LEN(L.[ORG_ROLLUP_LEVEL02_CD]) = 4 THEN 2
		WHEN LEN(L.[ORG_ROLLUP_LEVEL01_CD]) = 4 THEN 1
	END as 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
ORDER BY 1