帆软用户frLlnxgPLQ(提问者)额，sql很长，可能没啥参考价值吧，日志处执行的sql到数据库是可以执行，sql就是一般的连表，表都是实际的数据库表select oper.*, pacu.*,e.employee_no,e.employee_name
from (select docid,
sum(ceil(zqsc / 0.5) * 0.5) zqsc,
sum(ceil(jzsc / 0.5) * 0.5) jzsc,
sum(zqtc) zqtc,
sum(jztc) jztc
from (
select trunc(sbtime) dat,
docid,
round(sum(case
when is_emergency = \'1\' then --如果是急诊只判断是否是asa，岁数满足条件的1.5倍
(xbtime - sbtime) * bs1_5 * 24 --时间相减的结果是天，*24转化成小时
else
0
end),
1) jzsc, /*急诊时长*/
round(sum(case
when is_emergency = \'2\' then --如果是择期
(case
when (sbtime < day5h and xbtime > day5h) and
(xbtime < day22h) --如果上下班时间跨越5点但是不超过22点
-- 5点后时长 * （接台的话1倍，不是接台则5倍） + 5点前的时长*(1倍 )
then
((xbtime - day5h) * (case
when isjt = 1 then
1
else
5
end) + (day5h - sbtime) * 1)
-- 如果上下班时间跨越5点，并且下班超过22点
when (sbtime < day5h and xbtime > day5h) and
(xbtime > day22h) then
(
-- 如果是接台 22点-上班时间 * 1倍，22 点后 的5倍
case
when isjt = 1 then
(day22h - sbtime) * 1 + (sbtime - day22h) * 5
--如果是开台 5点到下班时间*5倍，5点前1倍
else
(xbtime - day5h) * 5 + (day5h - sbtime) * 1
end)
--如果上下班在5点前,1倍
when xbtime < day5h then
(xbtime - sbtime) * 1
--如果上下班在5点后，并且跨越22点
when sbtime > day5h and sbtime < day22h and
xbtime > day22h then
(case
when isjt = 1 then -- 如果是接台
(day22h - sbtime) * 1 + (xbtime - day22h) * 5 -- 22点-上班时间 * 1倍，22 点后 的5倍
else --如果是开台
(xbtime - sbtime) * 5 --全5倍
end)
--如果上下班在17-22点(开台5倍，接台1倍)
when xbtime > day5h and xbtime < day22h then
((xbtime - sbtime) * (case
when isjt = 1 then
1
else
5
end))
--如果22点后全5倍
when sbtime > day22h then
(xbtime - sbtime) * 5
else
0
end
) * bs1_5 * 24
else
0
end),
1) zqsc, /*择期时长*/
sum(case
when is_emergency = \'1\' then
(case
when isjt = 1 then
0.5
else
1
end)
else
0
end) jztc, /*急诊台次**/
sum(case
when is_emergency = \'2\' then
(case
when isjt = 1 then
0.5
else
1
end)
else
0
end) zqtc /*择期台次*/
from (
select b.sam_apply_id,
b.jiaobdoc docid,
b.sbtime,
b.xbtime,
b.is_emergency,
case
when asa in (\'4\', \'5\') or
(age >= 80 or age <= 6) then
1.5
else
1
end bs1_5,
isjt,
to_date(to_char(b.sbtime, \'yyyy-mm-dd\') ||
\' 17:00:00\',
\'yyyy-mm-dd hh24:mi:ss\') day5h,
to_date(to_char(b.sbtime, \'yyyy-mm-dd\') ||
\' 22:00:00\',
\'yyyy-mm-dd hh24:mi:ss\') day22h
from (
select a.sam_apply_id,
jiaobdoc,
to_date(max(sbtime),
\'yyyy-mm-dd hh24:mi:ss\') sbtime,
to_date(min(xbtime),
\'yyyy-mm-dd hh24:mi:ss\') xbtime,
max(r.is_emergency) is_emergency,
max(a.dat - r.birthday) / 365 age,
max(o.s_asamzfj_dm) asa,
max(case
when o.narcotic_doctor_id = jiaobdoc then
0
else
1
end) isjt
from (
select t.sam_apply_id,
max(case
when n.node_name = \'jiaobanuser\' then
n.node_value
else
\'\'
end) jiaobdoc,
max(case
when n.node_name = \'jaobrensbtime\' then
n.node_value
else
\'\'
end) sbtime,
max(case
when n.node_name = \'jiaobantime\' then
n.node_value
else
\'\'
end) xbtime,
max(r.ana_beging_date) dat
from sam_emr_rec t
left join sam_apply p
on t.sam_apply_id = p.id
left join sam_emr_rec_nv n
on t.id = n.sam_emr_rec_id
left join sam_anar r
on r.sam_apply_id = t.sam_apply_id
where t.rss_emr_type_id = \'huaxi_sam_jbjl\'
and p.scheduled_date >=
to_date(\'2022-12-20\', \'yyyy-mm-dd\')
and p.scheduled_date <
to_date(\'2022-12-29\', \'yyyy-mm-dd\') + 1
and p.s_sssyzt_dm >= \'90\'
group by t.id, t.sam_apply_id
union all
/*查询最后一个接班人信息，上面是查询前面接班人信息*/
select t.sam_apply_id,
max(case
when n.node_name = \'jiebanuser\' then
n.node_value
else
\'\'
end) jiebdoc,
max(case
when n.node_name = \'jiaobantime\' then
n.node_value
else
\'\'
end) sbtime,
to_char(max(r.ana_end_date),
\'yyyy-mm-dd hh24:mi:ss\') xbtime,
max(r.ana_beging_date)
from sam_emr_rec t
inner join sam_apply p
on t.sam_apply_id = p.id
left join sam_emr_rec_nv n
on t.id = n.sam_emr_rec_id
left join sam_anar r
on r.sam_apply_id = t.sam_apply_id
where t.rss_emr_type_id = \'huaxi_sam_jbjl\'
and p.scheduled_date >=
to_date(\'2022-12-20\', \'yyyy-mm-dd\')
and p.scheduled_date <
to_date(\'2022-12-29\', \'yyyy-mm-dd\') + 1
and p.s_sssyzt_dm >= \'90\'
group by t.id, t.sam_apply_id) a
left join sam_reg r
on r.id = a.sam_apply_id
left join sam_reg_op o
on o.sam_reg_id = r.id
group by jiaobdoc, a.sam_apply_id
union all
/*查询出所有没有交接的手术，上面是所有交接了的手术*/
select r.id,
o.narcotic_doctor_id,
a.ana_beging_date,
a.ana_end_date,
r.is_emergency,
(a.ana_beging_date - r.birthday) / 365,
o.s_asamzfj_dm,
0 isjt
from sam_reg r
inner join sam_apply p
on r.id = p.id
left join sam_reg_op o
on r.id = o.sam_reg_id
left join sam_anar a
on a.sam_apply_id = r.id
left join sam_emr_rec e
on r.id = e.sam_apply_id
and e.rss_emr_type_id = \'huaxi_sam_jbjl\'
where p.scheduled_date >=
to_date(\'2022-12-20\', \'yyyy-mm-dd\')
and p.scheduled_date <
to_date(\'2022-12-29\', \'yyyy-mm-dd\') + 1
and p.s_sssyzt_dm >= \'90\'
and e.id is null
) b
)
group by docid, trunc(sbtime))
group by docid) oper
left join (
select docid, sum(fstc) fstc, sum(ceil(fssc / 0.5) * 0.5) fssc
from (
select count(docid) * 0.15 fstc,
sum((rec_out_date - rec_in_date) * 24 * (case
when floor = \'4楼\' then
0.8
when floor = \'5楼\' then
0.5
else
1
end)) fssc,
docid
from (select a.rec_in_date,
a.rec_out_date,
m.floor,
ssgd.docid
from sam_anar a
inner join sam_apply t
on a.sam_apply_id = t.id
inner join sam_reg r
on t.id = r.id
left join sam_room m
on r.sam_room_id = m.id
inner join (select r.sam_apply_id,
n.node_value docid
from sam_emr_rec r
left join sam_emr_rec_nv n
on n.sam_emr_rec_id = r.id
where r.rss_emr_type_id = \'pacu_ssgd\'
and n.node_name = \'MZYSID\') ssgd
on ssgd.sam_apply_id = t.id
where t.scheduled_date >=
to_date(\'2022-12-20\', \'yyyy-mm-dd\')
and t.scheduled_date <
to_date(\'2022-12-29\', \'yyyy-mm-dd\') + 1
and a.rec_out_date is not null
and t.s_sssyzt_dm >= \'90\')
group by docid, trunc(rec_in_date))
group by docid
) pacu
on oper.docid = pacu.docid
left join hrm_employee e on oper.docid=e.id
1min目标场景问卷
立即参与
