Page Comparison

WITH ChildOrgList As (
	SELECT
		CASE
			WHEN LEN(ChildOrg.[CHART_CD],ChildOrg.[ORG_ROLLUPUC_LEVEL06LEVEL_CD]) = 4 THEN 6
			WHEN LEN(, ChildOrg.[ORG_CD],ChildOrg.[ORG_NM],
		ChildOrg.[ORG_ROLLUPPARENT_LEVEL05ORG_CD]) = 4 THEN 5
			WHEN LEN(, ChildOrg.[ORG_ROLLUP_LEVEL04LEVEL01_CD]) = 4 THEN 4
			WHEN LEN(, ChildOrg.[ORG_ROLLUP_LEVEL03LEVEL02_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.[CHART_CD],ChildOrg.[ORG_ROLLUPUC_LEVEL06LEVEL_CD]) = 4 THEN 6
			WHEN LEN(, ChildOrg.[ORG_ROLLUP_LEVEL05_CD]) = 4 THEN 5CD],ChildOrg.[ORG_NM],
			WHEN LEN(ChildOrg.[ORG_ROLLUPPARENT_LEVEL04ORG_CD]) = 4 THEN 4
			WHEN LEN(, ChildOrg.[ORG_ROLLUP_LEVEL03LEVEL01_CD]) = 4 THEN 3
			WHEN LEN(, ChildOrg.[ORG_ROLLUP_LEVEL02_CD]) = 4 THEN 2,
			WHEN LEN(ChildOrg.[ORG_ROLLUP_LEVEL01LEVEL03_CD]) = 4 THEN 1
		END as ORG_LEVEL, 		ChildOrg.[CHART[ORG_ROLLUP_LEVEL04_CD], ChildOrg.[ORG_UCROLLUP_LEVELLEVEL05_CD],
		ChildOrg.[ORG_CD],ChildOrg.[ORG_NM],
		ROLLUP_LEVEL06_CD]
	FROM [dwhs_ods].[dbo].[kfs_d_org] as ChildOrg
	INNER JOIN ChildOrgList as Child ON ChildOrg.[ORG_PARENT_ORG_CD], = ChildOrgChild.[ORG_ROLLUP_LEVEL01_CD], ChildOrg
)
SELECT
	CASE
		WHEN LEN(L.[ORG_ROLLUP_LEVEL02LEVEL06_CD],) = 4 THEN 6
		ChildOrgWHEN LEN(L.[ORG_ROLLUP_LEVEL03LEVEL05_CD], ChildOrg]) = 4 THEN 5
		WHEN LEN(L.[ORG_ROLLUP_LEVEL04_CD], ChildOrg) = 4 THEN 4
		WHEN LEN(L.[ORG_ROLLUP_LEVEL05LEVEL03_CD],) = 4 THEN 3
		ChildOrgWHEN LEN(L.[ORG_ROLLUP_LEVEL06LEVEL02_CD]) 	FROM [dwhs_ods].[dbo].[kfs_d_org] as ChildOrg
	INNER JOIN ChildOrgList as Child ON ChildOrg= 4 THEN 2
		WHEN LEN(L.[ORG_PARENTROLLUP_ORGLEVEL01_CD]) = Child.ORG_CD
)
SELECT
	L.[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