Page Comparison

WITH ChildOrgList As (
	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],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],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
)
SELECT
	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
ORDER BY 1