with temp as
(
select case when apply_dept='0303' then '0303' else apply_dept_hs end VISIT_DEPT
,apply_doctor DOCTOR_CODE
,d.idcard idcard
,case when c.s_date is not null and charge_code<>'023758' and gh.ampm is null then a.charge_amount*a.herbal_amount end as 节假日门诊人次
,case when c.s_date is not null and charge_code<>'023758' and gh.ampm is null then a.charge_price*a.charge_amount*a.herbal_amount end as 节假日诊查费
,case when c.s_date is not null and charge_code='023758' and gh.ampm is null then a.charge_amount*a.herbal_amount end as 节假日急诊人次
,case when c.s_date is not null and charge_code='023758' and gh.ampm is null then a.charge_price*a.charge_amount*a.herbal_amount end as 节假日急诊诊查费
,case when c.s_date is null and charge_code<>'023758' and gh.ampm is null then a.charge_amount*a.herbal_amount end as 非节假日门诊人次
,case when c.s_date is null and charge_code<>'023758' and gh.ampm is null then a.charge_price*a.charge_amount*a.herbal_amount end as 非节假日诊查费
,case when c.s_date is null and charge_code='023758' and gh.ampm is null then a.charge_amount*a.herbal_amount end as 非节假日急诊人次
,case when c.s_date is null and charge_code='023758' and gh.ampm is null then a.charge_price*a.charge_amount*a.herbal_amount end as 非节假日急诊诊查费
,case when gh.ampm is not null
then a.charge_amount*a.herbal_amount else 0 end as 夜诊人次
,case when gh.ampm is not null
then a.charge_price*a.charge_amount*a.herbal_amount else 0 end as 夜诊金额
--增加互联网病人就诊数
,0 as 互联网人次数
,0 as 互联网诊察费
from v_mz_detail a
join VIEW_MZ_VISIT b on a.p_id=b.p_id and a.times=b.times
left join sys_holiday c on trunc(charge_date)=trunc(s_date)
left join gh_base gh on trunc(gh.request_date)=trunc(b.VISIT_DATE) and gh.doctor_code=apply_doctor and gh.ampm='y'
left join dic_doctor d on a.APPLY_DOCTOR=d.doctsn
where a.charge_code in (select charge_code from gh_zd_clinic_charge union all
select code from wj_charge_item where code in
(
'158285',
'158288',
'023745',
'023746',
'023747',
'023748',
'920738'
)
)
and charge_status<>1 and a.bill_code='2'
and report_date>=to_date('20250701','yyyymmdd')
AND report_date<to_date('20250703','yyyymmdd')
and (exists (select 1 from gh_base gh where trunc(gh.request_date)=trunc(b.VISIT_DATE) and gh.doctor_code=apply_doctor ) or apply_dept_hs like '0115%' )
union all
select case when g.dept_sn='0303' then '0303' else g.dept_sn_hs end VISIT_DEPT
,y.DOCTOR_CODE DOCTOR_CODE
,d.idcard idcard
,case when c.s_date is not null and charge_code<>'023758' and g.AMPM<>'y' then x.charge_amount end as 节假日门诊人次
,case when c.s_date is not null and charge_code<>'023758' and g.AMPM<>'y' then x.charge_price*x.charge_amount end as 节假日诊查费
,case when c.s_date is not null and charge_code='023758' and g.AMPM<>'y' then x.charge_amount end as 节假日急诊人次
,case when c.s_date is not null and charge_code='023758' and g.AMPM<>'y' then x.charge_price*x.charge_amount end as 节假日急诊诊查费
,case when c.s_date is null and charge_code<>'023758' and g.AMPM<>'y' then x.charge_amount end as 非节假日门诊人次
,case when c.s_date is null and charge_code<>'023758' and g.AMPM<>'y' then x.charge_price*x.charge_amount end as 非节假日诊查费
,case when c.s_date is null and charge_code='023758' and g.AMPM<>'y' then x.charge_amount end as 非节假日急诊人次
,case when c.s_date is null and charge_code='023758' and g.AMPM<>'y' then x.charge_price*x.charge_amount end as 非节假日急诊诊查费
,case when g.AMPM='y' then x.charge_amount else 0 end as 夜诊人次
,case when g.AMPM='y' then x.charge_price*x.charge_amount else 0 end as 夜诊金额
--增加互联网病人就诊数
,CASE WHEN g. CLINIC_TYPE in ('0P','0Q','6P','2P','3P','4P','5P') THEN x.charge_amount end as 互联网人次数
,CASE WHEN g. CLINIC_TYPE in ('0P','0Q','6P','2P','3P','4P','5P') THEN x.charge_price*x.charge_amount end as 互联网诊察费
from VIEW_GH_DETAIL x
join v_mz_visit y on x.p_id=y.p_id and x.times=y.times and x.CHARGE_AMOUNT>0
join gh_base_child gb on x.P_ID=gb.p_id and x.TIMES=gb.times
join v_gh_base_zh g on g.request_sn=gb.request_sn
left join dic_doctor d on y.doctor_code=d.doctsn
left join sys_holiday c on trunc(advice_time)=trunc(s_date)
where x.bill_code='2' and x.charge_code in (select charge_code from gh_zd_clinic_charge
union all
select code from wj_charge_item where code in
(
'158285',
'158288',
'023745',
'023746',
'023747',
'023748',
'920738'
)
) and gb.charge_flag='1' ------已缴费
and nvl(gb."COMMENT",' ') not LIKE '%转诊,作废原明细%' ------转诊有两条记录,排除转诊前原挂号记录
and g.dept_sn not IN ('0305','030501','030504','03060301','030601','070101','0707','9901','0616')
and advice_time>=to_date('20250701','yyyymmdd')
AND advice_time<to_date('20250703','yyyymmdd')
and not exists ( select 1 from GH_DEPOSIT de where de.p_id=x.P_ID and de.times=x.TIMES and de.account_sn=x.ACCOUNT_SN and de.detail_sn=x.DETAIL_SN and de.jzdw='1386')
union all
select
case when g.dept_sn='0303' then '0303' else g.dept_sn_hs end VISIT_DEPT
,y.DOCTOR_CODE DOCTOR_CODE
,d.idcard idcard
,case when c.s_date is not null and charge_code<>'023758' and g.AMPM<>'y' then x.charge_amount end as 节假日门诊人次
,case when c.s_date is not null and charge_code<>'023758' and g.AMPM<>'y' then x.charge_price*x.charge_amount end as 节假日诊查费
,case when c.s_date is not null and charge_code='023758' and g.AMPM<>'y' then x.charge_amount end as 节假日急诊人次
,case when c.s_date is not null and charge_code='023758' and g.AMPM<>'y' then x.charge_price*x.charge_amount end as 节假日急诊诊查费
,case when c.s_date is null and charge_code<>'023758' and g.AMPM<>'y' then x.charge_amount end as 非节假日门诊人次
,case when c.s_date is null and charge_code<>'023758' and g.AMPM<>'y' then x.charge_price*x.charge_amount end as 非节假日诊查费
,case when c.s_date is null and charge_code='023758' and g.AMPM<>'y' then x.charge_amount end as 非节假日急诊人次
,case when c.s_date is null and charge_code='023758' and g.AMPM<>'y' then x.charge_price*x.charge_amount end as 非节假日急诊诊查费
,case when g.AMPM='y' then x.charge_amount else 0 end as 夜诊人次
,case when g.AMPM='y' then x.charge_price*x.charge_amount else 0 end as 夜诊金额
--增加互联网病人就诊数
,CASE WHEN g. CLINIC_TYPE in ('0P','0Q','6P','2P','3P','4P','5P') THEN x.charge_amount end as 互联网人次数
,CASE WHEN g. CLINIC_TYPE in ('0P','0Q','6P','2P','3P','4P','5P') THEN x.charge_price*x.charge_amount end as 互联网诊察费
from VIEW_GH_DETAIL x
join v_mz_visit y on x.p_id=y.p_id and x.times=y.times and x.CHARGE_AMOUNT<0
join gh_base_child gb on x.P_ID=gb.p_id and x.TIMES=gb.times
join v_gh_base_zh g on g.request_sn=gb.request_sn
left join dic_doctor d on y.doctor_code=d.doctsn
left join sys_holiday c on trunc(advice_time)=trunc(s_date)
where x.bill_code='2' and x.charge_code in (select charge_code from gh_zd_clinic_charge
union all
select code from wj_charge_item where code in
(
'158285',
'158288',
'023745',
'023746',
'023747',
'023748',
'920738'
)
) and gb.charge_flag='1' ------已缴费
and nvl(gb."COMMENT",' ') LIKE '%退号%' ------退号
and nvl(gb."COMMENT",' ') not LIKE '%转诊,作废原明细%' ------转诊有两条记录,排除转诊前原挂号记录
and g.dept_sn not IN ('0305','030501','030504','03060301','030601','070101','0707','9901','0616')
and case when x.charge_date<ADVICE_TIME then advice_time else x.charge_date end>=to_date('20250701','yyyymmdd')
AND case when x.charge_date<ADVICE_TIME then advice_time else x.charge_date end<to_date('20250703','yyyymmdd')
and not exists ( select 1 from GH_DEPOSIT de where de.p_id=x.P_ID and de.times=x.TIMES and de.account_sn=x.ACCOUNT_SN and de.detail_sn=x.DETAIL_SN and de.jzdw='1386')
)
select
decode(grouping(aa.visit_dept),1,'',aa.visit_dept) as 科室编码,
decode(grouping(aa.visit_dept)+grouping(bb.name),2,'总合计:',1,bb.name||'小计:',bb.name) as 科室,
decode(grouping(cc.name),1,'',cc.name) as 开方医生,
decode(grouping(aa.DOCTOR_CODE),1,'',aa.DOCTOR_CODE) as 医生工号,
decode(grouping(aa.idcard),1,'',aa.idcard) as 医生身份证,
sum(nvl(aa.节假日门诊人次,0)) as 节假日门诊人次,
sum(nvl(aa.节假日诊查费,0)) as 节假日诊查费,
sum(nvl(aa.节假日急诊人次,0)) as 节假日急诊人次,
sum(nvl(aa.节假日急诊诊查费,0)) as 节假日急诊诊查费,
sum(nvl(aa.非节假日门诊人次,0)) as 非节假日门诊人次,
sum(nvl(aa.非节假日诊查费,0)) as 非节假日诊查费,
sum(nvl(aa.非节假日急诊人次,0)) as 非节假日急诊人次,
sum(nvl(aa.非节假日急诊诊查费,0)) as 非节假日急诊诊查费
,sum(nvl(aa.夜诊人次,0)) as 夜诊人次
,sum(nvl(aa.夜诊金额,0)) as 夜诊金额
--增加互联网病人就诊数
,sum(nvl(aa.互联网人次数,0)) as 互联网人次数
,sum(nvl(aa.互联网诊察费,0)) as 互联网诊察费
from temp aa
left join dic_dept_code bb on aa.visit_dept=bb.dept_sn
left join dic_employee cc on aa.DOCTOR_CODE=cc.emp_sn
group by
grouping sets((aa.visit_dept,aa.DOCTOR_CODE,idcard,
bb.name,
cc.name),(),(bb.name)) order by
bb.name,aa.visit_dept nulls last; 查询缓慢
最新发布