包括入院时间大于出院时间的记录

Question

我有一个桥表，它是一个2维记录的组合。它工作得很好，但不包括4条记录，其中admit_time大于discharge_time。

我如何修改我的代码，以包括4条记录?

SELECT
ISNULL(D.dim_patient_episode_key, 0) dim_patient_episode_key,
ISNULL(S.dim_continuous_care_ep_key, 0) dim_continuous_care_ep_key
FROM 
[TABLEOWNER].[dim_patient_episode] D
LEFT JOIN 
[TABLEOWNER].[dim_continuous_care_episode] S 
ON D.patid = S.patid
AND D.program_x_tx_setting_code = S.program_x_tx_setting_code
AND D.preadmit_admission_date + D.admit_time_of <= ISNULL(S.date_of_discharge, getdate()) + ISNULL(S.disc_time_of, '00:00:00')
AND ISNULL(D.date_of_discharge, getdate()) + ISNULL(D.disc_time_of, '00:00:00') >= S.preadmit_admission_date + S.admit_time_of
WHERE 
D.admit_time_of <> 'UNKNOWN' 
AND S.disc_time_of <> 'UNKNOWN'

Answer 1

据我所知，您正在寻找入院时间大于出院时间的当前标准OR。像这样

SELECT
ISNULL(D.dim_patient_episode_key, 0) dim_patient_episode_key,
ISNULL(S.dim_continuous_care_ep_key, 0) dim_continuous_care_ep_key
FROM 
[TABLEOWNER].[dim_patient_episode] D
LEFT JOIN 
[TABLEOWNER].[dim_continuous_care_episode] S 
ON D.patid = S.patid
AND D.program_x_tx_setting_code = S.program_x_tx_setting_code
AND D.preadmit_admission_date + D.admit_time_of <= ISNULL(S.date_of_discharge, getdate()) + ISNULL(S.disc_time_of, '00:00:00')
AND ISNULL(D.date_of_discharge, getdate()) + ISNULL(D.disc_time_of, '00:00:00') >= S.preadmit_admission_date + S.admit_time_of
WHERE 
(D.admit_time_of <> 'UNKNOWN' 
AND S.disc_time_of <> 'UNKNOWN')
or (D.admit_time_of>S.disc_time_of);