存储过程 报表

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