1 CREATE PROCEDURE [dbo].[Proc_PX_Situation] 2 @BMDateFrom nvarchar(50), --报名时间 从 3 @BMDateEnd nvarchar(50), --报名时间 到 4 @BenlonFenJu nvarchar(50) --所属分局 5 AS 6 begin 7 --declare @SqlStr nvarchar(2000); 8 create table #temp_table( 9 Row_ID int, 10 RowGuid varchar(50), --计划Guid 11 --PX_Plan 12 PlanName nvarchar(100), --培训名称 13 UnitCount int, --计划参加企业总数 14 UserCount int, --计划参加人员总数 15 --PX_BaoM 16 BMUnitCount int, --实际报名企业总数 某一个计划下面报名的 DanWeiName的数量,不重复 17 BMUserCount int, --实际报名人员总数 某一个计划下面报名的 Count(UserGuid) 18 PayUnitCount int, --实际交费企业总数 19 PayUserCount int, --实际交费人员总数 20 PXUnitCount int, --实际培训企业总数 21 PXUserCountt int , --实际培训人员总数 22 BaoMDNum nvarchar(50) --报名点 23 -- BaoMdate datetime --报名时间 24 ) 25 26 insert into #temp_table( 27 Row_ID, 28 RowGuid , --计划Guid 29 --PX_Plan 30 PlanName , --培训名称 31 UnitCount , --计划参加企业总数 32 UserCount , --计划参加人员总数 33 --PX_BaoM 34 BMUnitCount , --实际报名企业总数 某一个计划下面报名的 DanWeiName的数量,不重复 35 BMUserCount , --实际报名人员总数 某一个计划下面报名的 Count(UserGuid) 36 PayUnitCount , --实际交费企业总数 37 PayUserCount , --实际交费人员总数 38 PXUnitCount , --实际培训企业总数 39 PXUserCountt , --实际培训人员总数 40 BaoMDNum --报名点 41 -- BaoMdate --报名时间 42 ) 43 (select Row_ID, RowGuid,PlanName,'','','','','','','','','' from PX_Plan) 44 45 --统计start 46 47 --计划参加企业总数 48 if @BenlonFenJu='' 49 update a set a.UnitCount=b.UnitCount from #temp_table a, 50 ( 51 select RowGuid,UnitCount from px_plan 52 )b where a.RowGuid=b.RowGuid 53 else 54 update a set a.UnitCount=b.UnitCount from #temp_table a, 55 ( 56 select PlanGuid,UnitCount,BaoMDCode from PX_PlanBaoMD where BaoMDCode=@BenlonFenJu 57 )b where a.RowGuid=b.PlanGuid 58 59 --计划参加人员总数 60 if @BenlonFenJu='' 61 update a set a.UserCount=b.UserCount from #temp_table a, 62 ( 63 select RowGuid,UserCount from px_plan 64 )b where a.RowGuid=b.RowGuid 65 else 66 update a set a.UserCount=b.UserCount from #temp_table a, 67 ( 68 select PlanGuid,UserCount,BaoMDCode from PX_PlanBaoMD where BaoMDCode=@BenlonFenJu 69 )b where a.RowGuid=b.PlanGuid 70 71 --实际报名企业总数 72 update a set a.BMUnitCount=b.total from #temp_table a, 73 ( 74 select planguid, count(distinct DanWeiGuid) total from ( 75 select BMAddDate,planguid,DanWeiGuid from View_PX_Plan_BaoM 76 where BMAddDate >(case @BMDateFrom when '' then '1900-01-01' else @BMDateFrom end) 77 and BMAddDate<(case @BMDateEnd when '' then GETDATE() else @BMDateEnd end) 78 and BaoMDNum in (select BaoMDCode from PX_PlanBaoMD where BaoMDCode like '%'+@BenlonFenJu+'%') 79 )p group by p.planguid 80 )b where a.RowGuid=b.PlanGuid 81 82 --实际报名人员总数 83 update a set a.BMUserCount=b.total from #temp_table a, 84 ( 85 select planguid,count(UserGuid) total from ( 86 select BMAddDate,planguid,UserGuid from View_PX_Plan_BaoM 87 where BMAddDate >(case @BMDateFrom when '' then '1900-01-01' else @BMDateFrom end) 88 and BMAddDate<(case @BMDateEnd when '' then GETDATE() else @BMDateEnd end) 89 and BaoMDNum in (select BaoMDCode from PX_PlanBaoMD where BaoMDCode like '%'+@BenlonFenJu+'%') 90 )p group by p.planguid 91 )b where a.RowGuid=b.PlanGuid 92 93 --实际交费企业总数 94 update a set a.PayUnitCount=b.total from #temp_table a, 95 (select planguid,count(distinct DanWeiGuid) total from( 96 select BMAddDate,planguid,DanWeiGuid from View_PX_Plan_BaoM 97 where IsPay=1 98 and BMAddDate >(case @BMDateFrom when '' then '1900-01-01' else @BMDateFrom end) 99 and BMAddDate<(case @BMDateEnd when '' then GETDATE() else @BMDateEnd end) 100 and BaoMDNum in (select BaoMDCode from PX_PlanBaoMD where BaoMDCode like '%'+@BenlonFenJu+'%') 101 )p group by p.planguid 102 )b where a.RowGuid=b.PlanGuid 103 104 --实际交费人员总数 105 update a set a.PayUserCount=b.total from #temp_table a, 106 (select planguid,count(UserGuid) total from( 107 select BMAddDate,planguid,UserGuid from View_PX_Plan_BaoM 108 where IsPay=1 109 and BMAddDate >(case @BMDateFrom when '' then '1900-01-01' else @BMDateFrom end) 110 and BMAddDate<(case @BMDateEnd when '' then GETDATE() else @BMDateEnd end) 111 and BaoMDNum in (select BaoMDCode from PX_PlanBaoMD where BaoMDCode like '%'+@BenlonFenJu+'%') 112 )p group by p.planguid 113 )b where a.RowGuid=b.PlanGuid 114 115 --实际培训企业总数 116 update a set a.PXUnitCount=b.total from #temp_table a, 117 (select planguid,count(distinct DanWeiGuid) total from( 118 select BMAddDate,planguid,DanWeiGuid from View_PX_Plan_BaoM 119 where IsPay=1 and IsPeiXun=1 120 and BMAddDate >(case @BMDateFrom when '' then '1900-01-01' else @BMDateFrom end) 121 and BMAddDate<(case @BMDateEnd when '' then GETDATE() else @BMDateEnd end) 122 and BaoMDNum in (select BaoMDCode from PX_PlanBaoMD where BaoMDCode like '%'+@BenlonFenJu+'%') 123 )p group by p.planguid 124 )b where a.RowGuid=b.PlanGuid 125 126 --实际培训人员总数 127 update a set a.PXUserCountt=b.total from #temp_table a, 128 (select planguid,count(UserGuid) total from( 129 select BMAddDate,planguid,UserGuid from View_PX_Plan_BaoM 130 where IsPay=1 and IsPeiXun=1 131 and BMAddDate >(case @BMDateFrom when '' then '1900-01-01' else @BMDateFrom end) 132 and BMAddDate<(case @BMDateEnd when '' then GETDATE() else @BMDateEnd end) 133 and BaoMDNum in (select BaoMDCode from PX_PlanBaoMD where BaoMDCode like '%'+@BenlonFenJu+'%') 134 )p group by p.planguid 135 )b where a.RowGuid=b.PlanGuid 136 137 --报名点 138 update a set a.BaoMDNum=b.BaoMDNum from #temp_table a, 139 (select distinct planguid,BaoMDNum from View_PX_Plan_BaoM)b where a.RowGuid=b.PlanGuid 140 141 142 --报名时间 --AddDate 143 --update a set a.BaoMdate=b.BMAddDate from #temp_table a, 144 --(select planguid,DanWeiGuid, BMAddDate from View_PX_Plan_BaoM)b where a.RowGuid=b.PlanGuid 145 --统计end 146 147 select * from #temp_table order by Row_ID DESC 148 149 end 150 151 152 153 154 155 156 GO
