经典事例

-- 按照指定年查询该年内各月得新规案件在30天内的解决率

-- 按照指定年查询该年内各月得新规案件在30天内的解决率
SELECT
	MON.MON AS 年月
	,CASE WHEN FM_ITM.TMCI = 0 THEN 0 ELSE NVL(ROUND((FZ_ITM.TMCI/FM_ITM.TMCI)*100),0) END AS TMCI月解决率
	,CASE WHEN FM_ITM.FTMS = 0 THEN 0 ELSE NVL(ROUND((FZ_ITM.FTMS/FM_ITM.FTMS)*100),0) END AS FTMS月解决率
	,CASE WHEN FM_ITM.GTMS = 0 THEN 0 ELSE NVL(ROUND((FZ_ITM.GTMS/FM_ITM.GTMS)*100),0) END AS GTMS月解决率
FROM
	(
	-- 30天内解决的案件数
	SELECT
		ITM_NEW.MON AS MON
		,SUM(DECODE(ITM_NEW.DN_ACDGROUPID, 'TMCI', 1, 0)) AS TMCI   -- 按照部门每次加一
		,SUM(DECODE(ITM_NEW.DN_ACDGROUPID, 'FTMS', 1, 0)) AS FTMS
		,SUM(DECODE(ITM_NEW.DN_ACDGROUPID, 'GTMS', 1, 0)) AS GTMS
	FROM
		(SELECT 
			TO_NUMBER(TO_CHAR(DCI.DN_RCP_DT, 'YYYYMM')) AS MON
			,DECODE(EMP_ACDGROUP.DN_ACDGROUPID,'5181','TMCI','5182','TMCI','F','FTMS','G','GTMS',' ') AS DN_ACDGROUPID  
		FROM
			DN_CPL_ITM DCI		-- 投诉案件表
			-- 该案件最初的行动的转换状态不是新转再来源和转咨询
			,(SELECT 
				DN_RCP_ID_MIN.DN_ITM_ID AS DN_ITM_ID
				,DN_RCP_ID_MIN.DN_RCP_ID AS DN_RCP_ID
			FROM
				(
				SELECT 
					DCR.DN_ITM_ID AS DN_ITM_ID			-- 投诉id
					,MIN(DCR.DN_RCP_ID)  AS DN_RCP_ID		-- 最小的处理ID 
				FROM
					DN_CPL_RCP DCR
				WHERE
					(NOT(DCR.DN_DEL_FLG = 'Y') OR DCR.DN_DEL_FLG IS NULL)   -- 没有被删除
				GROUP BY
					DCR.DN_ITM_ID
				) DN_RCP_ID_MIN,
				DN_CPL_RCP DCR
			WHERE DCR.DN_ITM_ID = DN_RCP_ID_MIN.DN_ITM_ID		-- 投诉id
				AND DCR.DN_RCP_ID = DN_RCP_ID_MIN.DN_RCP_ID	-- 处理ID 
				AND (DCR.DN_REVERSE_FG NOT IN ('3', '7') OR
			                       DCR.DN_REVERSE_FG IS NULL)	-- 3 新转再来源  7 转咨询
			) DN_RCP_ID_INIT,
			-- 部门的判断：由该案件的担当者(DN_CPL_ITM.DN_TNT_ID)所在的坐席组决定，坐席组是('5181', '5182')为TMCI,坐席组是(F)为FTMS,坐席组是(G)
			-- 坐席组的取得：利用该案件的担当者ID，从员工表（DN_EMPLOYEE）中查出坐席ID，再利用坐席ID在SYS_ACDGROUP_MEMBER中查找坐席组
			(
			SELECT 
				DISTINCT DE.DN_EMP_ID AS DN_TNT_ID
				,SAM.DN_ACDGROUPID AS DN_ACDGROUPID
			FROM
				DN_EMPLOYEE DE     		-- 员工表
				,SYS_ACDGROUP_MEMBER SAM  	-- 坐席组
			WHERE
				DE.DN_AGENTID = SAM.DN_AGENTID
			
				AND DN_ACDGROUPID IN ('5181', '5182', 'F', 'G') 
			) EMP_ACDGROUP
			,(
			SELECT
				DTH.DN_ITM_ID AS DN_ITM_ID			-- 案件id
				,MAX(DTH.DN_TRANS_TIME)	AS DN_TRANS_TIME	-- 符合条件的最大日期
			FROM
				DN_TRANS_HIS DTH				-- 投诉履历案件
			WHERE
				DTH.DN_TRANS_CODE = '8'				-- VARCHAR2(1)
				AND DTH.DN_RCP_ID IS NULL
				AND DTH.DN_CPLCFM_DIV = '4'			-- VARCHAR2(2)
			GROUP BY
				DTH.DN_ITM_ID					-- 按照案件分组	
			) TRANS_TIME_MAX
		WHERE
			DCI.DN_ITM_ID = DN_RCP_ID_INIT.DN_ITM_ID 		-- 投诉案件id
			AND DCI.DN_TNT_ID =EMP_ACDGROUP.DN_TNT_ID
			AND DCI.DN_TNT_ID = EMP_ACDGROUP. DN_TNT_ID		-- 担当者
			AND DCI.DN_ITM_ID = TRANS_TIME_MAX.DN_ITM_ID		-- 投诉案件id
			AND DCI.DN_RCP_DT >= TO_DATE('201104','YYYYMM')		        -- 受理日
			AND DCI.DN_RCP_DT <  TO_DATE('201204','YYYYMM')
			AND (NOT(DCI.DN_DEL_FLG = 'Y') OR DCI.DN_DEL_FLG IS NULL)       -- 未删除
			AND (NOT(DCI.DN_ICROP_FLG = '1' AND DCI.DN_IMP_CPL_CD = '1') 
				OR DCI.DN_ICROP_FLG IS NULL)	-- 不包括iCROP连携的一般案件    DN_ICROP_FLG为1的为iCROP连携案件   案件重要度判断：根据DN_IMP_CPL_CD判断，1[一般]，2 重要，3 超重要
			AND DCI.DN_ITM_STATUS = '4'		-- 根据DN_ITM_STATUS表中判断 4 完结
			AND (NVL(DCI.DN_FIN_APP_DT, TRANS_TIME_MAX.DN_TRANS_TIME) - DCI.DN_RCP_DT) <= 30   -- 完结日 - 受理日<= 30
	) ITM_NEW
	GROUP BY 
		ITM_NEW.MON	-- 按月份分组
	) FZ_ITM,
	(
	-- 新规的所有案件
	SELECT
		ITM_NEW.MON AS MON
		,SUM(DECODE(ITM_NEW.DN_ACDGROUPID, 'TMCI', 1, 0)) AS TMCI
		,SUM(DECODE(ITM_NEW.DN_ACDGROUPID, 'FTMS', 1, 0)) AS FTMS
		,SUM(DECODE(ITM_NEW.DN_ACDGROUPID, 'GTMS', 1, 0)) AS GTMS
	FROM
		(SELECT 
			TO_NUMBER(TO_CHAR(DCI.DN_RCP_DT, 'YYYYMM')) AS MON
			,DECODE(EMP_ACDGROUP.DN_ACDGROUPID,'5181','TMCI','5182','TMCI','F','FTMS','G','GTMS',' ') AS DN_ACDGROUPID
		FROM
			DN_CPL_ITM DCI		-- 投诉案件表
			-- 该案件最初的行动的转换状态不是新转再来源和转咨询
			,(SELECT 
				DN_RCP_ID_MIN.DN_ITM_ID AS DN_ITM_ID
				,DN_RCP_ID_MIN.DN_RCP_ID AS DN_RCP_ID
			FROM
				(
				SELECT 
					DCR.DN_ITM_ID AS DN_ITM_ID			-- 投诉id
					,MIN(DCR.DN_RCP_ID)  AS DN_RCP_ID		-- 最小的处理ID 
				FROM
					DN_CPL_RCP DCR
				WHERE
					(NOT(DCR.DN_DEL_FLG = 'Y') OR DCR.DN_DEL_FLG IS NULL)
				GROUP BY
					DCR.DN_ITM_ID
				) DN_RCP_ID_MIN,
				DN_CPL_RCP DCR
			WHERE DCR.DN_ITM_ID = DN_RCP_ID_MIN.DN_ITM_ID		-- 投诉id
				AND DCR.DN_RCP_ID = DN_RCP_ID_MIN.DN_RCP_ID	-- 处理ID 
				AND (DCR.DN_REVERSE_FG NOT IN ('3', '7') OR
			                       DCR.DN_REVERSE_FG IS NULL)
			) DN_RCP_ID_INIT,
			-- 部门的判断：由该案件的担当者(DN_CPL_ITM.DN_TNT_ID)所在的坐席组决定，坐席组是('5181', '5182')为TMCI,坐席组是(F)为FTMS,坐席组是(G)
			-- 坐席组的取得：利用该案件的担当者ID，从员工表（DN_EMPLOYEE）中查出坐席ID，再利用坐席ID在SYS_ACDGROUP_MEMBER中查找坐席组
			(
			SELECT 
				DISTINCT DE.DN_EMP_ID AS DN_TNT_ID
				,SAM.DN_ACDGROUPID AS DN_ACDGROUPID
			FROM
				DN_EMPLOYEE DE     		-- 员工表
				,SYS_ACDGROUP_MEMBER SAM  	-- 坐席组
			WHERE
				DE.DN_AGENTID = SAM.DN_AGENTID
			
				AND DN_ACDGROUPID IN ('5181', '5182', 'F', 'G') 
			) EMP_ACDGROUP
		WHERE
			DCI.DN_ITM_ID = DN_RCP_ID_INIT.DN_ITM_ID 		-- 投诉案件id
			AND DCI.DN_TNT_ID =EMP_ACDGROUP.DN_TNT_ID
			AND DCI.DN_TNT_ID = EMP_ACDGROUP. DN_TNT_ID		-- 担当者
			AND DCI.DN_RCP_DT >= TO_DATE('201104','YYYYMM')		        -- 受理日
			AND DCI.DN_RCP_DT <  TO_DATE('201204','YYYYMM')
			AND (NOT(DCI.DN_DEL_FLG = 'Y') OR DCI.DN_DEL_FLG IS NULL)       -- 未删除
			AND (NOT(DCI.DN_ICROP_FLG = '1' AND DCI.DN_IMP_CPL_CD = '1') 
				OR DCI.DN_ICROP_FLG IS NULL)	-- 不包括iCROP连携的一般案件    DN_ICROP_FLG为1的为iCROP连携案件   案件重要度判断：根据DN_IMP_CPL_CD判断，1[一般]，2 重要，3 超重要
	) ITM_NEW
	GROUP BY 
		ITM_NEW.MON	-- 按月份分组
	) FM_ITM
	,(
		SELECT 
			TO_NUMBER(TO_CHAR(add_months(to_date('201103', 'yyyymm'),ROWNUM),'YYYYMM'))  AS MON
		FROM
			DUAL
			CONNECT BY ROWNUM <= months_between(to_date('201203', 'yyyymm'), to_date('201104', 'yyyymm'))+1
	) MON

WHERE 
	MON.MON = FM_ITM.MON(+)
	AND MON.MON = FZ_ITM.MON(+)
ORDER BY
	MON.MON