存储过程 报表

CREATE PROCEDURE fz_bank4 @flag varchar(6), --日期参数 @str varchar(20) --p:省 np:市 AS declare @date_front varchar(20) declare @date_middle varchar(20) declare @date_back varchar(20) set @date_front = left(@flag,4)+'-01-01'; /**2010-01-01**/ set @date_middle = left(@flag,4)+'-'+right(@flag,2)+'-01'; /**2010-06-01**/ set @date_back = left(@flag,4)+'-'+convert(varchar,(cast(right(@flag,2) as int)+1))+'-01'; /**2010-07-01 **/ --------------------------------存储过程虚拟表 create table #bank ( id varchar(20), domain varchar(20), cpcode varchar(20), item_no varchar(30), sk_date varchar(30),/**收款日期**/ je_month numeric(15,2),/** 存放境外金额本月**/ je_year numeric(15,2),/** 存放境外金额金额本年**/ ysk_je_month numeric(15,2),/** 存放境外收款金额本月**/ ysk_je_year numeric(15,2),/** 存放境外收款金额本年**/ bl_je_month numeric(15,2),/** 收款比例本月**/ bl_je_year numeric(15,2),/** 收款比例年份**/ note varchar(200), gbcode varchar(30) ) create table #swcode_table ( code varchar(30), --id name varchar(30) ) create table #history_sbba_table ( cpcode varchar(20), sk_date datetime, --收款日期 ysk_date datetime,--预收款金额 je numeric(15,2), --金额 ysk_je numeric(15,2), --预收款金额 item_no varchar(30), note varchar(50), domain varchar(80), gbcode varchar(50) ) ------判断省表和非省表 if @str='p' begin insert #swcode_table(code,name) select code,name from swcode where code like '%00' end else begin insert #swcode_table(code,name) select code,name from swcode where code not like '%00' end --------------------------------------------------------------插入表 -----插入 #history_sbba_table数据 insert into #history_sbba_table( cpcode,sk_date,ysk_date,je,ysk_je,item_no,note,gbcode) select cpcode,sk_date,ysk_date,je,ysk_je,item_no,note,gbcode from history_sbba h,#swcode_table s where s.code=h.cpcode ---临时表 存储过程 insert into #bank( item_no,cpcode,note,domain,gbcode) select distinct item_no,cpcode,h.note,s.name as domain,g.name as gbcode from #history_sbba_table h,swcode s,gbcode_bank g where s.code=h.cpcode and g.code=h.gbcode --------------------------------------------------------月份 -------------更新#bank里面的字段 update #bank set je_month = jeSum --金额月份 from #bank inner join (select cpcode as cpcode, sum(je) as jeSum from #history_sbba_table where sk_date>=@date_middle and sk_date<@date_back group by cpcode) a on #bank.cpcode = a.cpcode update #bank set ysk_je_month = yskSum --预收款金额 from #bank inner join (select distinct cpcode as cpcode, ysk_je as yskSum from #history_sbba_table where ysk_date>=@date_middle and ysk_date<@date_back ) a on #bank.cpcode = a.cpcode update #bank set bl_je_month = ysk_je_month/je_month --比例 ----------------------------------------------------------------------年份 -------------更新#bank里面的字段 update #bank set je_year = jeSum --金额月份 from #bank inner join (select cpcode as cpcode, sum(je) as jeSum from #history_sbba_table where sk_date>=@date_front and sk_date<@date_back group by cpcode) a on #bank.cpcode = a.cpcode update #bank set ysk_je_year = yskSum --预收款金额 from #bank inner join (select distinct cpcode as cpcode, ysk_je as yskSum from #history_sbba_table where ysk_date>=@date_front and ysk_date<@date_back ) a on #bank.cpcode = a.cpcode update #bank set bl_je_year = ysk_je_year/je_year --比例 ---------------------------------------------------------------------------总计 insert into #bank(id,je_month,je_year,ysk_je_month,ysk_je_year,bl_je_month,bl_je_year,cpcode,domain,note,gbcode) select '1',sum(je_month),sum(je_year),sum(ysk_je_month),sum(ysk_je_year),sum(bl_je_month),sum(bl_je_year),'' ,'总计','','' from #bank select domain,gbcode,isnull(je_month,0) as je_month,isnull(je_year,0) as je_year, isnull(ysk_je_month,0) as ysk_je_month, isnull(ysk_je_year,0) as ysk_je_year ,isnull(bl_je_month,0) as bl_je_month ,isnull(bl_je_year,0) as bl_je_year ,note from #bank order by id desc GO