declare @begindate as datetime
declare @enddate as datetime
declare @period as int
--修改期间开始时间
set @begindate='2010-7-7'
--修改期间终止时间
set @enddate='2010-7-8'
--修改会计期间
set @period=7

--生成起点凭证
select * into #tmp_zz_begin from
(
select gav.iperiod as 会计期间,c.ccode as 科目编码,c.ccode_name as 科目名称,sum(gav.md) as 累计借方,sum(gav.mc) as 累计贷方 from gl_accvouch gav,code c where gav.iperiod=@period and c.ccode=gav.ccode and gav.doutbilldate between '2000-1-1' and @begindate
group by gav.iperiod,c.ccode,c.ccode_name
union
select @period as 会计期间,c.ccode as 科目编码,c.ccode_name as 科目名称,0 as 累计借方,0 as 累计贷方 from code c where c.ccode not in (select ccode from gl_accvouch gav where gav.iperiod=@period) and c.bend=1
) det
--select * from #tmp_zz_begin


--生成终点凭证
select * into #tmp_zz_end from
(
select gav.iperiod as 会计期间,c.ccode as 科目编码,c.ccode_name as 科目名称,sum(gav.md) as 累计借方,sum(gav.mc) as 累计贷方 from gl_accvouch gav,code c where gav.iperiod=@period and c.ccode=gav.ccode and gav.doutbilldate between '2000-1-1' and @enddate
group by gav.iperiod,c.ccode,c.ccode_name
union
select @period as 会计期间,c.ccode as 科目编码,c.ccode_name as 科目名称,0 as 累计借方,0 as 累计贷方 from code c where c.ccode not in (select ccode from gl_accvouch gav where gav.iperiod=@period) and c.bend=1
) det
--select * from #tmp_zz_end


--生成起点报表
select
@begindate as 时间
,gav.会计期间
,c.ccode as 科目编码
,c.ccode_name as 科目名称
,ga.mb as 当期期初
,sum(gav.累计借方) as 累计借方
,sum(gav.累计贷方) as 累计贷方
,ga.mb+sum(gav.累计借方)-sum(gav.累计贷方) as 当期期末
into #tmp_zy_begin
from gl_accsum ga
,code c
,#tmp_zz_begin as gav
where c.ccode=gav.科目编码 and ga.ccode=c.ccode and ga.iperiod=gav.会计期间
group by c.ccode,c.ccode_name,ga.mb,ga.me,gav.会计期间
--select * from #tmp_zy_begin

 

--生成终点报表
select
@enddate as 时间
,gav.会计期间
,c.ccode as 科目编码
,c.ccode_name as 科目名称
,ga.mb as 当期期初
,sum(gav.累计借方) as 累计借方
,sum(gav.累计贷方) as 累计贷方
,ga.mb+sum(gav.累计借方)-sum(gav.累计贷方) as 当期期末
into
#tmp_zy_end
from gl_accsum ga
,code c
,#tmp_zz_end as gav
where c.ccode=gav.科目编码 and ga.ccode=c.ccode and ga.iperiod=gav.会计期间
group by c.ccode,c.ccode_name,ga.mb,ga.me,gav.会计期间
--select * from #tmp_zy_end

--生成最终报告
select tzb.会计期间
,@begindate as 开始时间
,@enddate as 结束时间
,tzb.科目编码
,tzb.科目名称
,tzb.当期期末 as 当期期初
,tze.累计借方-tzb.累计借方 as 累计借方
,tze.累计贷方-tzb.累计贷方 as 累计贷方
,tze.当期期末
from #tmp_zy_begin tzb,#tmp_zy_end tze
where tzb.科目编码=tze.科目编码
and tzb.科目编码 in('1001','100101','100102')
--修改上面内容限定取出科目


drop table #tmp_zy_end
drop table #tmp_zz_end
drop table #tmp_zy_begin
drop table #tmp_zz_begin