指标完成情况对比查询sql

指标完成情况对比查询sql

1. 需求

2. SQL

select
	--部门
	dept.name as bm,
	--年度指标任务-新签（万元）
	ndzbwh.nxqndzbrw as nxqndzbrw,
	--年度指标任务-收入（万元）
	ndzbwh.nsrndzbrw as nsrndzbrw,
	--年度指标任务-回款（万元）
	ndzbwh.nhkndzbrw as nhkndzbrw,
	--指标完成情况-新签（万元）-取部门新签合同维护
	case when isnull(bmxqhtwh.nxqzbwcqk,0) = 0 then 0 else isnull(bmxqhtwh.nxqzbwcqk,0)/10000 end as nxqzbwcqk,
	--指标完成情况-收入（万元）-取项目产值及回款填报
	case when isnull(xmczjhktbfzpxend.nsrzbwcqk,0) = 0 then 0 else isnull(xmczjhktbfzpxend.nsrzbwcqk,0)/10000 end as nsrzbwcqk,
	--指标完成情况-回款（万元）-取部门收款明细维护
	case when isnull(bmskmxwh.nhkzbwcqk,0) = 0 then 0 else isnull(bmskmxwh.nhkzbwcqk,0)/10000 end as nhkzbwcqk,
	--指标完成情况百分比%-新签
	case when isnull(ndzbwh.nxqndzbrw,0) = 0 then '0%' else convert(varchar(200),convert(decimal(18,2),isnull(bmxqhtwh.nxqzbwcqk,0)/isnull(ndzbwh.nxqndzbrw,0)/100))+'%' end as nxqzbwcqkbfb,
	--指标完成情况百分比%-收入
	case when isnull(ndzbwh.nsrndzbrw,0) = 0 then '0%' else convert(varchar(200),convert(decimal(18,2),isnull(xmczjhktbfzpxend.nsrzbwcqk,0)/isnull(ndzbwh.nsrndzbrw,0)/100))+'%' end as nsrzbwcqkbfb,
	--指标完成情况百分比%-回款
	case when isnull(ndzbwh.nhkndzbrw,0) = 0 then '0%' else convert(varchar(200),convert(decimal(18,2),isnull(bmskmxwh.nhkzbwcqk,0)/isnull(ndzbwh.nhkndzbrw,0)/100))+'%' end as nhkzbwcqkbfb
from
	org_dept dept
--年度指标维护
left join
	(
	select b.pk_dept as pk_dept, b.nxq as nxqndzbrw, b.nsr as nsrndzbrw, b.nhk as nhkndzbrw
	from zkch_ndzbwh h
	left join zkch_ndzbwh_b b on h.pk_ndzbwh = b.pk_ndzbwh
	left join bd_accperiod acc on acc.pk_accperiod = h.pk_zbnd
	where isnull(h.dr, 0) = 0
	and isnull(b.dr, 0) = 0
	and isnull(acc.dr, 0) = 0
	and acc.periodyear = '2023'
	) ndzbwh
on ndzbwh.pk_dept = dept.pk_dept
--部门新签合同维护
left join
	(
	select
		h.tbdept as tbdept, sum (b.je) as nxqzbwcqk
	from jygyl_bmxqhtwh h
	left join jygyl_bmxqhtwh_b b on b.pk_bmxqhtwh = h.pk_bmxqhtwh
	left join bd_accperiodmonth acc on acc.pk_accperiodmonth = h.tbmonth
	where isnull(h.dr, 0) = 0
	and isnull(b.dr, 0) = 0
	and isnull(acc.dr, 0) = 0
	and acc.yearmth >= '2023-01'
	and acc.yearmth <= '2023-04'
	group by tbdept
	) bmxqhtwh
on bmxqhtwh.tbdept = dept.pk_dept
--部门收款明细维护
left join
	(
	select
		h.tbbm as tbbm, sum(b.dnxj) as nhkzbwcqk
	from jygyl_bmskmxwh h
	left join jygyl_bmskmxwh_b b on b.pk_bmskmxwh = h.pk_bmskmxwh
	left join bd_accperiodmonth acc on acc.pk_accperiodmonth = h.tbyf
	left join (select tbbm,tbyf,max(version) maxversion from jygyl_bmskmxwh where isnull(dr, 0) = 0 group by tbbm,tbyf ) temp
	on h.tbbm = temp.tbbm and h.tbyf = temp.tbyf
	where isnull(h.dr, 0) = 0
	and isnull(b.dr, 0) = 0
	and isnull(acc.dr, 0) = 0
	and version = maxversion
	and acc.yearmth = '2023-04'
	group by h.tbbm
	) bmskmxwh
on bmskmxwh.tbbm = dept.pk_dept
--项目产值及回款填报
left join
	(
	select
		xmczjhktbfzpx.pk_dept as pk_dept, sum(nsrzbwcqk) as nsrzbwcqk
	from 
		(
		select 
			row_number() over (partition by xmczjhktb.project_code order by xmczjhktb.yearmth desc) as rank,
			xmczjhktb.*
		from
			(
			select 
				mon.yearmth, 
				pro.project_code, 
				dept.pk_dept,
				(select sum(yzbhsdnljwcnum) from jygyl_xmczjhktb_b b where b.pk_xmczjhktb = h.pk_xmczjhktb and isnull(h.dr,0) = 0 and isnull(b.dr,0) = 0 and csrcid is not null and len(csrcid) > 0) as nsrzbwcqk
			from jygyl_xmczjhktb h
			inner join bd_accperiodmonth mon on h.tbyf = mon.pk_accperiodmonth
			inner join bd_project pro on h.xmbh = pro.pk_project
			inner join org_dept dept on dept.pk_dept = pro.def2
			where isnull(h.dr,0) = 0
			and isnull(mon.dr,0) = 0
			and isnull(pro.dr,0) = 0
			and isnull(dept.dr,0) = 0
			and	mon.yearmth <= '2023-04'
			) xmczjhktb
		) xmczjhktbfzpx where rank <=1
	group by xmczjhktbfzpx.pk_dept
	)xmczjhktbfzpxend
on xmczjhktbfzpxend.pk_dept = dept.pk_dept
where ndzbwh.pk_dept in (select h.pk_tbdept from jygyl_bmyhqxb h left join jygyl_bmyhqxb_b b on h.pk_bmyhqx = b.pk_bmyhqx where isnull(h.dr,0) = 0 and isnull(b.dr,0) = 0 and b.pk_user = '1001A31000000008WSNH')
and ndzbwh.pk_dept in ('1001A21000000008128D')