sql - 查询以比较医院人口普查中缺少患者的日期

Question

我有一个医院病床普查，它被触发并在表格中创建一个带有日期/时间戳的行。当床位检查部分完成后，它会标记事件普查。我发现一些患者在他们住院的日子里没有在事件普查中加上时间戳。我正在尝试编写一个查询来捕获所有可能遇到此问题的患者。
我需要在患者入院和出院日期之间捕获患者，然后在他们没有时间戳事件的任何一天进行人口普查。例如，该患者在 12 日或 13 日没有进行人口普查，但在 14 日进行了普查。我希望能够提取此 pat_id 和日期，它们没有加盖人口普查。

11-APR-2019 11:59:00 PM CENSUS
12-APR-2019 03:12:00 PM TRANSFER OUT
12-APR-2019 03:12:00 PM TRANSFER IN
14-APR-2019 07:06:00 AM PATIENT UPDATE
14-APR-2019 11:40:00 AM TRANSFER OUT
14-APR-2019 11:40:00 AM TRANSFER IN
14-APR-2019 11:59:00 PM CENSUS

我为我的查询创建了一个日历部分。然后我创建了一个查询以在某个时间范围内捕获患者。从那里我有点卡住了。

DATE1
AS
(select 
to_char(dates,'MM/DD/YYYY') AS WEEK_DATE,
dates, 
to_char(dates,'D') weekday, 
to_char(dates,'mm') m_onth, 
to_char(dates,'ww') week_of_year, 
to_char(dates,'dd') month_day,
to_char(dates,'ddd') Year_day,
SUBSTR(dates,1,2) AS WEEKDATE
from (SELECT TRUNC(to_date(v.yyyy,'YYYY'),'YY') +LEVEL - 1 DATES 
FROM ( SELECT 2019 yyyy FROM dual ) v 
CONNECT BY LEVEL < 366
)
)
,
ADT 
AS (select distinct
adt.pat_id,
peh.y_mrn,
adt.DEPARTMENT_ID,
adp.department_name,
--peh.HOSP_ADMSN_TIME,
to_char(peh.HOSP_ADMSN_TIME,'MM/DD/YYYY') AS HOSP_ADMSN_TIME2,
--peh.HOSP_DISCH_TIME,
to_char(peh.HOSP_DISCH_TIME,'MM/DD/YYYY') AS HOSP_DISCH_TIME2,
adt.effective_time,
to_char(aDT.effective_time,'MM/DD/YYYY') AS EFFECT_DATE,
--LEAD(adt.effective_time) over (partition by ADT.pat_id order by ADT.pat_id, adt.effective_time) AS NEXT_EFF_DATE,
--CASE WHEN adt.event_type_c =6 THEN adt.effective_time END AS CENSUS_DATE,
et.title as event_type,
adt.event_type_c,
peh.ADT_PAT_CLASS_C,
Adt.event_subtype_c--,
--LAG(adt.effective_time) over (partition by ADT.pat_id order by ADT.pat_id, adt.effective_time) AS PREV_EFF_DATE
from 
clarity_adt adt
left OUTER join 
 pat_enc_hsp peh
 on
peh.pat_enc_csn_id = adt.pat_enc_csn_id
left outer join
clarity_dep adp 
on adt.department_id = adp.department_id
left OUTER join 
zc_event_type et 
on adt.event_type_c = et.event_type_c
where 
adt.effective_time between '08-apr-2019' and '15-apr-2019' 
order by adt.effective_time 
)
,
ADT2
AS
(
SELECT-- DISTINCT
D.WEEK_DATE,
A.HOSP_ADMSN_TIME2,
A.EFFECT_DATE,
A.PAT_ID,
CASE WHEN D.WEEK_DATE IS NOT NULL AND A.EFFECT_DATE IS NULL AND A.event_type <> 'CENSUS' THEN 1
WHEN D.WEEK_DATE IS NOT NULL AND A.EFFECT_DATE IS NULL AND A.event_type IS NULL THEN 1 
WHEN D.WEEK_DATE IS NOT NULL AND A.EFFECT_DATE IS NOT NULL AND A.event_type <> 'CENSUS' THEN 1 ELSE 0 
END AS NO_ADT_INFO,
A.event_type,
A.HOSP_DISCH_TIME2
FROM
DATE2 D
LEFT OUTER JOIN
ADT A
ON 
D.WEEK_DATE = A.EFFECT_DATE
ORDER BY 
D.WEEK_DATE)

