SecondaryUse開発日記

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

スタッフ検索(ID連絡票用)

電子カルテのアカウント一覧の抽出及び一覧より新規利用者へのアカウント通知案内文の印刷が可能です

 

select

case

when M_COMSTAFF.VALIDSTARTDATE > TO_CHAR(sysdate,’YYYY/MM/DD’) and M_COMSTAFF.VALIDENDDATE >= TO_CHAR(sysdate,’YYYY/MM/DD’) then ’△’

when M_COMSTAFF.VALIDSTARTDATE <= TO_CHAR(sysdate,’YYYY/MM/DD’) and M_COMSTAFF.VALIDENDDATE >= TO_CHAR(sysdate,’YYYY/MM/DD’) then ’○’ else ’×’ end 有無,

 

replace(M_COMWARD.WARDNAME,’外来’,null) 病棟名,

p.PROFESSIONNAME 職種名,

g.GRADESHORTNAME 役職名,

M_COMSTAFF.KANJINAME 氏名,

M_COMSTAFF.KANANAME カナ,

M_COMSTAFF.STAFFCODE ユーザーID,

M_COMSTAFF.PASSWORD パスワード,

M_COMSTAFF.LICENSECODE1 麻薬施用者番号,

M_COMSTAFF.GENERATIONNO 世代,

(M_COMSTAFF.GENGOU || ’ ’ || M_COMSTAFF.BIRTHDAY ) 生年月日,

M_COMSTAFF.UPDATETERMINAL 更新端末,

TO_CHAR(M_COMSTAFF.VALIDSTARTDATE,’YYYY/MM/DD’) 適用開始,

TO_CHAR(M_COMSTAFF.VALIDENDDATE,’YYYY/MM/DD’) 適用終了,

TO_CHAR(M_COMSTAFF.UPDATEDATE,’YYYY/MM/DD’) 更新日,

mc1.DEPTSHORTNAME 担当科1,

mc2.DEPTSHORTNAME 担当科2,

mc3.DEPTSHORTNAME 担当科3,

mc4.DEPTSHORTNAME 担当科4,

mc5.DEPTSHORTNAME 担当科5,

mc6.DEPTSHORTNAME 担当科6,

mc7.DEPTSHORTNAME 担当科7,

mc8.DEPTSHORTNAME 担当科8,

mc9.DEPTSHORTNAME 担当科9

 

from M_COMSTAFF

left join M_COMWARD on M_COMSTAFF.WARDCODE = M_COMWARD.WARDCODE

left join M_COMPROFESSION p on M_COMSTAFF.PROFESSIONCODE = p.PROFESSIONCODE

left join M_COMGRADE g on M_COMSTAFF.DEGREECODE = g.GRADECODE

 

left join (

select * from ( select

STAFFCODE,GENERATIONNO,

lead(DEPARTMENT,0) over (partition by STAFFCODE,GENERATIONNO order by DEPARTMENT) p0,

lead(DEPARTMENT,1) over (partition by STAFFCODE,GENERATIONNO order by DEPARTMENT) p1,

lead(DEPARTMENT,2) over (partition by STAFFCODE,GENERATIONNO order by DEPARTMENT) p2,

lead(DEPARTMENT,3) over (partition by STAFFCODE,GENERATIONNO order by DEPARTMENT) p3,

lead(DEPARTMENT,4) over (partition by STAFFCODE,GENERATIONNO order by DEPARTMENT) p4,

lead(DEPARTMENT,5) over (partition by STAFFCODE,GENERATIONNO order by DEPARTMENT) p5,

lead(DEPARTMENT,6) over (partition by STAFFCODE,GENERATIONNO order by DEPARTMENT) p6,

lead(DEPARTMENT,7) over (partition by STAFFCODE,GENERATIONNO order by DEPARTMENT) p7,

lead(DEPARTMENT,8) over (partition by STAFFCODE,GENERATIONNO order by DEPARTMENT) p8,

lead(DEPARTMENT,9) over (partition by STAFFCODE,GENERATIONNO order by DEPARTMENT) p9,

row_number() over (partition by STAFFCODE,GENERATIONNO order by DEPARTMENT) rn

from M_COMSTAFFDEPARTMENT )

where rn=1

) c on c.STAFFCODE = M_COMSTAFF.STAFFCODE and c.GENERATIONNO = M_COMSTAFF.GENERATIONNO

 

left join M_COMDEPARTMENT mc1 on mc1.DEPTCODE = c.p0

left join M_COMDEPARTMENT mc2 on mc2.DEPTCODE = c.p1

left join M_COMDEPARTMENT mc3 on mc3.DEPTCODE = c.p2

left join M_COMDEPARTMENT mc4 on mc4.DEPTCODE = c.p3

left join M_COMDEPARTMENT mc5 on mc5.DEPTCODE = c.p4

left join M_COMDEPARTMENT mc6 on mc6.DEPTCODE = c.p5

left join M_COMDEPARTMENT mc7 on mc7.DEPTCODE = c.p6

left join M_COMDEPARTMENT mc8 on mc8.DEPTCODE = c.p7

left join M_COMDEPARTMENT mc9 on mc9.DEPTCODE = c.p8

left join M_COMDEPARTMENT mc10 on mc10.DEPTCODE = c.p9

 

where M_COMSTAFF.VALIDENDDATE >= TO_CHAR(sysdate,’YYYY/MM/DD’)

 order by M_COMSTAFF.VALIDSTARTDATE desc , M_COMSTAFF.UPDATEDATE DESC