Identifying/Counting Unique Claims

Question

I need your help to get a count of unique claims from a table where claim_id and claim_id2 can occasionally crisscross

So for example, given a table with the following rows:

Table scripts:

create table claim_table
(
 id int identity(1,1),
 claim_id varchar(20) not null,
 claim_id2 varchar(20),
 claim_status varchar(20),
 member_id varchar(20) not null
)
;
insert into claim_table (claim_id, claim_id2, claim_status, member_id) values ('1234','','PAID','A');
insert into claim_table (claim_id, claim_id2, claim_status, member_id) values ('2314','7654','REVERSED','A');
insert into claim_table (claim_id, claim_id2, claim_status, member_id) values ('5653','1209','REVERSED','B');
insert into claim_table (claim_id, claim_id2, claim_status, member_id) values ('1209','5653','REVERSED','B');

In this scenario, the count of claims is: 3 because the last 2 recs are for a single claim. And for these 2 recs, I'd like the original claim_id, 5653, be the one identified in the count of claims

So the final result will be claim_ids - 1234, 2314 and 5653 making up the 3 claims

I was trying to using an outer join but it's not giving me the desired results

select  a.claim_id a_claim_id      
        ,b.claim_id b_claim_id     
        --sum (case when a.claim_id = b.claim_id then 0 else 1 end) clm_cnt
from claim_table a
join 
  (select id, member_id, claim_id, claim_id2, claim_status, row_number() over(partition by claim_id order by id)
   from claim_table) b
  on a.member_id = b.member_id
  and a.id = b.id+1
;

p.s. I changed this query a few times and the version you're seeing here is a bit of a confused mess

THNXS in advanced for the assist!

Gar

Answer 1

I mocked up your data a little more simply:

DECLARE @claimIDS TABLE (ID INT IDENTITY, Claim1 INT, Claim2 INT)
INSERT INTO @claimIDs (Claim1, Claim2) VALUES
(1234, NULL),
(2314, 7654),
(5653, 1209),
(1209, 5653)

Using this we just need to determine which claim is lower and which is higher so we can order them. You can order them quite simply with a CASE statement.

SELECT CASE WHEN c1.claim1 < c2.claim1 OR c2.claim1 IS NULL THEN c1.claim1 ELSE c2.claim1 END AS claim1,
       CASE WHEN c1.claim2 > c2.claim2 OR c2.claim2 IS NULL THEN c1.claim2 ELSE c2.claim2 END AS claim2,
       MIN(c1.ID) AS ID, COUNT(c1.ID) AS TotalClaims
  FROM @claimIDS c1
    LEFT OUTER JOIN @claimIDS c2
      ON c1.claim1 = c2.Claim2
 GROUP BY CASE WHEN c1.claim1 < c2.claim1 OR c2.claim1 IS NULL THEN c1.claim1 ELSE c2.claim1 END,
       CASE WHEN c1.claim2 > c2.claim2 OR c2.claim2 IS NULL THEN c1.claim2 ELSE c2.claim2 END

Which produces the following result. ID is the lowest ID number of the set and TotalClaims is the number of claims within the set.

claim1  claim2  ID  TotalClaims
-------------------------------
1234    NULL    1   1
1209    5653    3   2
2314    7654    2   1