我想最终得到患者 ID、他们没有人口普查的星期几、医院入院和出院日期

PAT_ID  WEEK_DATE   EVENT_TYPE  HOSP_ADMSN_TIME HOSP_DISCH_TIME
ABCDEF  4/12/2019   NO CENSUS   4/10/2019   4/19/2019
ABCDEF  4/13/2019   NO CENSUS   4/10/2019   4/19/2019
GHIJK   4/8/2019    NO CENSUS   4/2/2019    4/12/2019
GHIJK   4/11/2019   NO CENSUS   4/2/2019    4/12/2019

标签： sqloracle

Answer 1

以下是两名患者的样本数据：

  events(pat_id, event_date, event_type) as (
    select 'ABCD', to_date('2019-04-11 23:59', 'yyyy-mm-dd hh24:mi'), 'CENSUS'         from dual union all
    select 'ABCD', to_date('2019-04-12 15:12', 'yyyy-mm-dd hh24:mi'), 'TRANSFER OUT'   from dual union all
    select 'ABCD', to_date('2019-04-12 15:12', 'yyyy-mm-dd hh24:mi'), 'TRANSFER IN'    from dual union all
    select 'ABCD', to_date('2019-04-14 07:06', 'yyyy-mm-dd hh24:mi'), 'PATIENT UPDATE' from dual union all
    select 'ABCD', to_date('2019-04-14 11:40', 'yyyy-mm-dd hh24:mi'), 'TRANSFER OUT'   from dual union all
    select 'ABCD', to_date('2019-04-14 11:40', 'yyyy-mm-dd hh24:mi'), 'TRANSFER IN'    from dual union all
    select 'ABCD', to_date('2019-04-14 23:59', 'yyyy-mm-dd hh24:mi'), 'CENSUS'         from dual union all
    select 'GHIJ', to_date('2019-05-17 23:59', 'yyyy-mm-dd hh24:mi'), 'CENSUS'         from dual union all
    select 'GHIJ', to_date('2019-05-19 23:59', 'yyyy-mm-dd hh24:mi'), 'CENSUS'         from dual ),
  peh(pat_id, hosp_admsn_time, hosp_disch_time) as (
    select 'ABCD', date '2019-04-11', date '2019-04-14' from dual union all
    select 'GHIJ', date '2019-05-17', date '2019-05-20' from dual ),

CENSUS您可以为每个患者创建递归查询生成天，并检查这些天是否有事件：

with cte(pat_id, num, adm, dis) as (
    select pat_id, 0, hosp_admsn_time, hosp_disch_time from peh
    union all
    select pat_id, num + 1, adm, dis from cte where num < dis - adm)
select pat_id, day, 'NO CENSUS' info, adm, dis
  from (select pat_id, adm + num day, adm, dis from cte) d
  where not exists (
    select 1 
      from events 
      where pat_id = d.pat_id and trunc(event_date) = d.day and event_type = 'CENSUS')
  order by pat_id, day;

结果：

PAT_ID DAY         INFO      ADM         DIS
------ ----------- --------- ----------- -----------
ABCD   2019-04-12  NO CENSUS 2019-04-11  2019-04-14
ABCD   2019-04-13  NO CENSUS 2019-04-11  2019-04-14
GHIJ   2019-05-18  NO CENSUS 2019-05-17  2019-05-20
GHIJ   2019-05-20  NO CENSUS 2019-05-17  2019-05-20

^{dbfiddle 演示}