|
CREATE OR REPLACE procedure OPT.proposal_valid_weekly(input_date in date)
as
v_date date;
begin
v_date:=trunc(input_date);
delete from f_proposal_valid_weekly where the_date=v_date+1;
insert into f_proposal_valid_weekly(the_date,group_name,realname,proposal_total,proposal_patient_total,replay3_total,replay3_patient_total,prensent,tel,booking,patientsignin)
with pa
as
(select DISPLAYNAME,realname,id,patientid,spaceid,ctime
from
(select rig1.DISPLAYNAME,rio.realname,rp.id,rp.patientid,rp.spaceid,count(distinct po.id) spacepost,dref.ctime
from opt.r_proposals rp
left join opt.r_doctorpatientrefs dref on hostid=rp.id
left join r_doctorpatientposts po on po.doctorpatientrefid=dref.id and po.status=1 and po.userid=dref.spaceid
join opt.r_inspectgroups rig on rig.id=rp.inspectgroupid
join opt.r_inspectors rio on rp.inspectorid=rio.id and rio.realname not like '%测试%'
join opt.r_inspectgroups rig1 on rig.parentid=rig1.id
where rp.ctime>=v_date-9
and rp.ctime<v_date+1
and rp.servicedef='FLOW'
and po.ctime<v_date+1
and rp.status='CLOSED'
and rp.result='FINISH'
--and dref.ctime>=rp.utime
group by rig1.DISPLAYNAME,realname,rp.id,rp.patientid,rp.spaceid,dref.ctime
)
这是一段存储过程 |
|