CODEWITHSUNDEEP
sqlsql-servert-sql
Gene Dan
Gene Dan

Reputation: 43

Counting Policy Ages with a Query in tsql

I have a table containing insurance policies (let's call it POLICIES) in one field, along with the policies off of which they were renewed in another field:

POLICY_ID | PRIOR_POLICY_ID
===========================
ABC       |
ABD       |   ABC
AFP       |
ANR       |   ABD
BRC       |   AFP
FKZ       |

I would like to write a query to count the total number of prior policies for each policy, with the result looking like this:

POLICY_ID | NUM_PRIOR_POLICIES
==============================
ABC       |   0
ABD       |   1
AFP       |   0
ANR       |   2
BRC       |   1
FKZ       |   0

Any suggestions would be appreciated.

Upvotes: 2

Views: 70

Answers (2)

Anon
Anon

Reputation: 10908

DECLARE @data TABLE ( POLICY_ID char(3), PRIOR_POLICY_ID char(3) );

INSERT @data VALUES
  ('ABC',NULL ),('ABD','ABC'),('AFP',NULL ),
  ('ANR','ABD'),('BRC','AFP'),('FKZ',NULL );

WITH cte AS (
  SELECT POLICY_ID, 0 AS NUM_PRIOR_POLICIES
  FROM @data
  WHERE PRIOR_POLICY_ID IS NULL
  UNION ALL
  SELECT d.POLICY_ID, NUM_PRIOR_POLICIES + 1
  FROM cte c
  INNER JOIN @data d
    ON (c.POLICY_ID = d.PRIOR_POLICY_ID)
)
SELECT POLICY_ID, NUM_PRIOR_POLICIES
FROM cte
ORDER BY POLICY_ID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You need a recursive CTE for this:

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

Here is a SQL Fiddle showing it working.

Upvotes: 1

Related Questions