Query Helped Needed - Filtering Data Results

[lrdave36]

I have a query request that has me stuck. I think it would be easiest to explain by showing the desired results first:


recip_SSN mbr_ssn seq_nbr agency


123445555 444557777 1 NAH
123445555 444557777 2 NAH



This particular result set is what I need my query to return. I need to find all recip_SSNs that have the following:

1. Two records in the recip table
2. both records contain a mbr_ssn
3. the mbr_ssn must be the same number in both rows
4. the recip_ssn has more than one seq_nbr
5. the agency must be the same name on both rows



I've been able to find people with more than one seq_nbr and mbr_ssn, but it shows me people with two different agency names. I can't figure out how to filter to only show people with the same agency name on both rows.

Here is my query so far:

select distinct A.recip_ssn_nbr
 from dsnp.pr01_t_recip_sys  A,
 
where A.recip_type_cd in ('20', '50')
and A.BENEF_SEQ_NBR > 1
group by A.recip_ssn_nbr
having count (A.mbr_ssn_nbr) > 1

Any help would be appreciated.

 

[markros]

What is the SQL Server version? For SQL 2005+:

;with cte as (select *, row_number() over (partition by
recip_type_cd, mbr_ssn_nbr, Agency order by Seq_nbr) as Row,
count(*) over (partition by
recip_type_cd, mbr_ssn_nbr, Agency) as cntRecipies
from dsnp.pr01_t_recip_sys  A
 where A.recip_type_cd in ('20', '50') 
and A.BENEF_SEQ_NBR > 1)

select * from cte where cntRecipies = 2

PluralSight Learning Library