select aa.科室 || ',' || aa.医师姓名 || ',' || aa.职称 || ','||
(case when to_number( to_char(sysdate, 'hh24')) < 12 then '上午' else '下午' end) || ',' || aa.出诊状态 || ',' || CASE
WHEN aa.号源 - aa.已挂 < 0 THEN
0
ELSE
aa.号源 - aa.已挂
END data2
from (select fun_getdeptname(a.zkid) 科室,
a.ysxm 医师姓名,
(select e.mc
from xtgl_ddlbn e
where e.lb = '0022'
and e.dm = a.gzdm) 职称,
decode(a.ztbz, 1, '出诊', '停诊') 出诊状态,
a.zkxh 号源,
count(1) 已挂
from yyfz_yspb a, yyfz_yyxx b
where a.pbid = b.pbid
and b.ztbz <> 9
and trunc(a.sbsj) = trunc(sysdate)
and ((to_char(a.sbsj, 'hh24:mi') < '12:00' and
to_char(sysdate, 'hh24:mi') < '12:00') or
(to_char(a.sbsj, 'hh24:mi') > '12:00' and
to_char(sysdate, 'hh24:mi') > '12:00'))
group by b.pbid,
a.zkid,
a.ysxm,
a.ztbz,
a.zkxh,
a.gzdm,
to_char(a.sbsj, 'hh24:mi')) AA
order by AA.科室;
