[SOLVED] Find customers who switched where they received medical care using SQL

Find customers who switched where they received medical care using SQL

I wish to identify those customers who MOST RECENTLY switched where they received their medical care over a two year period. I would like to find the last two transactions per customer over the two year period where the diagnosis code is the same but site of service is different.

For example, member 1 has visited both their physician's office and the outpatient facility many times in the two year period, but I am only interested in the last two transactions where they have switched site of service for the same diagnosis code.

As an example my data is structured as followed:

MEMBER_ID DIAGNOSIS_CD  DATE        SITE_OF_SERVICE
1         A             April       Physician
1         A             May         Physician
2         A             May         Home Infusion
1         B             May         Physician
1         A             July        Outpatient
1         A             August      Physician
1         A             September   Physician
1         A             October     Outpatient

Any help with solving this would be greatly appreciated.

I am using Sybase IQ.

Solution

You can use:

select
  member_id
from (
  select
    *,
    row_number() 
      over (partition by member_id, diagnosis_cd order by date desc) as rn
  from t
) x
where rn <= 2
group by member_id
having min(site_of_service) <> max(site_of_service)