我正在编写代码以选择在出院后30天内死亡的患者,我的问题是,当我有一名患者在30天内的耐受范围内有多次出院时,它会退回多行!我尝试使用最大排出日期来解决此问题,该方法有效,但是当我添加额外的列时,似乎从其他行中提取了某些元素。这是我的代码:
SELECT MAX(IPS.disch_dttm) [Discharge Datetime]
,MAX(IPS.IP_SPELL_ID) [Spell ID]
,pat.PAS_ID [K Number]
,MAX(IPS.DIS_WARD_ID) [Ward ID]
,DSSU.SU_DESCRIPTION [Discharging Ward]
FROM Pat_spell AS IPS
LEFT JOIN PATIENT PAT WITH (NOLOCK) ON PAT.DIM_PATIENT_ID = IPS.DIM_PATIENT_ID
LEFT JOIN SPECIALTY SPEC WITH (NOLOCK) ON SPEC.DIM_SPECIALTY_ID = IPS.DIM_DIS_SPECT_ID
LEFT JOIN SERVICE_UNIT DSSU WITH (NOLOCK) ON IPS.DIM_DIS_WARD_ID = DSSU.DIM_SSU_ID
WHERE (IPS.DISCH_DTTM <= PAT.DEATH_DTTM + 30)
AND IPS.DIM_DIS_SPECT_ID = '7195'
AND IPS.DISCH_DTTM BETWEEN '01/01/2014' AND '30/06/2014'
GROUP BY pat.PAS_ID
,pat.DEATH_DTTM
,IPS.DIM_PATIENT_ID
,DSSU.SSU_DESCRIPTION
ORDER BY pat.PAS_ID
这是上述代码中我一直用于调试的一行的输出:
Disch Date Event_ID Unique ID Ward ID Discharging Ward
2014-06-14 8366113 A123456 77085 WardA
上面得到的病房ID正确,但是“出院”错误。此外,Event_ID与先前的出席者相对应。我要实现的目标是在死亡后30天内仅提取最近的事件,并以“事件ID”作为我的唯一ID。如果我想要多行,输出将是这样的:
Disch Date Event ID Unique ID Ward ID Discharging Ward
1 2014-06-14 8208846 A123456 77085 Ward B
2 2014-05-16 8366113 A123456 77036 Ward A
这是我的输出应如下所示:
Disch Date Event_ID Unique ID Ward ID Discharging Ward
2014-06-14 8208846 A123456 77085 Ward B
综上所述,我的代码提取了正确的“放电日期”和正确的“ Ward ID”,但似乎将其余的数据从表中的其他行中提取了出来。很抱歉,我们深表歉意-任何帮助将不胜感激,或者如果被寻求解决之道,请指出正确的方向。
您需要的简化版本如下所示...
SELECT [DETAIL INFO, no need to MAX or GROUP BY]
FROM Pat_spell AS IPS
LEFT JOIN PATIENT PAT WITH (NOLOCK) ON PAT.DIM_PATIENT_ID = IPS.DIM_PATIENT_ID
LEFT JOIN SPECIALTY SPEC WITH (NOLOCK) ON SPEC.DIM_SPECIALTY_ID = IPS.DIM_DIS_SPECT_ID
LEFT JOIN SERVICE_UNIT DSSU WITH (NOLOCK) ON IPS.DIM_DIS_WARD_ID = DSSU.DIM_SSU_ID
INNER JOIN (
SELECT PatientID, MAX(IPS.disch_dttm) AS DischargeDt
FROM [AllMyTables]
WHERE (IPS.DISCH_DTTM <= PAT.DEATH_DTTM + 30)
AND IPS.DIM_DIS_SPECT_ID = '7195'
AND IPS.DISCH_DTTM BETWEEN '01/01/2014' AND '30/06/2014'
) t1 ON PAT.PatientID = t1.PatientID AND IPS.disch_dttm = t1.DischargeDt
ORDER BY pat.PAS_ID
由于INNER SQL每位患者返回1行,因此无需对OUTER SQL进行分组。
如果我有更多的数据可以使用,则可以将整个SQL拼接在一起,但这也许会为您指明正确的方向。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句