CREATE PROCEDURE fz_bank
@swcode varchar(6)
AS
declare @date1 varchar(20)
declare @date2 varchar(20)
declare @date3 varchar(20)
set @date1 = left(@swcode,4)+'-01-01'; /**2010-01-01**/
set @date2 = left(@swcode,4)+'-'+right(@swcode,2)+'-01'; /**2010-06-01**/
set @date3 = left(@swcode,4)+'-'+convert(varchar,(cast(right(@swcode,2) as int)+1))+'-01';
create table #bank
(
domain varchar(20),
gbcode0 varchar(20), /**付款人常驻国家(地区)代码**/
gbcode_bankname0 varchar(30),/** 付款国别名称 **/
sk_date0 varchar(30),/**收款日期**/
bicode0 varchar(30),/**币种**/
jsfs0 varchar(30),/**结算方式**/
sk_je_month numeric(15,2),/** 付款总金额本月**/
sk_je_year numeric(15,2),/** 付款总金额累计**/
bg_je_month numeric(15,2),/** 已报关付款金额本月**/
bg_je_year numeric(15,2),/** 已报关付款金额累计**/
ys_je_month numeric(15,2),/** 预付款金额本月**/
ys_je_year numeric(15,2),/** 预付款金额年份**/
tp_je_month numeric(15,2),/** 退赔款金额本月**/
tp_je_year numeric(15,2),/** 退赔款金额年份**/
my_je_month numeric(15,2),/** 退贸易从属费用金额本月**/
my_je_year numeric(15,2),/** 贸易从属费用金额年份**/
note0 varchar(200)
)
insert into #bank( gbcode0,gbcode_bankname0,domain,jsfs0)
select distinct gbcode,name,'合计' as domain,jsfs from gbcode_bank g,history_zcxx h where g.asicode=h.gbcode
/** 付款总金额本月**/
update #bank
set sk_je_month = a.fk_jeSum
from #bank
inner join
(select distinct gbcode as gbcode, sum(Fk_je ) as fk_jeSum
from history_zcxx where fk_date>=@date2 and fk_date<@date3 and bicode ='156' group by gbcode) a
on #bank.gbcode0 = a.gbcode
/** 付款总金额year**/
update #bank
set sk_je_year = a.fk_jeSum
from #bank
inner join
(select distinct gbcode as gbcode, sum(Fk_je ) as fk_jeSum
from history_zcxx where fk_date>=@date1 and fk_date<@date3 and bicode ='156' group by gbcode) a
on #bank.gbcode0 = a.gbcode
/** 已报关付款金额本月**/
update #bank
set bg_je_month = a.fk_jeSum
from #bank
inner join
(select distinct gbcode as gbcode, sum(bg_je) as fk_jeSum
from history_zcxx where fk_date>=@date2 and fk_date<@date3 and bicode ='156' group by gbcode) a
on #bank.gbcode0 = a.gbcode
/** 已报关付款金额本年**/
update #bank
set bg_je_year = a.fk_jeSum
from #bank
inner join
(select distinct gbcode as gbcode, sum(bg_je) as fk_jeSum
from history_zcxx where fk_date>=@date1 and fk_date<@date3 and bicode ='156' group by gbcode) a
on #bank.gbcode0 = a.gbcode
/** 预付款金额本月**/
update #bank
set ys_je_month = a.fk_jeSum
from #bank
inner join
(select distinct gbcode as gbcode, sum(Yfk_je) as fk_jeSum
from history_zcxx where fk_date>=@date2 and fk_date<@date3 and bicode ='156' group by gbcode) a
on #bank.gbcode0 = a.gbcode
/** 预付款金额本年**/
update #bank
set ys_je_year = a.fk_jeSum
from #bank
inner join
(select distinct gbcode as gbcode, sum(Yfk_je) as fk_jeSum
from history_zcxx where fk_date>=@date1 and fk_date<@date3 and bicode ='156' group by gbcode) a
on #bank.gbcode0 = a.gbcode
/** 退赔款金额本月**/
update #bank
set tp_je_month = a.fk_jeSum
from #bank
inner join
(select distinct gbcode as gbcode, sum(tp_je) as fk_jeSum
from history_zcxx where fk_date>=@date2 and fk_date<@date3 and bicode ='156' group by gbcode) a
on #bank.gbcode0 = a.gbcode
/** 退赔款金额本年**/
update #bank
set tp_je_year = a.fk_jeSum
from #bank
inner join
(select distinct gbcode as gbcode, sum(tp_je) as fk_jeSum
from history_zcxx where fk_date>=@date1 and fk_date<@date3 and bicode ='156' group by gbcode) a
on #bank.gbcode0 = a.gbcode
/** 退贸易从属费用金额本月**/
update #bank
set my_je_month = a.fk_jeSum
from #bank
inner join
(select distinct gbcode as gbcode, sum(my_je) as fk_jeSum
from history_zcxx where fk_date>=@date2 and fk_date<@date3 and bicode ='156' group by gbcode) a
on #bank.gbcode0 = a.gbcode
/** 退贸易从属费用金额本年**/
update #bank
set my_je_year = a.fk_jeSum
from #bank
inner join
(select distinct gbcode as gbcode, sum(my_je) as fk_jeSum
from history_zcxx where fk_date>=@date1 and fk_date<@date3 and bicode ='156' group by gbcode) a
on #bank.gbcode0 = a.gbcode
update #bank
set note0 = a.fk_jeSum
from #bank
inner join
(select distinct gbcode as gbcode, note as fk_jeSum
from history_zcxx group by gbcode,note) a
on #bank.gbcode0 = a.gbcode
select distinct gbcode0,gbcode_bankname0, sk_je_month, sk_je_year,bg_je_month,bg_je_year,ys_je_month,ys_je_year,tp_je_month,tp_je_year,my_je_month,my_je_year,note0,jsfs0 from #bank;
GO