主页 / coding / 问题 / 78443712
Accepted
alex
Asked: 2024-05-07 23:46:43 +0800 CST

指示 SQL 中同一 ID 列的某个日期是否早于另一个日期 < 6 个月

  • 772

我有这样的事情：

Id     Visit_Date        Visit_code      
A30     15May2004         Surgery            
A30     05FEB2005         Office             
B01     07DEC2002         Office             
B01     21NOV2002         Surgery

我需要这个，其中有一栏表明此人是否在就诊后 6 个月内接受过手术

ID     Office_Visit    SX_past_6mo
A30        1              0
B01        1              1

我正在尝试这样的事情，但子查询遇到问题

proc sql;
select case when 
case when
visit_type="Office" then 1 else 0 end as office_visit,
case when 
visit_type="Office" and
DATEDIFF(MM, visit_date, select(visit_date where visit_type="Surgery") > 6 then 1 else 0 end as sx_past_6mo
group by Id
sql

2 个回答

  1. Best Answer

    您需要将表连接到其自身。此代码为您提供每次手术以及（手术后首次就诊）和（手术后首次就诊几个月）。我不确定您是否想要在手术前或手术后去办公室，但我选择了手术后。

    select pas.Id as ID, pas.Visit_Date as SurgeryDate, Min(pao.Visit_Date) as FirstOfficeDateAfterSurgery, DATEDIFF(MM, pas.Visit_Date,Min(pao.Visit_Date)) as FirstOfficeMonthsAfterSurgery,
case 
    when isnull(Min(pao.Visit_Date),'01/01/1900') = '01/01/1900' then 0 
    when DATEDIFF(MM, pas.Visit_Date,Min(pao.Visit_Date)) <= 6 then 1 
    else 0 end as SX_past_6mo

from PatientActivity pas
left join PatientActivity pao 
    on pas.Id = pao.Id 
        and pao.Visit_code = 'Office' 
        and pao.Visit_Date > pas.Visit_Date
where pas.Visit_code = 'Surgery'
group by pas.Id, pas.Visit_Date
    • 0

  2. 根据示例数据，您可以这样总结：

    select Id,
    max(case when visit_type = 'Office' then 1 else 0 end) as Office_Visit,
    case months_between(
             max(case when visit_type = 'Surgery' then visit_date end),
             max(case when visit_type = 'Office'  then visit_date end)
         ) between 0 and 6 then 1 else 0 end as SX_past_6mo
from T
group by Id;

    在现实世界中，还会有许多其他的患者接触组合。使用子查询可能更健壮：

    select Id,
    1 as Office_Visit,
    case when exists (
        select 1 from T t2
        where t2.Id = t.Id and t2.visit_type = 'Surgery'
            and t2.visit_date < t1.visit_date
            and months_between(t2.visit_date, t1.visit_date) < 6
    ) then 1 else 0 end as SX_past_6mo
from T t
where visit_type = 'Office'
    • 0

相关问题