sql - 查找使用SQL切换到医疗服务地点的客户(Find customers who switched where they received medical care using SQL)

Question

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.

(例如，成员1在两年期间多次访问了他们的医师办公室和门诊设施，但我只对最近两次交易感兴趣，因为他们已将服务地点切换为相同的诊断代码。)

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.

(我正在使用Sybase IQ。)

  ask by TheGoat translate from so

Answer 1

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)