sql - Counting Policy Ages with a Query in tsql -

i have table containing insurance policies (let's call policies) in 1 field, along policies off of renewed in field:

policy_id | prior_policy_id =========================== abc       | abd       |   abc afp       | anr       |   abd brc       |   afp fkz       | 

i write query count total number of prior policies each policy, result looking this:

policy_id | num_prior_policies ============================== abc       |   0 abd       |   1 afp       |   0 anr       |   2 brc       |   1 fkz       |   0 

any suggestions appreciated.

you need recursive cte this:

with cte (       select p.policy_id, 0 num_priors       policies p       prior_policy_id null       union       select p.policy_id, 1 + cte.num_priors       cte join            policies p            on p.prior_policy_id = cte.policy_id ) select * cte; 

here sql fiddle showing working.