sql - 使用案例表达式选择正确的患者类型
问题描述
在此处输入图像描述，以下代码用于获取患者洗澡。末尾有一个名为 PATIENT_TYPE 的案例表达式。
可以通过 3 种事物中的 1 种将患者定义为类型。如果他们在重症监护室，他们就是 CHG。如果它们有特定类型的中心线，则它们是 CHG 或 STANDARD。
如果它们不在 icu 中并且没有中心线，则它们被定义为标准。
一名患者可以同时拥有 2 条不同的中心线。1行可以是标准的，1行可以是chg的。一个病人一天可以洗澡多次。由于当天洗澡的次数，患者可能会显示多行数据。
在患者逗留期间，患者类型需要保持一致，以便他们获得正确的沐浴。
如果线路是 2 条不同的线路，患者 a 将显示 2 种患者类型。所以在 3 月 1 日，joe 有了一个 cl，所以显示了带有患者类型 chg 的 cl。3/2 joe 移除了 cl，但它在一天中的部分时间在那里，所以仍然显示患者类型 chg。在 3/3 乔仍然在单位，但没有中心线。他的房间类型是标准的，所以他的患者类型现在是标准的，因为他不在 icu 中或有中心线。
我需要能够显示正确的患者类型。线条需要在特定单位的时间内显示。
CL-- PULL ALL CL PATIENTS IN TIME FRAME OF REPORT
AS
(
select DISTINCT
to_char(a.effective_time,'MM/DD/YYYY') AS EFFECT_DATE,
a.effective_time,
L.PAT_ID,
l.PAT_ENC_CSN_ID,
CASE WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND
TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time) THEN 'CL'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time) THEN 'CL' END AS CL,
CASE WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND
TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time) THEN 1
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time) THEN 1
ELSE NULL END AS CL_FLAG,
CASE WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND
TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time) THEN L.FLO_MEAS_ID
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time) THEN L.FLO_MEAS_ID
ELSE NULL END AS FLO_MEAS_ID,
CASE WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND
TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)THEN L.DESCRIPTION
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time) THEN L.DESCRIPTION
ELSE NULL END AS DISP_NAME,
CASE WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND
TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)THEN L.IP_LDA_ID
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time) THEN L.IP_LDA_ID
ELSE NULL END AS IP_LDA_ID,
CASE WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND
TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)THEN L.PLACEMENT_INSTANT
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time) THEN L.PLACEMENT_INSTANT
ELSE NULL END AS PLACEMENT_INSTANT,
CASE WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND
TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)THEN L.REMOVAL_INSTANT
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time) THEN L.REMOVAL_INSTANT
ELSE NULL END AS REMOVAL_INSTANT,
CASE WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND
TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)THEN TO_CHAR(L.PLACEMENT_INSTANT,'MM/DD/YYYY')
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time) THEN TO_CHAR(L.PLACEMENT_INSTANT,'MM/DD/YYYY')
ELSE NULL END AS PLACEMENT_INSTANT_DATE,
CASE WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND
TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)THEN TO_CHAR(L.REMOVAL_INSTANT,'MM/DD/YYYY')
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time) THEN TO_CHAR(L.REMOVAL_INSTANT,'MM/DD/YYYY')
ELSE NULL END AS REMOVAL_INSTANT_DATE,
CASE
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095120 AND L.DESCRIPTION LIKE '%Arteriovenous Fistula%' THEN 'STANDARD'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095120
AND L.DESCRIPTION LIKE '%Arteriovenous Fistula%' THEN 'STANDARD'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095120 AND L.DESCRIPTION LIKE '%Arteriovenous Graft%' THEN 'STANDARD'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095120
AND L.DESCRIPTION LIKE '%Arteriovenous Graft%' THEN 'STANDARD'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095120 AND L.DESCRIPTION LIKE '%Arteriovenous Graft%' THEN 'STANDARD'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095120
AND L.DESCRIPTION LIKE '%Arteriovenous Graft%' THEN 'STANDARD'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095120 AND L.DESCRIPTION LIKE '%Tenckhoff Catheter%' THEN 'STANDARD'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095120
AND L.DESCRIPTION LIKE '%Tenckhoff Catheter%' THEN 'STANDARD'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095120 AND L.DESCRIPTION LIKE '%Hero Graft%' THEN 'STANDARD'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095120
AND L.DESCRIPTION LIKE '%Hero Graft%' THEN 'STANDARD'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =888300450 AND L.DESCRIPTION LIKE '%Arterial Line%' THEN 'STANDARD'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =888300450
AND L.DESCRIPTION LIKE '%Arterial Line%' THEN 'STANDARD'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095100 AND L.DESCRIPTION LIKE '%Tenckhoff Catheter%' THEN 'STANDARD'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095100
AND L.DESCRIPTION LIKE '%Tenckhoff Catheter%' THEN 'STANDARD'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095100 AND L.DESCRIPTION LIKE '%Hero Graft%' THEN 'STANDARD'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095100
AND L.DESCRIPTION LIKE '%Hero Graft%' THEN 'STANDARD'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095100 AND L.DESCRIPTION LIKE '%Arteriovenous Fistula%' THEN 'STANDARD'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095100
AND L.DESCRIPTION LIKE '%Arteriovenous Fistula%' THEN 'STANDARD'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095100 AND L.DESCRIPTION LIKE '%Arteriovenous Graft%' THEN 'STANDARD'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095100
AND L.DESCRIPTION LIKE '%Arteriovenous Graft%' THEN 'STANDARD'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095100 AND L.DESCRIPTION LIKE '%Arteriovenous Graft%' THEN 'STANDARD'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095100
AND L.DESCRIPTION LIKE '%Arteriovenous Graft%' THEN 'STANDARD'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID IN (26739,26747,26751,26751,26751,26755,26768,8882819,8882826,888304620,888304630,888304640,888304650,888304660,888304670,
3042000201, 3042000202, 3042673901, 8887095100,8887095120,8887095120,8887095120,8883040100580,8883040100583,8883040100584) THEN 'CHG'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID IN (26739,26747,26751,26751,26751,26755,26768,8882819,8882826,888304620,888304630,888304640,888304650,888304660,888304670,
3042000201, 3042000202, 3042673901, 8887095100,8887095120,8887095120,8887095120,8883040100580,8883040100583,8883040100584)THEN 'CHG'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095120 AND L.DESCRIPTION NOT LIKE '%Arteriovenous Fistula%' THEN 'CHG'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095120
AND L.DESCRIPTION NOT LIKE '%Arteriovenous Fistula%' THEN 'STANDARD'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095120 AND L.DESCRIPTION NOT LIKE '%Arteriovenous Graft%' THEN 'CHG'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095120
AND L.DESCRIPTION NOT LIKE '%Arteriovenous Graft%' THEN 'CHG'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095120 AND L.DESCRIPTION NOT LIKE '%Arteriovenous Graft%' THEN 'CHG'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095120
AND L.DESCRIPTION NOT LIKE '%Arteriovenous Graft%' THEN 'CHG'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095120 AND L.DESCRIPTION NOT LIKE '%Tenckhoff Catheter%' THEN 'CHG'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095120
AND L.DESCRIPTION NOT LIKE '%Tenckhoff Catheter%' THEN 'CHG'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095120 AND L.DESCRIPTION NOT LIKE '%Hero Graft%' THEN 'CHG'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095120
AND L.DESCRIPTION NOT LIKE '%Hero Graft%' THEN 'CHG'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =888300450 AND L.DESCRIPTION NOT LIKE '%Arterial Line%' THEN 'CHG'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =888300450
AND L.DESCRIPTION NOT LIKE '%Arterial Line%' THEN 'CHG'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095100 AND L.DESCRIPTION NOT LIKE '%Tenckhoff Catheter%' THEN 'CHG'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095100
AND L.DESCRIPTION NOT LIKE '%Tenckhoff Catheter%' THEN 'CHG'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095100 AND L.DESCRIPTION NOT LIKE '%Hero Graft%' THEN 'CHG'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095100
AND L.DESCRIPTION NOT LIKE '%Hero Graft%' THEN 'CHG'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095100 AND L.DESCRIPTION NOT LIKE '%Arteriovenous Fistula%' THEN 'CHG'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095100
AND L.DESCRIPTION NOT LIKE '%Arteriovenous Fistula%' THEN 'CHG'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095100 AND L.DESCRIPTION NOT LIKE '%Arteriovenous Graft%' THEN 'CHG'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095100
AND L.DESCRIPTION NOT LIKE '%Arteriovenous Graft%' THEN 'CHG'
WHEN TRUNC(L.PLACEMENT_INSTANT) <= TRUNC(a.effective_time) AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)
AND L.FLO_MEAS_ID =8887095100 AND L.DESCRIPTION NOT LIKE '%Arteriovenous Graft%' THEN 'CHG'
WHEN L.PLACEMENT_INSTANT IS NULL AND TRUNC(L.REMOVAL_INSTANT)>= TRUNC(a.effective_time)AND L.FLO_MEAS_ID =8887095100
AND L.DESCRIPTION NOT LIKE '%Arteriovenous Graft%' THEN 'CHG'
ELSE NULL END AS CL_PATIENT_TYPE-- PICKING PATIENT TYPE DEFINED BY LINE TYPES
From IP_LDA_NOADDSINGLE L
JOIN CLARITY_ADT A
ON L.PAT_ENC_CSN_ID = A.PAT_ENC_CSN_ID
where L.FLO_MEAS_ID IN (
26739, -- Temporary Central Line
26747, -- Permanent (tunneled) Catheter
)AND
(a.effective_time between (select start_date from dd) AND (select end_date from dd)+1)
)
,
CL2
AS
(
select DISTINCT
L.EFFECT_DATE,
L.effective_time,
L.PAT_ID,
L.IP_LDA_ID,
l.PAT_ENC_CSN_ID,
L.CL_FLAG,
L.FLO_MEAS_ID,
L.DISP_NAME,
L.PLACEMENT_INSTANT,
L.REMOVAL_INSTANT,
L.PLACEMENT_INSTANT_DATE,
L.REMOVAL_INSTANT_DATE,
L.CL_PATIENT_TYPE,-- PICKING PATIENT TYPE DEFINED BY LINE TYPES FROM CL L
MIN(L.CL_PATIENT_TYPE) OVER (PARTITION BY L.PAT_ID,L.EFFECT_DATE) AS MIN_CLPAT,
MAX(L.CL_PATIENT_TYPE) OVER (PARTITION BY L.PAT_ID,L.EFFECT_DATE) AS MAX_CLPAT,
L.CL
FROM CL L
WHERE L.CL_FLAG=1
)
,
PTS -- PATIENTS
AS
(select distinct
DEP.REV_LOC_ID AS HOSPITAL,
adt.DEPARTMENT_ID,
dep.department_name,
to_char(adt.effective_time,'MM/DD/YYYY') AS EFFECT_DATE,
ADT.EFFECTIVE_TIME,
ADT.PAT_ID,
PEH.Y_MRN,
PEH.PAT_ENC_CSN_ID,
peh.inpatient_data_id,
peh.hosp_admsn_time,
CASE WHEN ADT.DEPARTMENT_ID NOT IN
(2133009,4313023,4313015,4313016,4314007,4314009,4314027,5119219,5119221,5115578,73,5115579,5119222,5123216,5133207,5119223,5101202,5133026,5119220,5120202,5119218,5119235 ) THEN 'STANDARD'WHEN ADT.DEPARTMENT_ID IN (2133009,4313023,4313015,4313016,4314007,4314009,4314027,5119219,5119221,5115578,73,5115579,5119222,5123216,5133207,5119223,5101202,5133026,5119220,5120202,5119218,5119235 ) THEN 'CHG'END AS RM_PATIENT_TYPE--PICKING UP PATIENT TYPE DEFINED BY ROOM
from
clarity_adt adt
left outer join
pat_enc_hsp peh on
adt.PAT_ENC_CSN_ID = peh.PAT_ENC_CSN_ID
LEFT OUTER join
clarity_dep dep
on adt.DEPARTMENT_ID = dep.DEPARTMENT_ID
where
((adt.effective_time between (select start_date from dd) AND (select end_date from dd)+1)
and trunc (adt.effective_time) <> trunc (peh.hosp_admsn_time)
) and adt.event_type_c in (6)
and PEH.ADT_PAT_CLASS_C = '1'
AND adt.event_subtype_c <> 2
)
,
FMEAS -- BATHING FLOW MEASURE
AS
(
select distinct
adt.DEPARTMENT_ID,
dep.department_name,
ADT.PAT_ID,
PEH.Y_MRN,
PEH.PAT_ENC_CSN_ID AS FMEAS_PAT_ENC_CSN_ID,
NULL AS y_inpatient_dat,
to_char(ADT.EFFECTIVE_TIME,'MM/DD/YYYY') AS EFFECT_DATE,
NULL AS RECORDED_TIME,
TO_CHAR(ADT.EFFECTIVE_TIME,'MM/DD/YYYY') AS RECORDED_DATE,
NULL AS meas_value,
NULL AS Standard_Bath,
NULL AS CHG
from
clarity_adt adt
LEFT OUTER join
pat_enc_hsp peh
on
adt.PAT_ENC_CSN_ID = peh.PAT_ENC_CSN_ID
LEFT OUTER JOIN
clarity_dep dep
on
adt.DEPARTMENT_ID = dep.DEPARTMENT_ID
where
DEP.REV_LOC_ID IN (2133)--, 4313,4314,5133)
AND
(adt.effective_time between (select start_date from dd) AND (select end_date from dd)+1) and adt.event_type_c in (6)
and PEH.ADT_PAT_CLASS_C = '1'
AND adt.event_subtype_c <> 2
UNION
select distinct
adt.DEPARTMENT_ID,
dep.department_name,
ADT.PAT_ID,
PEH.Y_MRN,
PEH.PAT_ENC_CSN_ID AS FMEAS_PAT_ENC_CSN_ID,
fmeas.y_inpatient_dat,
to_char(ADT.EFFECTIVE_TIME,'MM/DD/YYYY') AS EFFECT_DATE,
Fmeas.Recorded_Time AS RECORDED_TIME,
TO_CHAR(fMEAS.RECORDED_TIME,'MM/DD/YYYY') AS RECORDED_DATE,
fmeas.meas_value,
CASE WHEN Fmeas.MEAS_VALUE LIKE 'Standard%' THEN 'Standard bathing wipes'
WHEN Fmeas.MEAS_VALUE LIKE '%Standard bathing wipes%' THEN 'Standard bathing wipes'
WHEN Fmeas.MEAS_VALUE LIKE '%Indep%' THEN 'Independent Shower' END AS Standard_Bath,
CASE WHEN Fmeas.MEAS_VALUE LIKE '%CHG (Chlorhexidine) bathing wipes%' THEN 'CHG (Chlorhexidine) bathing wipes'
WHEN Fmeas.MEAS_VALUE LIKE '%Independent Shower%' THEN 'Independent Shower'
WHEN Fmeas.MEAS_VALUE LIKE '%Incontience Care%'then 'Incontience Care'
WHEN Fmeas.MEAS_VALUE LIKE '%Bath not given - Patient refused CHG %'then 'Bath not given - Patient refused CHG'
WHEN Fmeas.MEAS_VALUE LIKE '%Bath not given - CHG contraindicated%'then 'Bath not given - CHG contraindicated'
WHEN Fmeas.MEAS_VALUE LIKE 'Bath not given - Other Comment%'then 'Bath not given - Other Comment' END AS CHG
from
clarity_adt adt
LEFT OUTER join
pat_enc_hsp peh
on
adt.PAT_ENC_CSN_ID = peh.PAT_ENC_CSN_ID
LEFT OUTER join
Ip_Flwsht_Meas fmeas
on
fmeas.y_inpatient_dat=peh.inpatient_data_id
LEFT OUTER JOIN
clarity_dep dep
on
adt.DEPARTMENT_ID = dep.DEPARTMENT_ID
where
(fmeas.RECORDED_TIME between (select start_date from dd) AND (select end_date from dd)+1)
and
(adt.effective_time between (select start_date from dd) AND (select end_date from dd)+1)
and
fmeas.flo_meas_id='888305660'
and adt.event_type_c in (6)
and
PEH.ADT_PAT_CLASS_C = '1'
AND
adt.event_subtype_c <> 2
)
,
PT2-- JOIN FLOWMEAS TO PATS
AS
(
SELECT DISTINCT
P.HOSPITAL,
P.DEPARTMENT_ID,
P.department_name,
P.EFFECT_DATE,
P.effective_time,
P.PAT_ID,
P.Y_MRN,
P.hosp_admsn_time,
P.PAT_ENC_CSN_ID,
P.RM_PATIENT_TYPE,
F.RECORDED_TIME,
F.RECORDED_DATE,
F.meas_value,
F.Standard_Bath,
F.CHG,
CASE
WHEN F.MEAS_VALUE IS NULL THEN 'NOT DOCUMENTED'
WHEN F.MEAS_VALUE ='Standard bathing wipes' THEN 'WASHED / GIVEN'
WHEN F.MEAS_VALUE LIKE '%Indep%' THEN 'WASHED / GIVEN'
WHEN F.MEAS_VALUE LIKE 'Indep%' THEN 'WASHED / GIVEN'
WHEN F.MEAS_VALUE LIKE '%CHG (Chlorhexidine) bathing wipes%' THEN 'WASHED / GIVEN'
WHEN F.MEAS_VALUE LIKE 'CHG (Chlorhexidine) bathing wipes%' THEN 'WASHED / GIVEN'
WHEN F.MEAS_VALUE LIKE 'Incontinence care%'then 'WASHED / GIVEN'
WHEN F.MEAS_VALUE LIKE '%Incontinence care%'then 'WASHED / GIVEN'
WHEN F.MEAS_VALUE LIKE '%Bath not given – Other (comment)%' THEN 'NOT GIVEN'
WHEN F.MEAS_VALUE LIKE '%refused%' then 'Refused'
WHEN F.MEAS_VALUE LIKE '%CHG contraindicated%' then 'Contraindicated'
WHEN F.MEAS_VALUE = 'Bath not given – CHG contraindicated' then 'Contraindicated'
END AS BATH_STATUS
FROM
PTS P
LEFT OUTER JOIN
FMEAS F
ON
P.PAT_ENC_CSN_ID = F.FMEAS_PAT_ENC_CSN_ID
WHERE
P.EFFECT_DATE = F.RECORDED_DATE
ORDER BY
P.HOSPITAL,
P.DEPARTMENT_ID,
P.EFFECT_DATE,
P.Y_MRN,
F.RECORDED_DATE)
,
FIN
AS
(
SELECT DISTINCT
P.HOSPITAL,
P.DEPARTMENT_ID,
P.department_name,
P.EFFECT_DATE,
P.effective_time,
C.IP_LDA_ID,
CASE WHEN TRUNC(C.PLACEMENT_INSTANT) <= TRUNC(P.effective_time) AND
TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time)THEN C.IP_LDA_ID
WHEN C.PLACEMENT_INSTANT IS NULL AND TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time) THEN C.IP_LDA_ID
ELSE NULL END AS IP_LDA_ID_A,
C.DISP_NAME,
CASE WHEN TRUNC(C.PLACEMENT_INSTANT) <= TRUNC(P.effective_time) AND
TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time)THEN C.DISP_NAME
WHEN C.PLACEMENT_INSTANT IS NULL AND TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time) THEN C.DISP_NAME
ELSE NULL END AS DISP_NAME_A,
C.PLACEMENT_INSTANT,
CASE WHEN TRUNC(C.PLACEMENT_INSTANT) <= TRUNC(P.effective_time) AND
TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time)THEN C.PLACEMENT_INSTANT
WHEN C.PLACEMENT_INSTANT IS NULL AND TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time) THEN TO_DATE('01/01/1900','MM/DD/YYYY')
ELSE NULL END AS PLACEMENT_INSTANT_A,
C.REMOVAL_INSTANT,
CASE WHEN TRUNC(C.PLACEMENT_INSTANT) <= TRUNC(P.effective_time) AND
TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time)THEN C.REMOVAL_INSTANT
WHEN C.PLACEMENT_INSTANT IS NULL AND TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time) THEN C.REMOVAL_INSTANT
ELSE NULL END AS REMOVAL_INSTANT_A,
C.PLACEMENT_INSTANT_DATE,
CASE WHEN TRUNC(C.PLACEMENT_INSTANT) <= TRUNC(P.effective_time) AND
TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time)THEN C.PLACEMENT_INSTANT_DATE
WHEN C.PLACEMENT_INSTANT IS NULL AND TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time) THEN C.PLACEMENT_INSTANT_DATE
ELSE NULL END AS PLACEMENT_INSTANT_DATE_A,
C.REMOVAL_INSTANT_DATE,
CASE WHEN TRUNC(C.PLACEMENT_INSTANT) <= TRUNC(P.effective_time) AND
TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time)THEN C.REMOVAL_INSTANT_DATE
WHEN C.PLACEMENT_INSTANT IS NULL AND TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time) THEN C.REMOVAL_INSTANT_DATE
ELSE NULL END AS REMOVAL_INSTANT_DATE_A,
C.CL,
CASE WHEN TRUNC(C.PLACEMENT_INSTANT) <= TRUNC(P.effective_time) AND
TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time)THEN C.CL
WHEN C.PLACEMENT_INSTANT IS NULL AND TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time) THEN C.CL
ELSE NULL END AS CL_A,
C.CL_PATIENT_TYPE,
CASE WHEN TRUNC(C.PLACEMENT_INSTANT) <= TRUNC(P.effective_time) AND
TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time)THEN C.CL_PATIENT_TYPE
WHEN C.PLACEMENT_INSTANT IS NULL AND TRUNC(C.REMOVAL_INSTANT)>= TRUNC(P.effective_time) THEN C.CL_PATIENT_TYPE
ELSE NULL END AS CL_PATIENT_TYPE_A,
P.RM_PATIENT_TYPE,
P.PAT_ID,
P.Y_MRN,
P.hosp_admsn_time,
P.PAT_ENC_CSN_ID,
P.RECORDED_TIME,
P.RECORDED_DATE,
P.meas_value,
P.Standard_Bath,
P.CHG,
P.BATH_STATUS
FROM
PT2 P
LEFT OUTER JOIN
CL C
ON
P.PAT_ENC_CSN_ID =C.PAT_ENC_CSN_ID
WHERE
(P.effective_time between (select start_date from dd) AND (select end_date from dd)+1)
)
SELECT DISTINCT
P.EFFECT_DATE,
P.Y_MRN,
**CASE WHEN P.DEPARTMENT_ID IN (2133009,4313023,4313015,4313016,4314007,4314009,4314027,5119219,5119221,5115578,73,5115579,5119222,5123216,
5133207,5119223,5101202,5133026,5119220,5120202,5119218,5119235) THEN 'CHG'
WHEN P.IP_LDA_ID_A IS NOT NULL AND P.DEPARTMENT_ID NOT IN (2133009,4313023,4313015,4313016,4314007,4314009,4314027,5119219,5119221,5115578,73,5115579,5119222,5123216,
5133207,5119223,5101202,5133026,5119220,5120202,5119218,5119235)THEN MIN(P.CL_PATIENT_TYPE_A) OVER (PARTITION BY P.PAT_ID,P.EFFECT_DATE)
WHEN P.IP_LDA_ID_A IS NULL AND P.DEPARTMENT_ID NOT IN (2133009,4313023,4313015,4313016,4314007,4314009,4314027,5119219,5119221,5115578,73,5115579,5119222,5123216,
5133207,5119223,5101202,5133026,5119220,5120202,5119218,5119235)THEN P.RM_PATIENT_TYPE END AS PATIENT_TYPE,**
FROM
FIN P
解决方案
推荐阅读
- c - 为什么 IAR 中的 __attribute__((weak)) 无法编译？
- python - 上传许多图像 django rest 框架
- php - 以编程方式创建的 WooCommerce 订单 - 根据电子邮件而不是用户 ID 设置客户
- java - java中重定向控制台输入的问题
- python - 如何在熊猫中将multiindex更改为正常
- c# - C# 仅在存在时使用数组
- javascript - Autodesk Forge Viewer 调用内置扩展功能
- maven - “输入更改需要完全重建增量任务'：compileKotlin'。” Gradle 构建时
- gradle - 使用包装器时如何设置 org.gradle.native 系统属性
- javascript - 如何将循环重写为匿名函数？