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; | |||