読者です 読者をやめる 読者になる 読者になる

175.看護必要度救急搬送チェック

看護必要度の入力で「救急搬送後の入院」か否か、入力間違いが多いので、ER来院患者と入院患者で正しく入力しているかチェックSQLを作成しました

 

select
’取り過ぎ’ 区分,
s.ROOMCODE 病室,
s.BEDCODE ベッド,
z.PATIENTNO 患者番号,
( z.KANJILASTNAME || ’ ’ || z.KANJIFIRSTNAME ) 患者氏名 ,
A.ADMISSIONDATE 入院日,
a.ADMISSIONWARD 入院時病棟,
TO_DATE(d.対象日) - A.ADMISSIONDATE + 1 入院日数,
 NEEDEDDEGQUANTITYA  必要度A,
 NEEDEDDEGQUANTITYB  必要度B,
 NEEDEDDEGQUANTITYC  必要度C,
 case when NEEDEDDEGQUANTITYA >= ’2’ and NEEDEDDEGQUANTITYB >= ’3’ then ’○’
when NEEDEDDEGQUANTITYA >= ’3’ then ’○’
when NEEDEDDEGQUANTITYC >= ’1’ then ’○’
else null end  必要度条件
,ER.appdate 救急来院日
,ER.TRIAGEFREECOMMENT トリアージ入力コメント
,a.ADMISSIONNO 入院NO
,d.*
,s.STAFFCODE 更新者
,s.UPDATEDATE 更新日時
from (
SELECT
 d.TARGETDATE 対象日
,d.WARDCODE 病棟,
max ( decode(d.SUMITEMCODE,’A00000’,d.SUMDETAILNAME )) 判定用,
max ( decode(d.SUMITEMCODE,’A00100’,d.TEN )) "創傷処置",
max ( decode(d.ITEMCODE,’001000620’,d.TEN )) "呼吸ケア",
max ( decode(d.ITEMCODE,’001000260’,d.TEN )) "点滴ライン",
max ( decode(d.ITEMCODE,’001000270’,d.TEN )) "心電図モニタ",
max ( decode(d.ITEMCODE,’001000300’,d.TEN )) "シリンジポンプ",
max ( decode(d.ITEMCODE,’001000330’,d.TEN )) "輸血",
max ( decode(d.SUMITEMCODE,’A01500’,d.TEN )) "専門的な治療・処置",
max ( decode(d.ITEMCODE,’001000590’,d.TEN )) "◆救急搬送後の入院◆",
max ( decode(d.ITEMCODE,’001000390’,d.TEN )) "寝返り",
max ( decode(d.ITEMCODE,’001000790’,d.TEN )) "移乗",
max ( decode(d.ITEMCODE,’001000780’,d.TEN )) "口腔清潔",
max ( decode(d.ITEMCODE,’001000450’,d.TEN )) "食事摂取",
max ( decode(d.ITEMCODE,’001000460’,d.TEN )) "衣服の着脱",
max ( decode(d.ITEMCODE,’001000480’,d.TEN )) "診療・療養上の指示",
max ( decode(d.ITEMCODE,’001000490’,d.TEN )) "危険行動",
max ( decode(d.ITEMCODE,’001000520’,d.TEN )) "開頭手術7",
max ( decode(d.ITEMCODE,’001000530’,d.TEN )) "開胸手術7",
max ( decode(d.ITEMCODE,’001000540’,d.TEN )) "開腹手術5",
max ( decode(d.ITEMCODE,’001000550’,d.TEN )) "骨の手術5",
max ( decode(d.ITEMCODE,’001000560’,d.TEN )) "胸腔鏡・腹腔鏡手術3",
max ( decode(d.ITEMCODE,’001000570’,d.TEN )) "全身麻酔・脊椎麻酔の手術2",
max ( decode(d.SUMITEMCODE,’C00700’,d.TEN )) "救命等に係る内科的治療2"
,d.ADMISSIONNO
,d.PATIENTNO
,d.HOSPITALCODE,d.ROOMTYPE,d.WORKTIMEZONE
from (
 select
 HOSPITALCODE,ROOMTYPE,WORKTIMEZONE,
 WARDCODE,PATIENTNO,TARGETDATE,ITEMCODE,SUMITEMCODE,SUMDETAILNAME,ADMISSIONNO,
 decode(NEEDEDDEGQUANTITY,0,null,NEEDEDDEGQUANTITY) TEN
 from A_NSSUMDTL_H28
 where HOSPITALCODE = ’01’
 and   ROOMTYPE = ’0’
 and   TARGETDATE between ’<p>DATE21|開始日|AddDays(-30)</p>’ and ’<p>DATE21|終了日|AddDays(-1)</p>’
 and   WARDCODE in (’4E’,’4W’,’5E’,’6E’,’6W’,’7E’,’7W’,’8E’,’8W’)
) d
group by  d.HOSPITALCODE,d.ROOMTYPE,d.TARGETDATE, d.WARDCODE, d.WORKTIMEZONE, d.PATIENTNO, d.ADMISSIONNO
) d
left join A_NSSUM_H28 s on s.HOSPITALCODE = d.HOSPITALCODE and s.ROOMTYPE = d.ROOMTYPE and
    s.TARGETDATE = d.対象日 and s.WARDCODE = d.病棟 and s.WORKTIMEZONE = d.WORKTIMEZONE and s.PATIENTNO = d.PATIENTNO
