select 'A1' Note , ode,sum(case cbegind_c when '借' then mb else 0 end ) J,sum(case cbegind_c when '贷' then mb else 0 end ) D into #tbl from ufdata_001_..gl_accsum g join ufdata_001_..code c on ode=ode where iperiod=1 and c.bend=1 and (c.bsup=0 and c.bcus =0 and c.bperson=0 and c.bdept=0) group by ode
insert #tbl select 'A2',ode, sum(case cbegind_c when '借' then mb else 0 end ) J,sum(case cbegind_c when '贷' then mb else 0 end ) D from ufdata_001_..gl_accass g join ufdata_001_..code c on ode=ode where iperiod=1 and c.bend=1 group by ode
insert #tbl select 'B',ode ,sum(md) ,sum(mc) from ufdata_001_..code c join ufdata_001_..gl_accvouch g on ode=ode where iflag is null and iperiod between 1 and 2 group by ode
insert #tbl select 'C',ode ,sum(md) fsJ, sum(mc) fsD from ufdata_001_..code c join ufdata_001_..gl_accvouch g on ode=ode where iflag is null and iperiod =3 group by ode
insert #tbl select note ,left(ccode ,8) ccode , sum(J) j ,sum(D ) d from #tbl where len(ccode)=10 group by note ,left(ccode ,8)
insert #tbl select note ,left(ccode ,6) ccode , sum(J) j ,sum(D ) d from #tbl where len(ccode)=8 group by note ,left(ccode ,6)
insert #tbl select note ,left(ccode ,4) ccode , sum(J) j ,sum(D ) d from #tbl where len(ccode)=6 group by note ,left(ccode ,4)
select COALESCE( ode,ode,ode) ccode ,qcJ,qcD,fsJ,FsD,qmJ,qmD into #tbl3 from (select ccode , (case when sum(j)-sum(d) >0 then sum(j)-sum(d) else 0 end) as qcJ,(case when sum(j)-sum(d) <0 then sum(d)-sum(j) else 0 end) as qcD from #tbl where note<>'c' group by ccode) as t1
full join (select ccode , sum(j) fsJ,sum(d) fsD from #tbl where note='c' group by ccode ) as t2 on ode=ode
full join (select ccode , (case when sum(j)-sum(d) >0 then sum(j)-sum(d) else 0 end) as qmJ,(case when sum(j)-sum(d) <0 then sum(d)-sum(j) else 0 end) as qmD from #tbl group by ccode ) as t3 on ode=ode
select * from #tbl3 union all select '9合计' ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from #tbl3 where ccode like '____' union all select '1资产小计'ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from #tbl3 where ccode like '1___' union all select '2负债小计'ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from #tbl3 where ccode like '2___' union all select '3权益小计'ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from #tbl3 where ccode like '3___' union all select '5损益小计'ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from #tbl3 where ccode like '5___' order by 1
