资金归集率比率sql

 基础资料

select bd_glorgbook.glorgbookcode,
nvl(replace(bd_glorgbook.glorgbookname,'集团基准账薄',''),'小计')公司名称, --从萝卜那里学习

substr(bd_accsubj.subjcode,1,4)科目编码,---1111应收票据（借方），1131应收账款（+贷-借），2123工程结算
--以下是贷方
sum( case when gl_balance.year= '2012' then gl_balance.debitamount else 0 end) "2012借方发生额",
sum( case when gl_balance.year= '2013' then gl_balance.debitamount else 0 end) "2013借方发生额",
sum( case when gl_balance.year= '2014' then gl_balance.debitamount else 0 end) "2014借方发生额" , 
sum( case when gl_balance.year= '2015' then gl_balance.debitamount else 0 end) "2015借方发生额" ,
--以下是贷方
sum( case when gl_balance.year= '2012' then gl_balance.creditamount else 0 end) "2012贷方发生额",
sum( case when gl_balance.year= '2013' then gl_balance.creditamount else 0 end) "2013贷方发生额",
sum( case when gl_balance.year= '2014' then gl_balance.creditamount else 0 end) "2014贷方发生额" , 
sum( case when gl_balance.year= '2015' then gl_balance.creditamount else 0 end) "2015贷方发生额" 
from gl_balance, bd_accsubj, bd_glorgbook
where gl_balance.pk_accsubj = bd_accsubj.pk_accsubj
and bd_glorgbook.pk_glorgbook = gl_balance.pk_glorgbook
and bd_glorgbook.glorgbookcode like '01%-0001' 
and (bd_accsubj.subjcode like '2123%' or bd_accsubj.subjcode like '1131%'or bd_accsubj.subjcode like '1111%')
and gl_balance.period<>'00'
and gl_balance.year in('2012','2013','2014','2015')
group by bd_glorgbook.glorgbookcode, rollup((substr(bd_accsubj.subjcode,1,4), bd_glorgbook.glorgbookname)) 

order by bd_glorgbook.glorgbookcode, substr(bd_accsubj.subjcode,1,4)

通过substr取一级

2015-10-13 08:50:00 计算工程款

select bd_glorgbook.glorgbookcode,
nvl(replace(bd_glorgbook.glorgbookname,'集团基准账薄',''),'小计')公司名称, --从萝卜那里学习

--substr(bd_accsubj.subjcode,1,4)科目编码,---1111应收票据（借方），1131应收账款（+贷-借），2123工程结算

--以下是贷方
sum( case when gl_balance.year= '2012' then gl_balance.creditamount else 0 end) "2012贷方发生额",
sum( case when gl_balance.year= '2013' then gl_balance.creditamount else 0 end) "2013贷方发生额",
sum( case when gl_balance.year= '2014' then gl_balance.creditamount else 0 end) "2014贷方发生额" , 
sum( case when gl_balance.year= '2015' and gl_balance.period<=6 then gl_balance.creditamount else 0 end) "2015贷方发生额" 
from gl_balance, bd_accsubj, bd_glorgbook
where gl_balance.pk_accsubj = bd_accsubj.pk_accsubj
and bd_glorgbook.pk_glorgbook = gl_balance.pk_glorgbook
and bd_glorgbook.glorgbookcode like '01%-0001' 
and (bd_accsubj.subjcode like '2123%' )
and gl_balance.period<>'00'
and gl_balance.year in('2012','2013','2014','2015')
group by bd_glorgbook.glorgbookcode, substr(bd_accsubj.subjcode,1,4), bd_glorgbook.glorgbookname
order by bd_glorgbook.glorgbookcode, substr(bd_accsubj.subjcode,1,4)

2015年6月的工程款中gl_balance需要是<=不能是=，6也不需要引号（=6为当月的余额），必须记账才能取到

归集sql

select bd_accid.accidcode,
       bd_accid.accidname,
      sum( case when substr(fts_voucher_b.interestdate, 1, 4)= '2012' then fts_voucher_b.creditamount else 0 end) "2012归集额",
