How can I join a table to itself to get the values needed?

So I have a table that I’m trying to join to and I’m not quite getting everything I need. So I have a table that looks like this. I want to see if there is a way to join this table to itself to get the rest, so I see that when I see the code ‘0013’ then I should be getting a code of ‘0013’ code_type of ‘9_prcdr’ and med_code_ID of 74717. I’ve tried to join this to itself, but I’m either not getting enough rows or getting too many. I’ve looked online and I see there is a function I could try which is lead or lag, but I don’t have too much experience with it. Could you help? Thank you!

patient_id claim_id  from_dt    prcdr_cd code   code_type  med_code_ID
123        961      2004-02-14  0013     0013   9_prcdr  74717  
456        214      2004-02-21  0013     NULL   NULL     NULL   
789        961      2004-01-16  0013     0013   9_prcdr  74717  
1001       212      2004-09-24  0013     NULL   NULL     NULL   
10002      291      2004-10-02  0013     NULL   NULL     NULL

This is what it should look like.

patient_id claim_id  from_dt    prcdr_cd code   code_type  med_code_ID
123        961      2004-02-14  0013     0013   9_prcdr  74717  
456        214      2004-02-21  0013     0013   9_prcdr  74717  
789        961      2004-01-16  0013     0013   9_prcdr  74717  
1001       212      2004-09-24  0013     0013   9_prcdr  74717  
10002      291      2004-10-02  0013     0013   9_prcdr  74717

And my attempt was this :

select * 
from t1
INNER JOIN t1 t2 ON t1.code =  t2.prcdr_cd
    AND t1.patient_id = t2.patient_id 
    AND t1.claim_id = t2.claim_id
    AND t1.from_dt = t2.from_dt

Answer

I suspect that you can use window functions:

select
    patient_id,
    claim_id,
    from_dt,
    prcdr_cd,
    max(code)        over(partition by prcdr_cd) code,
    max(code_type)   over(partition by prcdr_cd) code_type,
    max(med_code_ID) over(partition by prcdr_cd) med_code_ID
from t1

This assumes that there a given prcdr_cd correspond to a unique tuple of values of code, code_type and med_code_ID.

Advertisement

Answer