View: dbo.Tax_Jurisdiction_Hierarchy_View
View definition
CREATE VIEW [dbo].[Tax_Jurisdiction_Hierarchy_View]
AS
WITH CTE
AS ( SELECT tax_jurisdiction_KEY ,
'.'+[dbo].[sf_Pad_Left](CAST(tax_jurisdiction_KEY AS NVARCHAR(MAX)),7,'0') AS tree_string ,
do_not_consolidate AS new_do_not_consolidate ,
0 AS N_level ,
0 AS is_loop
FROM [dbo].[Tax_Jurisdiction]
UNION ALL
SELECT cur.tax_jurisdiction_KEY ,
prv.tree_string + '.'
+ [dbo].[sf_Pad_Left](CAST(cur.tax_jurisdiction_KEY AS NVARCHAR(MAX)),7,'0') AS tree_string ,
CAST(CASE WHEN prv.new_do_not_consolidate = 1
THEN 1
ELSE cur.do_not_consolidate
END AS BIT) AS new_do_not_consolidate ,
prv.N_level + 1 AS N_level ,
CASE WHEN prv.tree_string LIKE '%.'
+ [dbo].[sf_Pad_Left](CAST(cur.tax_jurisdiction_KEY AS NVARCHAR(MAX)),7,'0')
+ '%' THEN 1
ELSE 0
END AS is_loop
FROM CTE AS prv
JOIN [dbo].[Tax_Jurisdiction] AS cur ON prv.tax_jurisdiction_KEY = cur.parent__tax_jurisdiction_KEY
WHERE prv.tree_string NOT LIKE '%'
+ CAST(cur.tax_jurisdiction_KEY AS NVARCHAR(MAX))
+ '%'
AND prv.is_loop = 0
)
SELECT C.tax_jurisdiction_KEY ,
C.new_do_not_consolidate AS inherited_do_not_consolidate , --The inherited value of do_not_consolidate.. if any parent is 1 then 1 else current.
C.N_level AS hierarchy_depth , --Starting at 0
CAST(C.is_loop AS BIT) AS is_loop , --Loop detection, should be 0
C.tree_string --enables loop detection
FROM ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY tax_jurisdiction_KEY ORDER BY N_level DESC ) AS RN
FROM CTE
) AS C
WHERE C.RN = 1;