sum( case when substr(fts_voucher_b.interestdate, 1, 4)= '2013' then fts_voucher_b.creditamount else 0 end) "2013归集额",
sum( case when substr(fts_voucher_b.interestdate, 1, 4)= '2014' then fts_voucher_b.creditamount else 0 end) "2014归集额" , 
sum( case when substr(fts_voucher_b.interestdate, 1, 4)= '2015' then fts_voucher_b.creditamount else 0 end) "2015归集额"  
  from fts_voucher_b, bd_accid
 where fts_voucher_b.dr = '0'
   and fts_voucher_b.pk_corp = '1162'
   and fts_voucher_b.pk_account = bd_accid.pk_accid
  -- and bd_accid.accidcode = '011501' --公司
   and fts_voucher_b.creditamount <> 0
   and substr(fts_voucher_b.interestdate, 1, 4) in('2012','2013','2014','2015') --年份
   and fts_voucher_b.pk_ass not in
       (select freevalueid from gl_freevalue where valuecode = '777777') 
       group by bd_accid.accidcode, bd_accid.accidname 
 order by bd_accid.accidcode 

 委托中心付款合计sql

select c.accidcode,c.accidname,

sum( case when a.cyear= '2012' then a.totalcredit else 0 end) "2012借方发生额",
sum( case when a.cyear= '2013' then a.totalcredit else 0 end) "2013借方发生额",
sum( case when a.cyear= '2014' then a.totalcredit else 0 end) "2014借方发生额" , 
sum( case when a.cyear= '2015' then a.totalcredit else 0 end) "2015借方发生额"  

from fts_voucher a,fts_voucher_b b, bd_accid c
 where a.pk_voucher=b.pk_voucher
 and b.pk_account=c.pk_accid
  and   a.cyear in('2012','2013','2014','2015')
  and a.datasource=2 --(6上收，2委托，5下拨，3到账通知，0手工填制)
group by c.accidcode,c.accidname 
order by c.accidcode
 

 待研究

select c.accidcode,c.accidname,

sum( case when a.cyear= '2012' then a.totalcredit else 0 end) "2012借方发生额",
sum( case when a.cyear= '2013' then a.totalcredit else 0 end) "2013借方发生额",
sum( case when a.cyear= '2014' then a.totalcredit else 0 end) "2014借方发生额" ,
sum( case when a.cyear= '2015' then a.totalcredit else 0 end) "2015借方发生额"

from fts_voucher a,fts_voucher_b b, bd_accid c
where a.pk_voucher=b.pk_voucher
and b.pk_account=c.pk_accid
and a.cyear in('2012','2013','2014','2015')
and a.datasource=2 --(6上收，2委托，5下拨，3到账通知，0手工填制)
and fts_voucher_b.pk_ass not in
(select freevalueid from gl_freevalue where valuecode = '777777')
group by c.accidcode,c.accidname
order by c.accidcode

2015-10-12 09:31:13 整合 去掉内部转账的委托付款

select c.accidcode,c.accidname,

sum( case when a.cyear= '2012' then a.totalcredit else 0 end) "2012借方发生额",
sum( case when a.cyear= '2013' then a.totalcredit else 0 end) "2013借方发生额",
sum( case when a.cyear= '2014' then a.totalcredit else 0 end) "2014借方发生额" , 
sum( case when a.cyear= '2015' then a.totalcredit else 0 end) "2015借方发生额"  

from fts_voucher a,fts_voucher_b b, bd_accid c
 where a.pk_voucher=b.pk_voucher
 and b.pk_account=c.pk_accid
  and   a.cyear in('2012','2013','2014','2015')
  and a.datasource=2 --(6上收，2委托，5下拨，3到账通知，0手工填制)
and b.pk_ass not in
(select freevalueid from gl_freevalue where valuecode = '777777') 
group by c.accidcode,c.accidname 
order by c.accidcode