|
#2
慧根2018-08-15 16:45
|
select ty.nameabbr as 区县,kcgs.zcount as 总数,kcjs.jscount as 及时数,
case when kcgs.zcount is not null and kcgs.zcount<>0 then round((kcjs.jscount/kcgs.zcount*100),3)||'%' else '0%' end 及时率
from hbrmw6.rms_county ty
left join (select dt.porta_county_id,count(distinct dt.flow_id) as zcount from v_project_survey_data dt
left join t_bpm_form_info fo on dt.flow_id=fo.flow_id where to_char(fo.send_time,'yyyy-mm-dd')>='2018-01-01'
and fo.end_time>=to_date('2018-03-01', 'yyyy-mm-dd') and fo.end_time<=to_date('2018-03-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and fo.app_type='platform' and dt.project_type1='2' and dt.project_type2 in ('2','3') and dt.pexpand3='装机模式' group by dt.porta_county_id ) kcgs on ty.int_id=kcgs.porta_county_id
left join (select dt.porta_county_id,count(distinct dt.flow_id) as jscount from v_project_survey_data dt
left join t_bpm_form_info fo on dt.flow_id=fo.flow_id where to_char(fo.send_time,'yyyy-mm-dd')>='2018-01-01'
and fo.end_time>=to_date('2018-03-01', 'yyyy-mm-dd') and fo.end_time<=to_date('2018-03-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and fo.app_type='platform' and dt.project_type1='2' and dt.project_type2 in ('2','3') and dt.pexpand3='装机模式'
and (fo.end_time-fo.send_time)<=dt.limittime group by dt.porta_county_id ) kcjs on ty.int_id=kcjs.porta_county_id
where ty.city_id='43'and ty.stateflag=0
order by ty.nameabbr;
[此贴子已经被作者于2018-8-16 14:49编辑过]