/*******************************
--創建勞動保障公務專項工作統計表a2
*******************************/
create or replace view v_a2_1 as
select a.jcid jcid,a.dwid dwid,c.dwxz dwxz,a.jctj jctj,a.ldzrs ldzrs,decode(b.aj_id,null,'0',b.aj_id) ajid
			,b.aj_flag aj_flag,d.JCSM_28 jzcs,b.qx qx,b.aj_lasj aj_lasj,b.fkje fkje,a.jcrq sj,d.jcsm_05 qtwlrs
	from zf_jcxx a,aj_djb b,dw_xx c,aj_jcxg d
 where a.jcid = b.jcid(+) and a.dwid = c.dwid(+) and b.aj_id = d.aj_id(+)
/
create or replace view v_a2_2 as
select a.njid njid,a.sfhg sfhg,a.blrq sj,b.dwxz dwxz,b.qx qx,b.dwid
	from zf_njxx a ,dw_xx b
 where a.dwid = b.dwid(+)
/



--勞動保障公務專項工作統計表第一行合計
select 1 xh,count(distinct(decode(jctj,'02',dwid,'03',dwid))) col1,sum(decode(jctj,'02',ldzrs,'03',ldzrs,0)) col2
			,sum(decode(jctj,'01',1,0)) col3,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)) col4
			,sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0)) col5,sum(decode(jctj,'01',ldzrs,0)) col6
			,sum(decode(jctj,'01',decode(aj_flag,'02',1,0),0)) col7
			,round((sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0))*100/decode(sum(decode(jctj,'01',decode(ajid,'0',0,1),0)),0,null,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)))),2) col8
			,'' col9,count(distinct(decode(jctj,'04',dwid))) col10,sum(decode(jctj,'04',ldzrs,0)) col11,'' col12
			,'' col13,count(distinct(dwid)) col14,sum(decode(jzcs,null,0,jzcs)) col15
	from v_a2_1
 where 1=1
/

--勞動保障公務專項工作統計表第2行分類統計
select 2 xh,count(distinct(decode(jctj,'02',dwid,'03',dwid))) col1,sum(decode(jctj,'02',ldzrs,'03',ldzrs,0)) col2
			,sum(decode(jctj,'01',1,0)) col3,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)) col4
			,sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0)) col5,sum(decode(jctj,'01',ldzrs,0)) col6
			,sum(decode(jctj,'01',decode(aj_flag,'02',1,0),0)) col7
			,round((sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0))*100/decode(sum(decode(jctj,'01',decode(ajid,'0',0,1),0)),0,null,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)))),2) col8
			,'' col9,count(distinct(decode(jctj,'04',dwid))) col10,sum(decode(jctj,'04',ldzrs,0)) col11,'' col12
			,'' col13,count(distinct(dwid)) col14,sum(decode(jzcs,null,0,jzcs)) col15
	from v_a2_1
 where 1=1 and dwxz = '01'
/
--勞動保障公務專項工作統計表第3行分類統計
select 3 xh,count(distinct(decode(jctj,'02',dwid,'03',dwid))) col1,sum(decode(jctj,'02',ldzrs,'03',ldzrs,0)) col2
			,sum(decode(jctj,'01',1,0)) col3,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)) col4
			,sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0)) col5,sum(decode(jctj,'01',ldzrs,0)) col6
			,sum(decode(jctj,'01',decode(aj_flag,'02',1,0),0)) col7
			,round((sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0))*100/decode(sum(decode(jctj,'01',decode(ajid,'0',0,1),0)),0,null,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)))),2) col8
			,'' col9,count(distinct(decode(jctj,'04',dwid))) col10,sum(decode(jctj,'04',ldzrs,0)) col11,'' col12
			,'' col13,count(distinct(dwid)) col14,sum(decode(jzcs,null,0,jzcs)) col15
	from v_a2_1
 where 1=1 and dwxz = '02'
/
--勞動保障公務專項工作統計表第4行分類統計
select 4 xh,count(distinct(decode(jctj,'02',dwid,'03',dwid))) col1,sum(decode(jctj,'02',ldzrs,'03',ldzrs,0)) col2
			,sum(decode(jctj,'01',1,0)) col3,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)) col4
			,sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0)) col5,sum(decode(jctj,'01',ldzrs,0)) col6
			,sum(decode(jctj,'01',decode(aj_flag,'02',1,0),0)) col7
			,round((sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0))*100/decode(sum(decode(jctj,'01',decode(ajid,'0',0,1),0)),0,null,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)))),2) col8
			,'' col9,count(distinct(decode(jctj,'04',dwid))) col10,sum(decode(jctj,'04',ldzrs,0)) col11,'' col12
			,'' col13,count(distinct(dwid)) col14,sum(decode(jzcs,null,0,jzcs)) col15
	from v_a2_1
 where 1=1 and dwxz = '03'
/
--勞動保障公務專項工作統計表第5行分類統計
select 5 xh,count(distinct(decode(jctj,'02',dwid,'03',dwid))) col1,sum(decode(jctj,'02',ldzrs,'03',ldzrs,0)) col2
			,sum(decode(jctj,'01',1,0)) col3,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)) col4
			,sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0)) col5,sum(decode(jctj,'01',ldzrs,0)) col6
			,sum(decode(jctj,'01',decode(aj_flag,'02',1,0),0)) col7
			,round((sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0))*100/decode(sum(decode(jctj,'01',decode(ajid,'0',0,1),0)),0,null,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)))),2) col8
			,'' col9,count(distinct(decode(jctj,'04',dwid))) col10,sum(decode(jctj,'04',ldzrs,0)) col11,'' col12
			,'' col13,count(distinct(dwid)) col14,sum(decode(jzcs,null,0,jzcs)) col15
	from v_a2_1
 where 1=1 and dwxz = '04'