and s.ADMTYPE = ’N’
left join Z_IBAPATIENT z on z.PATIENTNO = d.PATIENTNO
LEFT JOIN Z_ADMADMISSION A ON A.ADMISSIONNO = d.ADMISSIONNO AND  A.PATIENTNO = d.PATIENTNO
LEFT JOIN ( select * from G_EMERGENCYDETAIL
       where appdate BETWEEN TO_DATE(’<p>DATE21|開始日|AddDays(-30)</p>’) -1  and ’<p>DATE21|終了日|AddDays(-1)</p>’
         and ( instr(TRIAGEFREECOMMENT,’AC’) > 0 or instr(TRIAGEFREECOMMENT,’DH’) > 0 )
         and instr(TRIAGEFREECOMMENT,’HCU’) < 1
         and instr(TRIAGEFREECOMMENT,’ICU’) < 1
         and instr(TRIAGEFREECOMMENT,’*’) < 1
     ) ER ON  ER.PATIENTNO = d.PATIENTNO  and ( er.appdate between to_date(d.対象日)-1 and to_date( d.対象日 ))
where ER.TRIAGEFREECOMMENT is null
and  d."◆救急搬送後の入院◆" is not null

union

select
’取り漏れ’ 区分,
s.ROOMCODE 病室,
s.BEDCODE ベッド,
z.PATIENTNO 患者番号,
( z.KANJILASTNAME || ’ ’ || z.KANJIFIRSTNAME ) 患者氏名 ,
A.ADMISSIONDATE 入院日,
a.ADMISSIONWARD 入院時病棟,
TO_DATE(d.対象日) - A.ADMISSIONDATE + 1 入院日数,
 NEEDEDDEGQUANTITYA  必要度A,
 NEEDEDDEGQUANTITYB  必要度B,
 NEEDEDDEGQUANTITYC  必要度C,
 case when NEEDEDDEGQUANTITYA >= ’2’ and NEEDEDDEGQUANTITYB >= ’3’ then ’○’
when NEEDEDDEGQUANTITYA >= ’3’ then ’○’
when NEEDEDDEGQUANTITYC >= ’1’ then ’○’
else null end  必要度条件
,ER.appdate 救急来院日
,ER.TRIAGEFREECOMMENT トリアージ入力コメント
,a.ADMISSIONNO 入院NO
,d.*
,s.STAFFCODE 更新者
,s.UPDATEDATE 更新日時
from ( select * from G_EMERGENCYDETAIL
       where appdate BETWEEN TO_DATE(’<p>DATE21|開始日|AddDays(-30)</p>’) -1  and ’<p>DATE21|終了日|AddDays(-1)</p>’
         and ( instr(TRIAGEFREECOMMENT,’AC’) > 0 or instr(TRIAGEFREECOMMENT,’DH’) > 0 or
               instr(TRIAGEFREECOMMENT,’AC’) > 0 or instr(TRIAGEFREECOMMENT,’DH’) > 0 )
         and instr(TRIAGEFREECOMMENT,’HCU’) < 1
         and instr(TRIAGEFREECOMMENT,’ICU’) < 1
         and instr(TRIAGEFREECOMMENT,’HCU’) < 1
         and instr(TRIAGEFREECOMMENT,’ICU’) < 1
         and instr(TRIAGEFREECOMMENT,’5西B’) < 1
         and instr(TRIAGEFREECOMMENT,’お産’) < 1
         and instr(TRIAGEFREECOMMENT,’産科’) < 1
         and instr(TRIAGEFREECOMMENT,’*’) < 1
     ) ER
