Select Decode(b.出院日期, Null, Decode(b.状态, 1, 0, 3, 3, Decode(b.住院医师, '中联', 1, 2)),
Decode(b.出院方式, '死亡', 5, 4)) As 排序, Decode(Nvl(b.病案状态, 0), 0, 999, b.病案状态) As 排序2,
Decode(b.出院日期, Null,
Decode(b.状态, 1, '待入科病人', 3, '预出院病人', Decode(b.住院医师, '中联', '我的在院病人', '在院病人')),
Decode(b.出院方式, '死亡', '死亡病人', '出院病人')) As 类型, a.病人id, b.主页id, a.就诊卡号, b.住院号,
a.姓名, a.性别, a.年龄, c.名称 As 科室, b.住院医师, b.出院病床 As 床号, b.费别, b.入院日期, b.出院日期, b.状态,
b.险类, b.病案状态, -null As 医嘱id, -null As 发送号, -null As 执行状态, -null As 执行科室id, Null As 会诊内容
From 病人信息 a, 病案主页 b, 部门表 c
Where a.病人id = b.病人id And Nvl(b.主页id, 0)0 And (1 = 1 Or Nvl(b.状态, 0)1) And b.当前病区id = 81 And
b.出院科室id = c.Id And (10 And b.出院日期 Is Null Or 10 And b.出院日期 Between to_date('2008-11-14 00:00:00','yyyy-mm-dd hh24:mi:ss') And to_date('2009-01-22 23:59:59','yyyy-mm-dd hh24:mi:ss')) And
Nvl(b.病案状态, 0)5 And b.封存时间 Is Null
Union All
Select 6 As 排序, Decode(d.执行状态, 1, 1, 0) As 排序2, '会诊病人' As 类型, a.病人id, b.主页id, a.就诊卡号, b.住院号,
a.姓名, a.性别, a.年龄, g.名称 As 科室, b.住院医师, b.出院病床 As 床号, b.费别, b.入院日期, b.出院日期, b.状态,
b.险类, b.病案状态, d.医嘱id, d.发送号, Decode(d.执行状态, 1, 1, 0) As 执行状态, d.执行部门id As 执行科室id,
g.名称 || '病人,' || Decode(d.执行状态, 1, '已完成' || e.医嘱内容,
'请于' || To_Char(e.开始执行时间, 'MM.DD HH24:MI') || '进行' || e.医嘱内容 ||
Decode(e.医生嘱托, Null, Null, '(' || e.医生嘱托 || ')')) As 会诊内容
From 病人信息 a, 病案主页 b, 病人医嘱发送 d, 病人医嘱记录 e, 诊疗项目目录 f, 部门表 g
Where a.病人id = b.病人id And Nvl(b.主页id, 0)0 And e.病人科室id = g.Id And b.出院日期 Is Null And
Nvl(b.状态, 0)3 And b.病人id = e.病人id And b.主页id = e.主页id And d.医嘱id = e.Id And e.诊疗项目id = f.Id And
f.类别 = 'Z' And f.操作类型 = '7' And d.发送时间 Between to_date('2008-11-14 00:00:00','yyyy-mm-dd hh24:mi:ss') And to_date('2009-01-21 23:59:59','yyyy-mm-dd hh24:mi:ss') And
d.执行部门id + 0 In (Select 科室id From 病区科室对应 Where 病区id = 81) And Nvl(b.病案状态, 0)5 And
b.封存时间 Is Null
Union All
Select Distinct 7 As 排序, Decode(Nvl(b.病案状态, 0), 0, 999, b.病案状态) As 排序2, '转出病人' As 类型, a.病人id,
b.主页id, a.就诊卡号, b.住院号, a.姓名, a.性别, a.年龄, d.名称 As 科室, c.经治医师 As 住院医师, c.床号,
b.费别, b.入院日期, b.出院日期, b.状态, b.险类, b.病案状态, -null As 医嘱id, -null As 发送号,
-null As 执行状态, -null As 执行科室id, Null As 会诊内容
From 病人信息 a, 病案主页 b, 病人变动记录 c, 部门表 d
Where a.病人id = b.病人id And Nvl(b.主页id, 0)0 And b.出院科室id = d.Id And Nvl(b.状态, 0)2 And
b.当前病区id81 And Nvl(c.附加床位, 0) = 0 And b.病人id = c.病人id And b.主页id = c.主页id And
c.病区id = 81 And c.终止原因 = 3 And c.终止时间 Between Sysdate - 30 And Sysdate And
Nvl(b.病案状态, 0)5 And b.封存时间 Is Null
Order By 排序, 排序2, 住院号 Desc, 主页id Desc
|