/
--勞動保障公務專項工作統計表第6行分類統計
select 6 xh,count(distinct(decode(jctj,'02',dwid,'03',dwid))) col1,sum(decode(jctj,'02',ldzrs,'03',ldzrs,0)) col2
			,sum(decode(jctj,'01',1,0)) col3,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)) col4
			,sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0)) col5,sum(decode(jctj,'01',ldzrs,0)) col6
			,sum(decode(jctj,'01',decode(aj_flag,'02',1,0),0)) col7
			,round((sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0))*100/decode(sum(decode(jctj,'01',decode(ajid,'0',0,1),0)),0,null,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)))),2) col8
			,'' col9,count(distinct(decode(jctj,'04',dwid))) col10,sum(decode(jctj,'04',ldzrs,0)) col11,'' col12
			,'' col13,count(distinct(dwid)) col14,sum(decode(jzcs,null,0,jzcs)) col15
	from v_a2_1
 where 1=1 and dwxz = '05'
/
--勞動保障公務專項工作統計表第7行分類統計
select 7 xh,count(distinct(decode(jctj,'02',dwid,'03',dwid))) col1,sum(decode(jctj,'02',ldzrs,'03',ldzrs,0)) col2
			,sum(decode(jctj,'01',1,0)) col3,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)) col4
			,sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0)) col5,sum(decode(jctj,'01',ldzrs,0)) col6
			,sum(decode(jctj,'01',decode(aj_flag,'02',1,0),0)) col7
			,round((sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0))*100/decode(sum(decode(jctj,'01',decode(ajid,'0',0,1),0)),0,null,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)))),2) col8
			,'' col9,count(distinct(decode(jctj,'04',dwid))) col10,sum(decode(jctj,'04',ldzrs,0)) col11,'' col12
			,'' col13,count(distinct(dwid)) col14,sum(decode(jzcs,null,0,jzcs)) col15
	from v_a2_1
 where 1=1 and dwxz = '06'
/
--勞動保障公務專項工作統計表第8行分類統計
select 8 xh,count(distinct(decode(jctj,'02',dwid,'03',dwid))) col1,sum(decode(jctj,'02',ldzrs,'03',ldzrs,0)) col2
			,sum(decode(jctj,'01',1,0)) col3,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)) col4
			,sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0)) col5,sum(decode(jctj,'01',ldzrs,0)) col6
			,sum(decode(jctj,'01',decode(aj_flag,'02',1,0),0)) col7
			,round((sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0))*100/decode(sum(decode(jctj,'01',decode(ajid,'0',0,1),0)),0,null,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)))),2) col8
			,'' col9,count(distinct(decode(jctj,'04',dwid))) col10,sum(decode(jctj,'04',ldzrs,0)) col11,'' col12
			,'' col13,count(distinct(dwid)) col14,sum(decode(jzcs,null,0,jzcs)) col15
	from v_a2_1
 where 1=1 and dwxz = '07'
/
--勞動保障公務專項工作統計表第9行分類統計
select 9 xh,count(distinct(decode(jctj,'02',dwid,'03',dwid))) col1,sum(decode(jctj,'02',ldzrs,'03',ldzrs,0)) col2
			,sum(decode(jctj,'01',1,0)) col3,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)) col4
			,sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0)) col5,sum(decode(jctj,'01',ldzrs,0)) col6
			,sum(decode(jctj,'01',decode(aj_flag,'02',1,0),0)) col7
			,round((sum(decode(jctj,'01',decode(aj_flag,'04',1,'03',1,0),0))*100/decode(sum(decode(jctj,'01',decode(ajid,'0',0,1),0)),0,null,sum(decode(jctj,'01',decode(ajid,'0',0,1),0)))),2) col8
			,'' col9,count(distinct(decode(jctj,'04',dwid))) col10,sum(decode(jctj,'04',ldzrs,0)) col11,'' col12
			,'' col13,count(distinct(dwid)) col14,sum(decode(jzcs,null,0,jzcs)) col15
	from v_a2_1
 where 1=1 and dwxz = '08'
/

--勞動保障公務專項工作統計表補充資料
select 10 xh,count(distinct(dwid)) col1,count(distinct(decode(aj_flag,'04',dwid))) col2,count(distinct(decode(fkje,null,null,dwid))) col3
			,sum(decode(fkje,null,0,fkje)) col4,sum(qtwlrs) col5,'' col6
	from v_a2_1
 where 1=1
/

--取出不合各單位數
select 12 xh ,count(distinct(dwid)) row1,count(distinct(decode(dwxz,'01',dwid))) row2
			,count(distinct(decode(dwxz,'02',dwid))) row3,count(distinct(decode(dwxz,'03',dwid))) row4
			,count(distinct(decode(dwxz,'04',dwid))) row5,count(distinct(decode(dwxz,'05',dwid))) row6
			,count(distinct(decode(dwxz,'06',dwid))) row7,count(distinct(decode(dwxz,'07',dwid))) row8
			,count(distinct(decode(dwxz,'08',dwid))) row9
	from v_a2_2
 where 1=1 and sfhg = '02'
/