left join (
SELECT
d.TARGETDATE 対象日
,d.WARDCODE 病棟,
max ( decode(d.SUMITEMCODE,’A00000’,d.SUMDETAILNAME )) 判定用,
max ( decode(d.SUMITEMCODE,’A00100’,d.TEN )) "創傷処置",
max ( decode(d.ITEMCODE,’001000620’,d.TEN )) "呼吸ケア",
max ( decode(d.ITEMCODE,’001000260’,d.TEN )) "点滴ライン",
max ( decode(d.ITEMCODE,’001000270’,d.TEN )) "心電図モニタ",
max ( decode(d.ITEMCODE,’001000300’,d.TEN )) "シリンジポンプ",
max ( decode(d.ITEMCODE,’001000330’,d.TEN )) "輸血",
max ( decode(d.SUMITEMCODE,’A01500’,d.TEN )) "専門的な治療・処置",
max ( decode(d.ITEMCODE,’001000590’,d.TEN )) "◆救急搬送後の入院◆",
max ( decode(d.ITEMCODE,’001000390’,d.TEN )) "寝返り",
max ( decode(d.ITEMCODE,’001000790’,d.TEN )) "移乗",
max ( decode(d.ITEMCODE,’001000780’,d.TEN )) "口腔清潔",
max ( decode(d.ITEMCODE,’001000450’,d.TEN )) "食事摂取",
max ( decode(d.ITEMCODE,’001000460’,d.TEN )) "衣服の着脱",
max ( decode(d.ITEMCODE,’001000480’,d.TEN )) "診療・療養上の指示",
max ( decode(d.ITEMCODE,’001000490’,d.TEN )) "危険行動",
max ( decode(d.ITEMCODE,’001000520’,d.TEN )) "開頭手術7",
max ( decode(d.ITEMCODE,’001000530’,d.TEN )) "開胸手術7",
max ( decode(d.ITEMCODE,’001000540’,d.TEN )) "開腹手術5",
max ( decode(d.ITEMCODE,’001000550’,d.TEN )) "骨の手術5",
max ( decode(d.ITEMCODE,’001000560’,d.TEN )) "胸腔鏡・腹腔鏡手術3",
max ( decode(d.ITEMCODE,’001000570’,d.TEN )) "全身麻酔・脊椎麻酔の手術2",
max ( decode(d.SUMITEMCODE,’C00700’,d.TEN )) "救命等に係る内科的治療2"
,d.ADMISSIONNO
,d.PATIENTNO
,d.HOSPITALCODE,d.ROOMTYPE,d.WORKTIMEZONE


from (
 select
 HOSPITALCODE,ROOMTYPE,WORKTIMEZONE,
 WARDCODE,PATIENTNO,TARGETDATE,ITEMCODE,SUMITEMCODE,SUMDETAILNAME,ADMISSIONNO,
 decode(NEEDEDDEGQUANTITY,0,null,NEEDEDDEGQUANTITY) TEN
 from A_NSSUMDTL_H28
 where HOSPITALCODE = ’01’
 and   ROOMTYPE = ’0’
 and   TARGETDATE between ’<p>DATE21|開始日|AddDays(-30)</p>’ and ’<p>DATE21|終了日|AddDays(-1)</p>’
 and   WARDCODE in (’4E’,’4W’,’5E’,’6E’,’6W’,’7E’,’7W’,’8E’,’8W’)
) d
group by  d.HOSPITALCODE,d.ROOMTYPE,d.TARGETDATE, d.WARDCODE, d.WORKTIMEZONE, d.PATIENTNO, d.ADMISSIONNO
) d on ER.PATIENTNO = d.PATIENTNO and d.対象日 between to_char(er.appdate,’YYYYMMDD’)  and to_char(er.appdate+1,’YYYYMMDD’)
  and 判定用 = ’対象’
left join A_NSSUM_H28 s on s.HOSPITALCODE = d.HOSPITALCODE and s.ROOMTYPE = d.ROOMTYPE and
    s.TARGETDATE = d.対象日 and s.WARDCODE = d.病棟 and s.WORKTIMEZONE = d.WORKTIMEZONE and s.PATIENTNO = er.PATIENTNO
 and s.ADMTYPE = ’N’
left join Z_IBAPATIENT z on z.PATIENTNO = er.PATIENTNO
LEFT JOIN Z_ADMADMISSION A ON A.ADMISSIONDATE between er.appdate and er.appdate+1  AND  A.PATIENTNO = er.PATIENTNO
where d."◆救急搬送後の入院◆" is null
and   a.ADMISSIONNO is not null
and   TRUNC(MONTHS_BETWEEN(ER.appdate,z.BIRTHDAY) / 12) > 15