USE [finebi] GO /****** Object: StoredProcedure [dbo].[KPIIDCO000003_proc] Script Date: 2024/7/4 11:30:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[KPIIDCO000003_proc] AS BEGIN /* 指标信息： 加盟运营 KPIIDCO000003 拓展人均开店数:每月新开店总数 /每月拓展发薪总人数 该指标已经有报表取值： 每月新开店总数 DATABASE_5_161.odsdbbi.dbo.tzy_newst_money 每月拓展发薪总人数 DATABASE_5_161.odsdb_basic.dbo.count_num_HR 创建人：杨会政（20240510） */ /********************************************************************************* 处理指标源数据成基础格式表部分 ***********************************************************************************/ DROP TABLE IF EXISTS #jc SELECT settle,daqu,COUNT(DISTINCT code) ds INTO #jc FROM DATABASE_5_161.odsdbbi.dbo.tzy_newst_money WITH(nolock) WHERE settle >=CONVERT(CHAR(6),DATEADD(yy,DATEDIFF(yy,0,getdate()-10),0) ,112) GROUP BY settle,daqu UNION ALL SELECT settle,'广东战区'daqu,COUNT(DISTINCT code) ds FROM DATABASE_5_161.odsdbbi.dbo.tzy_newst_money WITH(nolock) WHERE settle >=CONVERT(CHAR(6),DATEADD(yy,DATEDIFF(yy,0,getdate()-10),0) ,112) GROUP BY settle DROP TABLE IF EXISTS #hr SELECT months,xzqu,CASE WHEN b.daqu ='广东' THEN '广东战区' ELSE b.daqu END daqu, rs,tzy INTO #hr FROM DATABASE_5_161.odsdb_basic.dbo.count_num_HR b --基础月份数据表 DROP TABLE IF EXISTS #jcyue SELECT a.settle yf,a.daqu,a.ds ds,b.tzy tzy,a.ds*1.0/b.tzy value INTO #jcyue FROM #jc a LEFT JOIN #hr b ON a.settle = b.months AND a.daqu = b.daqu ORDER by a.settle,a.daqu /********************************************************************************* 处理成呈现表部分 ***********************************************************************************/ --呈现表月度格式数据 DROP TABLE IF EXISTS #yuedu SELECT distinct LEFT(b.yf,4)+'年' nf,'控股公司' company,'广东战区' department1,b.daqu department2,'@整体'department3,a.kpicode, CASE WHEN ISNULL(a.tag2,'') ='' AND ISNULL(a.tag3,'')='' THEN a.tag1 WHEN ISNULL(a.tag2,'') <>'' AND ISNULL(a.tag3,'') ='' THEN a.tag2 ELSE a.tag3 END targetname, CASE WHEN ISNULL(a.tag2,'') ='' AND ISNULL(a.tag3,'')='' THEN '一级指标' WHEN ISNULL(a.tag2,'') <>'' AND ISNULL(a.tag3,'') ='' THEN '二级指标' ELSE '三级指标' END targetlevel,zl_1st,stat_unit,'月度' timetype,right(b.yf,2)+'月' timevalue,b.ds value0,b.tzy value1,b.value ,GETDATE() gxsj,-- '大区' weidu,'每月更新'updatemode INTO #yuedu FROM finebi.dbo.MYJ_KPIDetails_insert a , #jcyue b WHERE a.kpicode ='KPIIDCO000003'AND b.daqu <>'广东战区' INSERT INTO #yuedu--战区 SELECT distinct LEFT(b.yf,4)+'年' nf,'控股公司' company,'广东战区' department1,'开发服务中心'department2,'@整体'department3,a.kpicode, CASE WHEN ISNULL(a.tag2,'') ='' AND ISNULL(a.tag3,'')='' THEN a.tag1 WHEN ISNULL(a.tag2,'') <>'' AND ISNULL(a.tag3,'') ='' THEN a.tag2 ELSE a.tag3 END targetname, CASE WHEN ISNULL(a.tag2,'') ='' AND ISNULL(a.tag3,'')='' THEN '一级指标' WHEN ISNULL(a.tag2,'') <>'' AND ISNULL(a.tag3,'') ='' THEN '二级指标' ELSE '三级指标' END targetlevel,zl_1st,stat_unit,'月度' timetype,right(b.yf,2)+'月' timevalue,b.ds value0,b.tzy value1,b.value ,GETDATE() gxsj,-- '战区' weidu,'每月更新'updatemode FROM finebi.dbo.MYJ_KPIDetails_insert a , #jcyue b WHERE a.kpicode ='KPIIDCO000003'AND b.daqu ='广东战区' ----呈现表季度格式数据 DROP TABLE IF EXISTS #jidu SELECT a.nf,a.company,a.department1,a.department2,a.department3,a.kpicode,a.targetname,a.targetlevel,a.zl_1st,a.stat_unit,'季度'timetype, CASE WHEN timevalue IN ('01月','02月','03月') THEN '第1季度' WHEN timevalue IN ('04月','05月','06月') THEN '第2季度' WHEN timevalue IN ('07月','08月','09月') THEN '第3季度' ELSE '第4季度' END timevalue, SUM(value0)*1.0/SUM(value1) value,GETDATE() gxsj,weidu,updatemode INTO #jidu FROM #yuedu a GROUP BY a.nf,a.company,a.department1,a.department2,a.department3,a.kpicode,a.targetname,a.targetlevel,a.zl_1st,a.stat_unit, CASE WHEN timevalue IN ('01月','02月','03月') THEN '第1季度' WHEN timevalue IN ('04月','05月','06月') THEN '第2季度' WHEN timevalue IN ('07月','08月','09月') THEN '第3季度' ELSE '第4季度' END ,weidu,updatemode ----呈现表半年度格式数据 DROP TABLE IF EXISTS #banniandu SELECT a.nf,a.company,a.department1,a.department2,a.department3,a.kpicode,a.targetname,a.targetlevel,a.zl_1st,a.stat_unit,'半年度'timetype, CASE WHEN timevalue IN ('01月','02月','03月','04月','05月','06月') THEN '上半年' ELSE '下半年' end timevalue, SUM(value0)*1.0/SUM(value1) value,GETDATE