[补充工程统计case]科技活动经费sql2014

select bd_glorgbook.glorgbookcode,bd_glorgbook.glorgbookname,
       gl_detail.explanation,
       bd_accsubj.dispname,
       gl_detail.debitamount 借方,
       gl_detail.creditamount 贷方,
       gl_detail.prepareddatev 制单日期,
       gl_voucher.no 凭证号,
       wmsys.wm_concat(gl_freevalue.valuecode) valuecode,
       wmsys.wm_concat(gl_freevalue.valuename) valuename,
       gl_detail.pk_systemv,
       gl_detail.detailindex
  from bd_accsubj
  join gl_detail
    on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
  join bd_glorgbook
    on bd_glorgbook.pk_glorgbook = bd_accsubj.pk_glorgbook
  join gl_voucher
    on gl_detail.pk_voucher = gl_voucher.pk_voucher
  left join gl_freevalue
    on gl_detail.assid = gl_freevalue.freevalueid
 where gl_detail.dr = '0'
 and ( bd_accsubj.subjcode like  '4104010508%'or bd_accsubj.subjcode like  '550212%')
    
   and bd_glorgbook.glorgbookcode like '01%-0001'
   and gl_detail.yearv = '2013'
 /* and gl_detail.creditamount ='0'*/
   and gl_detail.explanation<>'期初'
   and gl_detail.debitamount<>0
  /* and gl_detail.explanation='付财大凤凰楼项目人工费(洪荣森)'*/
--and bd_accsubj.subjcode like '150103%'
 group by bd_glorgbook.glorgbookcode,bd_glorgbook.glorgbookname, gl_detail.explanation,
          bd_accsubj.dispname,
          gl_detail.creditamount,
          gl_detail.debitamount,
          gl_detail.prepareddatev,
          gl_voucher.no,
          gl_detail.pk_systemv,
          gl_detail.detailindex
 order by  bd_glorgbook.glorgbookcode, bd_accsubj.dispname,gl_detail.prepareddatev, gl_voucher.no

 发给建行的

select-- bd_glorgbook.glorgbookcode,
--bd_glorgbook.glorgbookname,
--gl_detail.prepareddatev 制单日期,
    -- gl_voucher.no 凭证号,
    -- gl_detail.explanation,
     gl_detail.yearv,
       bd_accsubj.dispname,
       sum(gl_detail.debitamount)   金额
      -- gl_detail.creditamount  贷方,
       from gl_detail, bd_accsubj, bd_glorgbook, gl_voucher
 where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook      
   and gl_detail.pk_voucher = gl_voucher.pk_voucher
   and gl_detail.dr = '0'
    and gl_detail.periodv<>'00'
    and gl_detail.debitamount<>0
   and gl_detail.yearv in ('2011','2012','2013')
   --and bd_glorgbook.glorgbookcode = '010201-0001'
   --and (gl_voucher.no='263' or gl_voucher.no='207')
   and (bd_accsubj.subjcode like '550212%' or bd_accsubj.subjcode like '4104010508%')
   and bd_glorgbook.glorgbookcode like '01%' 
   group by   gl_detail.yearv,bd_accsubj.dispname
 order by  gl_detail.yearv,bd_accsubj.dispname

下面是rollup安装分公司小计

select  bd_glorgbook.glorgbookcode,
           nvl(replace(bd_glorgbook.glorgbookname,'集团基准账薄',''),'小计')公司名称, --从萝卜那里学习
           
           bd_accsubj.subjcode,bd_accsubj.subjname,
           sum( case when gl_balance.year= '2010' then  gl_balance.debitamount else 0 end) "2010发生额",
           sum( case when gl_balance.year= '2011' then  gl_balance.debitamount else 0 end) "2011发生额",
           sum( case when gl_balance.year= '2012' then  gl_balance.debitamount else 0 end) "2012发生额"            
          
      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 '5502%' or bd_accsubj.subjcode like '5503%')
       and gl_balance.period<>'00'
       and gl_balance.year in('2010','2011','2012')
      group by bd_glorgbook.glorgbookcode,  rollup((bd_accsubj.subjcode,bd_accsubj.subjname , bd_glorgbook.glorgbookname))   
   
    order by bd_glorgbook.glorgbookcode,  bd_accsubj.subjcode

2014.9.10 更新

统计各个公司2010-2014费用

期间发生了问题，sum的时候里面不能是数字和汉字的组合，否则

 

单位取到万

select glorgbookcode,公司名称,
( case substr(subjcode,1,4)
 when '5502' then '管理费用'
  when '5503' then '财务费用'
    when '5201' then '投资收益'
      when '5301' then '营业外收入'
        else    '营业外支出' end 
)"会计科目",
round(sum(A)/10000,2)"2010发生额",round(sum(B)/10000,2) "2011发生额",round(sum(C)/10000,2)"2012发生额",round(sum(D)/10000,2) "2013发生额",round(sum(E)/10000,2) "2014发生额"
 from (

  select  bd_glorgbook.glorgbookcode,
           nvl(replace(bd_glorgbook.glorgbookname,'集团基准账薄',''),'小计')公司名称, --从萝卜那里学习
           
           bd_accsubj.subjcode,bd_accsubj.subjname,
           sum( case when gl_balance.year= '2010' then  gl_balance.debitamount else 0 end) A,
           sum( case when gl_balance.year= '2011' then  gl_balance.debitamount else 0 end)B,
           sum( case when gl_balance.year= '2012' then  gl_balance.debitamount else 0 end) C,  
           sum( case when gl_balance.year= '2013' then  gl_balance.debitamount else 0 end) D,
           sum( case when gl_balance.year= '2014' then  gl_balance.debitamount else 0 end) E              
          
      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 '5502%' --管理费用
       or bd_accsubj.subjcode like '5503%'  -- 财务费用
       or bd_accsubj.subjcode like '5201%' --投资收益
       or bd_accsubj.subjcode like '5301%' --营业外收入
       or bd_accsubj.subjcode like '5601%' --营业外支出
       )
       and gl_balance.period<>'00'
       and gl_balance.year in('2010','2011','2012','2013','2014')
      group by bd_glorgbook.glorgbookcode,  bd_accsubj.subjcode,bd_accsubj.subjname , bd_glorgbook.glorgbookname   
   
    order by bd_glorgbook.glorgbookcode,  bd_accsubj.subjcode)
    group by glorgbookcode,公司名称,substr(subjcode,1,4)
    order by glorgbookcode,substr(subjcode,1,4)