OkiTool開発日記

データベース参照ツールを開発しています。そのツールの開発秘話などなどの紹介

124.救急来院情報(指定日)日誌用

MegaOrkHRで救急患者一覧を表示するのになぜが2日分の一覧表示しか出来ないとの事。不思議です。

 

select --DISTINCT

to_char(a.appdate,’YYYY/MM/DD’) 来院日,

 ( z.KANJILASTNAME || ’ ’ || z.KANJIFIRSTNAME ) 患者氏名 ,

( Z.KANALASTNAME || ’ ’ || Z.KANAFIRSTNAME ) カナ,

 a.PATIENTNO 患者番号,

 decode(z.SEX,’0’,’男’,’女’) 性別,

 ( to_CHAR(TRUNC(MONTHS_BETWEEN(a.appdate,z.BIRTHDAY) / 12),’999’) || ’歳’ ||

to_CHAR(TRUNC(MONTHS_BETWEEN(a.appdate,z.BIRTHDAY)) - TRUNC(TRUNC(MONTHS_BETWEEN(a.appdate,z.BIRTHDAY) / 12) * 12),’00’) || ’ヵ月’ ) 年齢,

 

c3.DEPTSPECIALNAME 最終受入科,

 c9.NAME 来院方法,

 FW.WARDSUBNAME 入院病棟,

 translate(a.TRIAGEFREECOMMENT,’#’||CHR(10),’#’) トリアージ入力コメント,

 

case

when instr(a.TRIAGEFREECOMMENT,’キャンセル’) <> 0 then ’-’

when substr(a.TRIAGEFREECOMMENT,1,5) <= ’08:29’ then ’深夜’

when substr(a.TRIAGEFREECOMMENT,1,5) <= ’16:29’ then ’日勤’

when substr(a.TRIAGEFREECOMMENT,1,5) <= ’23:59’ then ’準夜’

else ’-’

end 日準深 ,

 

decode(instr(a.TRIAGEFREECOMMENT,’AC’),0,’’,’●’) 救急車,

decode(instr(a.TRIAGEFREECOMMENT,’*’),0,’’,’●’) 帰宅,

decode(instr(a.TRIAGEFREECOMMENT,’★’),0,’’,’●’) 死亡,

 c8.NAME 転帰,

 

a.DISEASENAME1 主訴1,

a.DISEASENAME2 主訴2,

a.DISEASENAME3 主訴3,

 

--to_char(a.appdate,’DY’) 曜日,

--decode(cc.HOLIDAYSTATUS,’Y’,’1’,’0’) 休日,

--to_date(z.BIRTHDAY) 生年月日,

 

--o1.O20 処方,

--o1.O30 注射,

--o1.O41 処置,

--o1.O60 検査,

--o1.O70 画像,

--o1.CALL - ( o1.O20 + o1.O30 + o1.O41 + o1.O60 + o1.O70 ) その他,

 

-- decode(a.VISITSTATUS,’0’,’未’,’1’,’待’,’2’,’休’,’3’,’終’,’4’,’CAN’,a.VISITSTATUS) 状態,

--substr(a.TRIAGEFREECOMMENT,1,2) 来院時,

--substr(a.TRIAGEFREECOMMENT,1,5) 来院時間,

--null 病棟,

-- nvl(FW.WARDNAME,’外来’) 現病棟,

 

a.updateterminal 操作端末,

a.updatedate 操作時間

 

--r.ADDRESSNAME1 住所,

--null 診療科,

--null トリアージ

 

from ( select * from G_EMERGENCYDETAIL

where appdate BETWEEN ’<p>DATE2|対象日開始|AddDays(-1)</p>’ and ’<p>DATE2|対象日終了|TODAY</p>’

) a

 

left join M_COMCALENDER cc on cc.CALENDERDATE = to_char(a.appdate,’YYYYMMDD’)

 

--left join M_COMDEPARTMENT c1 on c1.DEPTCODE = a.DEPARTMENTCODE

--left join M_COMDEPARTMENT c2 on c2.DEPTCODE = a.REQUESTDEPARTMENT

left join M_COMDEPARTMENT c3 on c3.DEPTCODE = a.LASTDEPARTMENTCODE

 

left join ( select * from M_COMSTAFF b1

where VALIDENDDATE = ( select max(VALIDENDDATE) from M_COMSTAFF b2 where b1.STAFFCODE = b2.STAFFCODE )

) s on a.UPDATEOPERATOR = s.STAFFCODE

 

left join Z_IBAPATIENT z on a.PATIENTNO = z.PATIENTNO

 

left join ( select * from Z_ADMADMISSION where ADMISSIONSTATUS in (’3’,’4’) ) za on za.PATIENTNO = a.PATIENTNO

left join M_COMWARD FW on za.CURRENTWARD= FW.WARDCODE

 

left join Z_IBAADDRESS r on a.PATIENTNO = r.PATIENTNO and r.ADDRESSSTATUS = ’0’

 

LEFT JOIN

( select

sum(decode(KINDOFORDER,’20’,1)) O20,

sum(decode(KINDOFORDER,’30’,1)) O30,

sum(decode(KINDOFORDER,’41’,1)) O41,

sum(decode(KINDOFORDER,’60’,1)) O60,

sum(decode(KINDOFORDER,’70’,1)) O70,

 

count(*) CALL,

 PATIENTNO,STARTDATE FROM C_COMMONORDER

where STARTDATE BETWEEN ’<p>DATE2|対象日開始|AddDays(-1)</p>’ and ’<p>DATE2|対象日終了|TODAY</p>’

and KINDOFORDER <= ’99’

group by PATIENTNO,STARTDATE ) o1 on o1.PATIENTNO = a.PATIENTNO and o1.STARTDATE = a.appdate

 

left join Z_ADMADMISSION aa on aa.PATIENTNO = a.PATIENTNO and aa.ADMISSIONDATE = a.appdate

 

left join M_COMCONST c9 on c9.TABLEKIND = ’ER_TRANSPORTMEASURES’ and c9.CODE = a.TRANSPORTCATEGORY

left join M_COMCONST c8 on c8.TABLEKIND = ’ER_OUTCOMESTATUS’ and c8.CODE = a.OUTCOME

 

where a.INVALIDSTATUS = ’0’

 

-- order by a.appdate,a.updatedate,a.PATIENTNO

order by a.appdate ,substr(a.TRIAGEFREECOMMENT,1,5)