sql合计-累计

declare @maxMonth INT;
set @maxMonth=(select max(iperiod) from GL_VoucherSum where 1=1);
select gl_vs.ccode,gl_vs.iyear,gl_vs.iperiod ,
(case when gl_vs.iperiod<@maxMonth then '本月合计'
when gl_vs.iperiod=@maxMonth then '当前合计'
ELSE '' END)as cdigest ,
sum(gl_vs.md)as sumMd,sum(gl_vs.mc) as sumMc, --金额合计
sum(gl_vs.nd_s)as sumNds,sum(gl_vs.nc_s) as sumNcs,--数量合计
sum(gl_vs.md_f)as sumMdf,sum(gl_vs.mc_f) as sumMcf,--外币合计
(case when cbegind_c=1 then '借' else '贷' end) as cbegind_c,
sum(mb+gl_vs.md-gl_vs.mc) as mbs,sum(gl_vs.nb_s+gl_vs.nd_s-gl_vs.nc_s) as nbs,
sum(gl_vs.mb_f+gl_vs.md_f+gl_vs.mc_f) as mbf
from GL_VoucherSum gl_vs
inner join GL_VoucherDetail gl_v on gl_v.ccode=gl_vs.ccode
where gl_v.ccode=100110 and ibook=1
group by gl_vs.orgcode,gl_vs.ccode,gl_vs.iyear,gl_vs.iperiod,gl_vs.cbegind_c
union all

select gl_vs.ccode,gl_vs.iyear,gl_vs.iperiod ,
(case when gl_vs.iperiod<@maxMonth then '累计'
when gl_vs.iperiod=@maxMonth then '当前累计'
when gl_vs.iperiod=12 then '本年累计'
ELSE '' END)as cdigest ,
(select sum(md) from GL_VoucherSum g where g.iperiod<=gl_vs.iperiod) as sumMd,
(select sum(mc) from GL_VoucherSum g where g.iperiod<=gl_vs.iperiod) as sumMc,--金额累计
(select sum(nd_s) from GL_VoucherSum g where g.iperiod<=gl_vs.iperiod) as sumNds,
(select sum(nc_s) from GL_VoucherSum g where g.iperiod<=gl_vs.iperiod) as sumNcs,--数量累计
(select sum(md_f) from GL_VoucherSum g where g.iperiod<=gl_vs.iperiod) as sumMdf,
(select sum(mc_f) from GL_VoucherSum g where g.iperiod<=gl_vs.iperiod) as sumMcf,--外币累计
'' as cbegind_c ,'' as mbs,'' as nbs,'' as mbf
from GL_VoucherSum gl_vs
inner join GL_VoucherDetail gl_v on gl_v.ccode=gl_vs.ccode
where gl_v.ccode=100110 and ibook=1 group by gl_vs.orgcode,gl_vs.ccode, gl_vs.iyear,gl_vs.iperiod
union all


select gl_vs.ccode,gl_vs.iyear ,'' as iperiod ,'期初余额' as cdigest ,
'' as sumMd,'' as sumMc,'' as sumNds,'' as sumNcs, '' as sumMdf,'' as sumMcf, '' as cbegind_c ,
(select mb from GL_VoucherSum where iperiod=@maxMonth) as mbs,
(select nb_s from GL_VoucherSum where iperiod=@maxMonth) as nbs,
(select mb_f from GL_VoucherSum where iperiod=@maxMonth) as mbf
from GL_VoucherSum gl_vs
inner join GL_VoucherDetail gl_v on gl_v.ccode=gl_vs.ccode
where gl_v.ccode=100110 and ibook=1 group by gl_vs.orgcode,gl_vs.ccode,gl_vs.iyear
order by iperiod,cdigest