I have sample Parent Child data like below.
declare @tab table (Item varchar(10),Parent varchar(10),Priority int)
insert into @tab
SELECT 'A',NULL,3,0
UNION ALL
SELECT 'A1','A',2,1
UNION ALL
SELECT 'A2','A',2,1
UNION ALL
SELECT 'A11','A1',2,2
UNION ALL
SELECT 'A12','A1',1,2
UNION ALL
SELECT 'A21','A2',3,2
UNION ALL
SELECT 'A22','A2',2,2
select * from @tab
now i need to traverse (Leaf node to subsequent Parent node) in reverse to set its Subsequent parent node Priority based on its all children minimum priority.
for example, for Children A11 and A12 minimum priority is 1 and that 1 should be set for A1.
for Children A21 and A22 minimum priority is 2 and that 2 should be set for A2.
now A1 priority is 1
A2 priority is 2
similarly for Children A1 and A2 minimum priority is 1 and that 1 should be set for A.
so expected output as below.
SELECT 'A',NULL,1,0
UNION ALL
SELECT 'A1','A',1,1
UNION ALL
SELECT 'A2','A',2,1
UNION ALL
SELECT 'A11','A1',2,2
UNION ALL
SELECT 'A12','A1',1,2
UNION ALL
SELECT 'A21','A2',3,2
UNION ALL
SELECT 'A22','A2',2,2
Please guide me how to do it.
Thanks in advance