USE [ibcs_baotou_true] GO /****** Object: StoredProcedure [dbo].[P_RP_BaoTouRemissionCountMonthBB] Script Date: 04/15/2011 10:43:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /*《减免汇总月报表》中“本月减免”中的历史数据及“本年累计减免”中的数据应该分为本年数据和本年前数据。 《减免明细表》中“减免历史欠费”也应分为本年数据和本年前数据，目前的报表无法进行和财务的对账， 分开的目的就是为了和财务进行数据的核对*/ -- ============================================= -- Author: zhengqiao@shanghai3h.com -- Create date: <2009-12-3> -- Description: <包头市供水总公司XXXX年XX月减免汇总月报表(T26)> --------Sample:EXEC P_RP_BaoTouRemissionCountMonthBB '201002','','','',1 ALTER procedure [dbo].[P_RP_BaoTouRemissionCountMonthBB]( @countDate varchar(20),--查询年月 @waterType varchar(20)='',--用水类别 @S_ST varchar(20)='',--站点，可复选用逗号隔开 @S_CID char(16)='',--用户代码 @flag int=1--分质用水标志位；0：分质；1：非分质 ) as declare @sql varchar(3000)--临时sql变量 ,@NowMonth varchar(20)--当前账务年月 ,@JianMianTableName varchar(50)--ZW_JianMiann表 ,@JianMianZBTableName varchar(50)--ZW_JianMian_ZB表 ,@BiaoKaxxTableName VARCHAR(50) -- 表卡信息表名 ,@s_BeginDate varchar(100)--减免日期开始日期 ,@s_EndDate varchar(100)--减免日期结束日期 ,@condition varchar(1000)--拼接查询条件 ,@countYear varchar(50) ,@countMonth varchar(50) ,@exChange int--标志位 set @exChange=0 ------------------------------------------------------------ set @countYear=substring(convert(char(8),@countDate,112),1,4) --2011 set @countMonth=substring(convert(char(8),@countDate,112),5,2) --04 -------得到账务年月范围--------- select @s_BeginDate=convert(varchar(10),D_KaiShiRQ,112), @s_EndDate=convert(varchar(10),D_JieShuRQ,112) from SYS_JieZhuanRZ where I_ZhangWuNY=@countDate -- print @s_BeginDate if @s_BeginDate is null set @exChange=1--return 0 --获取表名 print @countDate select @JianMianTableName='ZW_JianMian' select @JianMianZBTableName='ZW_JianMian_ZB' select @BiaoKaxxTableName=dbo.F_GetTableName(@countDate,'KG_BiaoKaXX') print @JianMianTableName ------判断要用到的表中是否有不存在的 IF(@JianMianTableName IS NULL) BEGIN set @exChange=1--RETURN 0 END -----查询居民类别的简号 create table #TMPJMJH (I_JH INT ,I_TJH int) insert into #TMPJMJH select * from F_BaotouGetFenLeiJH(0) -----查询企业类别的简号 create table #TMPQYJH (I_JH INT,I_TJH int ) insert into #TMPQYJH select * from F_BaotouGetFenLeiJH(1) CREATE TABLE #TMPJMJH1 (I_JH INT,S_FeiLeiMC varchar(50)) insert into #TMPJMJH1 select I_JH,S_FeiLeiMC from (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) JG_YongShuiFL declare @S_JH varchar(1000)--存放简号，用逗号隔开 declare @JH varchar(20)--存放游标轮循的简号值 set @S_JH='' if @waterType<>'' begin delete from #TMPJMJH1--清空表 set @sql='SELECT distinct I_JH,S_FeiLeiMC FROM (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) [JG_YongShuiFL] where i_ancestor IN (SELECT ID FROM (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) JG_YongShuiFL WHERE I_ANCESTOR =0) AND S_FEILEIMC LIKE ''%'+@waterType+'%'' ' print @sql INSERT INTO #TMPJMJH1 EXEC(@sql) ---使用游标读取#TMPJMJH表中字段值 declare JHCursor cursor for --声明游标 select I_JH from #TMPJMJH1 open JHCursor --打开游标 fetch next from JHCursor into @JH--使游标指向第一行 -- print @JH while @@FETCH_STATUS=0 begin set @S_JH=@S_JH+@JH+',' fetch next from JHCursor into @JH end close JHCursor--关闭游标 deallocate JHCursor--释放游标占用的空间 -- print @S_JH if @S_JH<>'' set @S_JH=substring(@S_JH,1,len(@S_JH)-1) end ----拼接查询条件 print '11111' set @condition=' and a.S_CaoZuoY=''100233''' if @S_ST<>'' and @S_ST<>'00' set @condition=@condition+' and a.S_ST in('+@S_ST+')' if @S_CID<>'' set @condition=@condition+' and a.S_CID='''+@S_CID+'''' if @waterType<>'' and @S_JH<>'' set @condition=@condition+' and a.I_JH in('+@S_JH+')' select * into #biaoka from kg_biaokaxx where 1=2 set @sql=' select * from '+@BiaokaxxTableName+' where I_JiHuiYSJJFS>=0 ' insert into #biaoka exec(@sql) --定义报表临时表1 本月临时报表 create table #NowMonth(I_JH int, WaterType varchar(50),--用水分类 CountDate varchar(50), BasicFee numeric(12, 2),--基本水费 SecondFee numeric(12,2),--二级水费 OverstepPlanFee numeric(12,2),--超计划水费 LateFee numeric(12,2),--滞纳金 SewageDisposeFee numeric(12,2),--污水处理费 TotalAmount numeric(12,2),--合计本月总额 ------------------------ BasicFee1 numeric(12, 2),--历史基本水费 SecondFee1 numeric(12,2),--历史二级水费 OverstepPlanFee1 numeric(12,2),--历史超计划水费 LateFee1 numeric(12,2),--滞纳金 SewageDisposeFee1 numeric(12,2),--污水处理费 TotalAmount1 numeric(12,2),--合计历史总额 TotalCount numeric(12,2),--合计总额 ) --定义报表临时表2 本年临时报表 create table #ThisYear(I_JH int, WaterType varchar(50),--用水分类 TotalBasicFee numeric(12, 2),--基本水费 TotalSecondFee numeric(12,2),--二级水费 TotalOverstepPlanFee numeric(12,2),--超计划水费 TotalLateFee numeric(12,2),--滞纳金 TotalSewageDisposeFee numeric(12,2),--污水处理费 YearTotalAmount numeric(12,2),--合计总额 CountDate varchar(50), ) print '33333' ------------分质、非分质 declare @con varchar(100) if @flag=0 set @con=' and c.I_BiaoZhongL=3' else set @con=' and c.I_BiaoZhongL<>3' select * into #tmpJianMian from zw_jianmian where 1=2 set @sql=' select a.* from '+@JianMianTableName+' as a left join '+@BiaoKaxxTableName+' as c on a.s_cid=c.s_cid where (a.DL_CaoZuoRQ>='+@s_BeginDate+' and a.DL_CaoZuoRQ<='+@s_EndDate+') and n_je>0 and a.I_ChuLi in(4,16) '+@con+@condition print @sql insert into #tmpJianMian exec(@sql) if @exChange=0 begin ----------------本月统计临时表-------------------- --基本水费 create table #tmpBasicFee(I_JH int,N_ZongJinE numeric(18,2)) set @sql='select b.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from #tmpJianMian as a left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB=0 and b.i_leixing=1 and b.I_FeiYongDLBH=580 and (a.i_y*100+a.i_m)='+@countDate+' group by b.I_JH' insert into #tmpBasicFee exec(@sql) --二级水费 create table #tmpSecondFee(I_JH int,N_ZongJinE numeric(18,2)) set @sql='select b.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from #tmpJianMian as a left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB>0 and a.s_cid not in (select s_cid from #biaoka) and b.I_FeiYongDLBH=580 and b.i_leixing=1 and (a.i_y*100+a.i_m)='+@countDate+' group by b.I_JH' --print @sql insert into #tmpSecondFee exec(@sql) --超计划水费 create table #tmpOverstepPlanFee(I_JH int,N_ZongJinE numeric(18,2)) set @sql='select b.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from #tmpJianMian as a left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB>0 and a.s_cid in (select s_cid from #biaoka) and b.I_FeiYongDLBH=580 and b.i_leixing=1 and (a.i_y*100+a.i_m)='+@countDate+' group by b.I_JH' --print @sql insert into #tmpOverstepPlanFee exec(@sql) -----滞纳金 --set @sql='select a.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' into ##tmpLateFee from --#tmpJianMian as a --left join '+@JianMianZBTableName+' as b on --a.i_jianmianbh=b.i_jianmianbh --where a.I_JLZT=0 --and b.I_JLZT=0'+@condition+' --and (a.DL_CaoZuoRQ>='+@s_BeginDate+' and a.DL_CaoZuoRQ<='+@s_EndDate+') --and a.I_ChuLi in(4,16) --group by a.I_JH' ----print @sql --exec(@sql) --污水处理费 create table #tmpSewageDisposeFee(I_JH int,N_ZongJinE numeric(18,2)) set @sql='select b.I_JH,sum(isnull(N_ZongJinE,0)) as ''N_ZongJinE'' from #tmpJianMian as a left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB=0 and b.i_leixing=1 and b.I_FeiYongDLBH=581 and (a.i_y*100+a.i_m)='+@countDate+' group by b.I_JH' --print @sql insert into #tmpSewageDisposeFee exec(@sql) ------------------------------------ select I_JH into #tmp3 from #tmpBasicFee --本月基本水费 select I_JH into #tmp4 from #tmpSecondFee --二级水费 select I_JH into #tmp5 from #tmpOverstepPlanFee --超计划水费 select I_JH into #tmp6 from #tmpSewageDisposeFee --污水处理费 --------------本月水费所有表-------------------------------- create table #first(i_jh int, s_feileimc varchar(64) ,BasicCost numeric(18,2), SecondLevelCost numeric(18,2), OverPlanCost numeric(18,2), LateFeeCost numeric(18,2), SlopsCost numeric(18,2), AllCost numeric(18,2), YM varchar(16)) --------------合并本月水费所有表----------------- insert into #first select T.I_JH,F.S_FeiLeiMC, isnull(A.N_ZongJinE,0) as 'BasicFee', isnull(B.N_ZongJinE,0) as 'SecondFee', isnull(C.N_ZongJinE,0) as 'OverstepPlanFee', 0 as 'LateFee', isnull(D.N_ZongJinE,0) as 'SewageDisposeFee', isnull(A.N_ZongJinE,0)+isnull(B.N_ZongJinE,0)+isnull(C.N_ZongJinE,0)+isnull(D.N_ZongJinE,0) as 'TotalAmount', @countYear+'年'+@countMonth+'月' as CountDate from (select I_JH from #tmp3 union select I_JH from #tmp4 union select I_JH from #tmp5 union select I_JH from #tmp6) T left join #tmpBasicFee A on T.I_JH=A.I_JH left join #tmpSecondFee B on T.I_JH=B.I_JH left join #tmpOverstepPlanFee C on T.I_JH=C.I_JH left join #tmpSewageDisposeFee D on T.I_JH=D.I_JH --left join ##tmpLateFee E on T.I_JH=E.I_JH left join #TMPJMJH1 F on T.I_JH=F.I_JH end ------------------------------------------------------------------------------------------------------------------------------------------- ----------减免历史基本水费、减免污水处理费--------- create table #lsBasicSlops(I_JH int,BasicCost numeric(18,2)) SET @SQL='SELECT t.* FROM( SELECT b.I_JH,SUM(b.N_ZongJinE) AS BasicCost FROM #tmpJianMian AS a LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=580 AND b.I_JIETIJB=0 and b.i_leixing=1 and (a.i_y*100+a.i_m)<'+@countDate+' GROUP BY b.I_JH) t' print @sql insert into #lsBasicSlops EXEC(@SQL) --污水费 create table #lsSlops(I_JH int,SlopsCost numeric(18,2)) SET @SQL='SELECT t1.* FROM ( SELECT b.I_JH,SUM(b.N_ZongJinE) AS SlopsCost FROM #tmpJianMian AS a LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=581 AND b.I_JIETIJB=0 and b.i_leixing=1 and (a.i_y*100+a.i_m)<'+@countDate+' GROUP BY b.I_JH ) t1' print @sql insert into #lsSlops EXEC(@SQL) ----------减免历史二级水费--------------------- create table #lsSecondLevel(I_JH int,SecondLevelCost numeric(18,2)) SET @SQL='SELECT b.I_JH,SUM(b.N_ZongJinE) AS SecondLevelCost FROM #tmpJianMian AS a LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=580 AND b.I_JIETIJB>0 and b.i_leixing=1 and (a.i_y*100+a.i_m)<'+@countDate+' and a.s_cid not in (select s_cid from #biaoka) GROUP BY b.I_JH' insert into #lsSecondLevel EXEC(@SQL) ----------减免历史超计划水费------------------- create table #lsOverPlan(I_JH int,OverPlanCost numeric(18,2)) SET @SQL='SELECT b.I_JH,SUM(b.N_ZongJinE) AS OverPlanCost FROM #tmpJianMian AS a LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=580 AND b.I_JIETIJB>0 and b.i_leixing=1 and (a.i_y*100+a.i_m)<'+@countDate+' and a.s_cid in (select s_cid from #biaoka) GROUP BY b.I_JH' insert into #lsOverPlan EXEC(@SQL) --------------------创建历史水费所有表 create table #second( i_jh int, s_feileimc varchar(64), BasicCost numeric(18,2), SecondLevelCost numeric(18,2), OverPlanCost numeric(18,2), LateFeeCost numeric(18,2), SlopsCost numeric(18,2), AllCost numeric(18,2), YM varchar(16)) -----------------合并历史所有表------------------- select distinct i_jh into #lsjh from #lsBasicSlops union select i_jh from #lsSecondLevel union select i_jh from #lsOverPlan union select i_jh from #lsSlops insert into #second SELECT DISTINCT t.i_jh,S_FeiLeiMC ,isnull(a.BasicCost,0) as BasicCost ,isnull(b.SecondLevelCost,0) as SecondLevelCost ,isnull(c.OverPlanCost,0) as OverPlanCost ,0 AS LateFeeCost ,isnull(e.SlopsCost,0) as SlopsCost ,ISNULL(a.BasicCost,0)+ISNULL(e.SlopsCost,0)+ISNULL(b.SecondLevelCost,0)+ISNULL(c.OverPlanCost,0) AS AllCost , @countYear+'年'+@countMonth+'月' as CountDate FROM #lsjh AS t LEFT JOIN #lsBasicSlops AS a ON t.I_JH=a.I_JH LEFT JOIN #lsSecondLevel AS b ON t.I_JH=b.I_JH LEFT JOIN #lsOverPlan AS c ON t.I_JH=c.I_JH LEFT JOIN #lsSlops AS e ON t.I_JH=e.I_JH LEFT JOIN (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) JG_YongShuiFL ON t.I_JH=JG_YongShuiFL.I_JH --#secon 历史水费所有表 #first本月水费所有表 #NowMonth 本月临时表（包含历史和非历史所有） #ThisYear 本年临时表 不含历史 ------------------------------------------------------------------------------------------------------------------------------------------- ---本月临时统计 select distinct i_jh,S_FeiLeiMC,YM into #he from #first union select i_jh,S_FeiLeiMC,YM from #second insert into #NowMonth select a.i_jh,a.S_FeiLeiMC,a.YM, isnull(b.BasicCost,0) as BasicCost , isnull(b.SecondLevelCost,0) as SecondLevelCost, isnull(b.OverPlanCost,0) as OverPlanCost, isnull(b.LateFeeCost,0) as LateFeeCost , isnull(b.SlopsCost,0) as SlopsCost, isnull(b.AllCost,0) as AllCost, --本月合计 isnull(c.BasicCost,0) as BasicCost1, isnull(c.SecondLevelCost,0) as SecondLevelCost1,--历史二级水费 isnull(c.OverPlanCost,0) as OverPlanCost1,--历史超计划水费 isnull(c.LateFeeCost,0) as LateFeeCost1, isnull(c.SlopsCost,0) as SlopsCost1, isnull(c.AllCost,0) as AllCost1, --本月历史减免合计 isnull(b.AllCost,0)+isnull(c.AllCost,0) as Allcount from #he as a left join #first as b on a.i_jh=b.i_jh left join #second as c on a.i_jh=c.i_jh ---------------------本年统计临时表------------------ declare @year varchar(10),--当前时间的年份 @m int,--月份循环变量 @month int,--当前时间的月份 @tmpYearM varchar(50),--循环使用账务年月 @s_StartDate varchar(50), @I_EndDate varchar(50) set @year=convert(char(4),@countDate,112) set @month=cast(substring(convert(char(8),@countDate,112),5,2) as int) --获取当前输入日期的月份 set @m=1 --print @month ------------------------------------------------- ---创建基本水费临时表 create table #tmpBasicFee1(I_JH int,N_ZongJinE numeric(12,2)) ---创建二级水费临时表 create table #tmpSecondFee1(I_JH int,N_ZongJinE numeric(12,2)) ---创建超计划水费临时表 create table #tmpOverstepPlanFee1(I_JH int,N_ZongJinE numeric(12,2)) ---创建污水处理费临时表 create table #tmpSewageDisposeFee1(I_JH int,N_ZongJinE numeric(12,2)) ---创建滞纳金临时表 --create table #tmpLateFee1(I_JH int,N_ZongJinE numeric(12,2)) ----------减免历史污水费 create table #ylsSlops(I_JH int,SlopsCost numeric(18,2)) ----------减免历史基本水费 create table #ylsBasicSlops(I_JH int,BasicCost numeric(18,2)) ----------减免历史二级水费 create table #ylsSecondLevel(I_JH int,SecondLevelCost numeric(18,2)) ----------减免历史超计划水费 create table #ylsOverPlan(I_JH int,OverPlanCost numeric(18,2)) ---while循环遍历从1月到当前月的各种费用 while @m<=@month --Condition here must be int or some type inherits int begin if len(@m)=1 set @tmpYearM=@year+'0'+cast(@m as varchar(5)) else set @tmpYearM=@year+cast(@m as varchar(5)) print @tmpYearM -------得到账务年月范围--------- select @s_StartDate=convert(varchar(8),D_KaiShiRQ,112), @I_EndDate=convert(varchar(8),D_JieShuRQ,112) from SYS_JieZhuanRZ where I_ZhangWuNY=@tmpYearM print @s_StartDate -- if @s_StartDate is null begin set @m=@m+1 print @m continue--重新开始循环 end --得到当前账务年月 select @NowMonth=max(I_ZhangWuNY) from SYS_JieZhuanRZ set @JianMianTableName='ZW_JianMian' set @JianMianZBTableName='ZW_JianMian_ZB' select @BiaoKaxxTableName=dbo.F_GetTableName(@tmpYearM,'KG_BiaoKaXX') delete from #tmpJianMian set @sql=' select a.* from '+@JianMianTableName+' as a left join '+@BiaoKaxxTableName+' as c on a.s_cid=c.s_cid where (a.DL_CaoZuoRQ>='+@s_StartDate+' and a.DL_CaoZuoRQ<='+@I_EndDate+') and n_je>0 and a.I_ChuLi in(4,16) and (a.i_y*100+a.i_m)<='+@tmpYearM+@con+@condition insert into #tmpJianMian exec(@sql) -- delete from #biaoka set @sql=' select * from '+@BiaokaxxTableName+' where I_JiHuiYSJJFS>=0 ' insert into #biaoka exec(@sql) ---基本水费 set @sql='insert into #tmpBasicFee1 select b.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from #tmpJianMian as a left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB=0 and b.i_leixing=1 and b.I_FeiYongDLBH=580 group by b.I_JH' print @sql exec(@sql) print @s_BeginDate --合计本年二级水费 set @sql='insert into #tmpSecondFee1 select b.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from #tmpJianMian as a left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB>0 and b.i_leixing=1 and a.s_cid not in (select s_cid from #biaoka) and b.I_FeiYongDLBH=580 group by b.I_JH' -- print @sql exec(@sql) --合计超计划水费 set @sql='insert into #tmpOverstepPlanFee1 select b.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from #tmpJianMian as a left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB>0 and b.i_leixing=1 and a.s_cid in (select s_cid from #biaoka) and b.I_FeiYongDLBH=580 group by b.I_JH' -- print @sql exec(@sql) -- ---滞纳金 -- set @sql='insert into #tmpLateFee1 -- select a.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from -- #tmpJianMian as a -- left join '+@JianMianZBTableName+' as b on -- a.i_jianmianbh=b.i_jianmianbh -- where a.I_JLZT=0 -- and b.I_JLZT=0'+@condition+' -- and (a.DL_CaoZuoRQ>='+@s_BeginDate+' and a.DL_CaoZuoRQ<='+@s_EndDate+') -- and a.I_ChuLi in(4,16) group by a.I_JH' ---- print @sql -- exec(@sql) --合计污水处理费 set @sql='insert into #tmpSewageDisposeFee1 select b.I_JH,sum(isnull(b.N_ZongJinE,0)) as ''N_ZongJinE'' from #tmpJianMian as a left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB=0 and b.i_leixing=1 and b.I_FeiYongDLBH=581 group by b.I_JH' -- print @sql exec(@sql) ----------------------------------------------------------历史减免-------------------------------------------------------- SET @SQL='insert into #ylsBasicSlops SELECT t.* FROM( SELECT b.I_JH,SUM(b.N_ZongJinE) AS BasicCost FROM #tmpJianMian AS a LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=580 AND b.I_JIETIJB=0 and b.i_leixing=1 GROUP BY b.I_JH) t' EXEC(@SQL) print @sql SET @SQL='insert into #ylsSlops SELECT t1.* FROM ( SELECT b.I_JH,SUM(b.N_ZongJinE) AS SlopsCost FROM #tmpJianMian AS a LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=581 AND b.I_JIETIJB=0 and b.i_leixing=1 GROUP BY b.I_JH ) t1' EXEC(@SQL) print @sql SET @SQL='insert into #ylsSecondLevel SELECT b.I_JH,SUM(b.N_ZongJinE) AS SecondLevelCost FROM #tmpJianMian AS a LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=580 AND b.I_JIETIJB>0 and b.i_leixing=1 and a.s_cid not in (select s_cid from #biaoka)GROUP BY b.I_JH' EXEC(@SQL) print @sql SET @SQL='insert into #ylsOverPlan SELECT b.I_JH,SUM(b.N_ZongJinE) AS OverPlanCost FROM #tmpJianMian AS a LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=580 AND b.I_JIETIJB>0 and b.i_leixing=1 and a.s_cid in (select s_cid from #biaoka) GROUP BY b.I_JH' EXEC(@SQL) print @sql --------------------------------------------------------结束历史减免统计------------------------------------------------------ ----------------改变循环控制变量----------- set @m=@m+1 end --------------------创建历史水费所有表 create table #ysecond( i_jh int, --s_feileimc varchar(64), BasicCost numeric(18,2), SecondLevelCost numeric(18,2), OverPlanCost numeric(18,2), LateFeeCost numeric(18,2), SlopsCost numeric(18,2), AllCost numeric(18,2), YM varchar(16)) -----------------合并历史所有表------------------- select distinct i_jh into #ylsjh from #ylsBasicSlops union select i_jh from #ylsSecondLevel union select i_jh from #ylsOverPlan union select i_jh from #ylsSlops insert into #ysecond SELECT DISTINCT t.i_jh --,S_FeiLeiMC ,isnull(a.BasicCost,0) as BasicCost ,isnull(b.SecondLevelCost,0) as SecondLevelCost ,isnull(c.OverPlanCost,0) as OverPlanCost ,0 AS LateFeeCost ,isnull(e.SlopsCost,0) as SlopsCost ,ISNULL(a.BasicCost,0)+ISNULL(e.SlopsCost,0)+ISNULL(b.SecondLevelCost,0)+ISNULL(c.OverPlanCost,0) AS AllCost , @countYear+'年'+@countMonth+'月' as CountDate FROM #ylsjh AS t LEFT JOIN #ylsBasicSlops AS a ON t.I_JH=a.I_JH LEFT JOIN #ylsSecondLevel AS b ON t.I_JH=b.I_JH LEFT JOIN #ylsOverPlan AS c ON t.I_JH=c.I_JH LEFT JOIN #ylsSlops AS e ON t.I_JH=e.I_JH LEFT JOIN (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) JG_YongShuiFL ON t.I_JH=JG_YongShuiFL.I_JH --创建临时表 保存 #ysecond中部分内容 create table #tmpsec( i_jh int, yBasicCost numeric(18,2), ySecondLevelCost numeric(18,2), yOverPlanCost numeric(18,2), yLateFeeCost numeric(18,2), ySlopsCost numeric(18,2)) insert into #tmpsec select i_jh, sum(BasicCost),sum(SecondLevelCost),sum(OverPlanCost),sum(LateFeeCost),sum(SlopsCost) from #ysecond group by i_jh print 'ssssssss' ------------------------------------------------结束历史所有表---------------------------------------------------------------- select I_JH,sum(N_ZongJinE) 'N_ZongJinE' into #tmpBasic from #tmpBasicFee1 group by I_JH select I_JH,sum(N_ZongJinE) 'N_ZongJinE' into #tmpSecond from #tmpSecondFee1 group by I_JH select I_JH,sum(N_ZongJinE) 'N_ZongJinE' into #tmpOverstepPlan from #tmpOverstepPlanFee1 group by I_JH select I_JH,sum(N_ZongJinE) 'N_ZongJinE' into #tmpSewageDispose from #tmpSewageDisposeFee1 group by I_JH -- select I_JH,sum(N_ZongJinE) 'N_ZongJinE' into #tmpLate -- from #tmpLateFee1 group by I_JH select I_JH into #tmp31 from #tmpBasic select I_JH into #tmp41 from #tmpSecond select I_JH into #tmp51 from #tmpOverstepPlan select I_JH into #tmp61 from #tmpSewageDispose -- select I_JH into #tmp71 from #tmpLate -----------------------年统计--------------------------- insert into #ThisYear select T.I_JH,F.S_FeiLeiMC,--用水分类 isnull(A.N_ZongJinE,0) as 'TotalBasicFee', --基本水费 isnull(B.N_ZongJinE,0) as 'TotalSecondFee', --二级水费 isnull(C.N_ZongJinE,0) as 'TotalOverstepPlanFee',--超计划水费 0 as 'TotalLateFee', ---滞纳金 isnull(D.N_ZongJinE,0) as 'TotalSewageDisposeFee',--污水处理费 isnull(A.N_ZongJinE,0)+isnull(B.N_ZongJinE,0)+isnull(C.N_ZongJinE,0)+isnull(D.N_ZongJinE,0) as 'YearTotalAmount', --本年合计减免 @countYear+'年'+@countMonth+'月' as CountDate from ( select I_JH from #tmp31 union select I_JH from #tmp41 union select I_JH from #tmp51 union select I_JH from #tmp61 )T left join #tmpBasic A on T.I_JH=A.I_JH left join #tmpSecond B on T.I_JH=B.I_JH left join #tmpOverstepPlan C on T.I_JH=C.I_JH left join #tmpSewageDispose D on T.I_JH=D.I_JH left join #TMPJMJH1 F on T.I_JH=F.I_JH left join #ysecond G on T.I_JH=G.I_JH -- left join #tmpLate E on T.I_JH=E.I_JH ---------------------------------------------- select distinct i_jh,CountDate,WaterType into #jh from #NowMonth union select i_jh,CountDate,WaterType from #ThisYear --------------------------------------------------------------------------------------- if @exChange=0 begin select distinct f.s_feileimc as s_daleimc, --用户类别 t. CountDate, --统计年月 t.WaterType, --用水类别 isnull(a.BasicFee,0) 'BasicFee', --本月基本水费 isnull(a.SecondFee,0) 'SecondFee', --本月二级水费 isnull(a.OverstepPlanFee,0) 'OverstepPlanFee',--本月超计划水费 isnull(a.LateFee,0) 'LateFee', --本月滞纳金 isnull(a.SewageDisposeFee,0) 'SewageDisposeFee', --本月污水处理费 isnull(a.TotalAmount,0) 'TotalAmount', --本月合计 isnull(a.BasicFee1,0) 'BasicFee1', --历史基本水费 isnull(a.SecondFee1,0) 'SecondFee1', --历史二级水费 isnull(a.OverstepPlanFee1,0) 'OverstepPlanFee1',--历史超计划水费 isnull(a.LateFee1,0) 'LateFee1', --历史滞纳金 isnull(a.SewageDisposeFee1,0) 'SewageDisposeFee1', --历史污水处理费 isnull(a.TotalAmount1,0) 'TotalAmount1', --历史合计 isnull(a.TotalCount,0) as TotalCount, --减免合计 b.TotalBasicFee, --基本水费 b.TotalSecondFee, --二级水费 b.TotalOverstepPlanFee, --超计划水费 b.TotalLateFee, --滞纳金 b.TotalSewageDisposeFee, --污水处理费 ------------ c.yBasicCost, c.ySecondLevelCost, c.yOverPlanCost, c.yLateFeeCost, isnull(c.ySlopsCost,0) 'ySlopsCost', --0 as SlopsCost, --c.AllCost , ISNULL(c.yBasicCost,0)+ISNULL(c.ySecondLevelCost,0)+ISNULL(c.yLateFeeCost,0)+ISNULL(c.yLateFeeCost,0)+ISNULL(c.ySlopsCost,0) AS AllCost, ---------------------- b.YearTotalAmount --合计 from #jh as t left join #NowMonth as a on t.i_jh=a.i_jh left join #ThisYear as b on t.I_JH=b.I_JH left join #tmpsec as c on t.I_JH=c.I_JH left JOIN (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) JG_YongShuiFL ON t.I_JH= JG_YongShuiFL.I_JH left join (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) f on JG_YongShuiFL.I_ANCESTOR=f.ID end else begin select distinct f.s_feileimc as s_daleimc , b.WaterType, a.*, b.CountDate, b.TotalBasicFee, b.TotalSecondFee, b.TotalOverstepPlanFee, b.TotalLateFee, b.TotalSewageDisposeFee, ---------------- c.yBasicCost as 'yBasicCost', c.ySecondLevelCost as'ySecondLevelCost', c.yOverPlanCost as'yOverPlanCost', c.yLateFeeCost as 'yLateFeeCost', c.ySlopsCost as 'ySlopsCost', --0 as SlopsCost, --c.AllCost, ISNULL(c.yBasicCost,0)+ISNULL(c.ySecondLevelCost,0)+ISNULL(c.yLateFeeCost,0)+ISNULL(c.yLateFeeCost,0)+ISNULL(c.ySlopsCost,0) AS AllCost, b.YearTotalAmount from #NowMonth as a right join #ThisYear as b on a.I_JH=b.I_JH right join #tmpsec as c on b.I_JH=c.I_JH LEFT JOIN (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia ))JG_YongShuiFL ON b.I_JH=JG_YongShuiFL.I_JH left join (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) f on JG_YongShuiFL.I_ANCESTOR=f.ID end drop table #NowMonth drop table #ThisYear drop table #tmpJianMian
