完善每日资金下拨的sql

条件max date取值于当天审核后的日期，这样周六的上收也可以取到

更新：fts_voucher_b.pk_account != '1162N51000000000032B'

(select fts_voucher_b.interestdate,
       bd_accid.accidcode,
       bd_accid.accidname,
       fts_voucher_b.creditamount as 上收金额,
       (case
         when (round(0.35 * fts_voucher_b.localcreditamount / 10000, 0) *
              10000) < 100000 then
          '0'
         else
          to_char(round(0.35 * fts_voucher_b.localcreditamount / 10000, 0)) || '万'
       end) as 下拨金额,
       
       to_number(substr(bd_custbank.account, -4, 4)) AS 下拨账号,
       bd_accbank.remcode as 助记码,
       (case
         when (round(0.35 * fts_voucher_b.localcreditamount / 10000, 0) *
              10000) < 100000 then
          fts_voucher_b.creditamount
         else
          fts_voucher_b.creditamount -
          round(0.35 * fts_voucher_b.localcreditamount / 10000, 0) * 10000
       end) as 余额
  from fts_voucher_b, bd_accid, bd_cubasdoc, bd_custbank, bd_accbank,fts_voucher
 where bd_custbank.pk_cubasdoc = bd_cubasdoc.pk_cubasdoc
   and bd_cubasdoc.custcode = bd_accid.accidcode
   and fts_voucher_b.pk_account = bd_accid.pk_accid
   and bd_custbank.pk_accbank = bd_accbank.pk_accbank
   and fts_voucher.pk_voucher=fts_voucher_b.pk_voucher
   and bd_custbank.defflag = 'Y'
   and fts_voucher_b.summary = '每日收入户自动归集'
   and fts_voucher.auditdate in
       (select max(auditdate)
          from fts_voucher
         where fts_voucher.summary = '每日收入户自动归集')    
   and fts_voucher_b.pk_account != '1162N51000000000032B'
   and bd_accid.accidcode<>'080101')
   
   union
   
  (select fts_voucher_b.interestdate,
       bd_accid.accidcode,
       bd_accid.accidname,
       fts_voucher_b.creditamount as 上收金额,
       to_char(0.85 * fts_voucher_b.localcreditamount / 10000) || '万' as 下拨金额,       
       to_number(substr(bd_custbank.account, -4, 4)) AS 下拨账号,
       bd_accbank.remcode as 助记码,
       fts_voucher_b.creditamount*0.15  as 余额
  from fts_voucher_b, bd_accid, bd_cubasdoc, bd_custbank, bd_accbank,fts_voucher
 where bd_custbank.pk_cubasdoc = bd_cubasdoc.pk_cubasdoc
   and bd_cubasdoc.custcode = bd_accid.accidcode
   and fts_voucher_b.pk_account = bd_accid.pk_accid
   and bd_custbank.pk_accbank = bd_accbank.pk_accbank
   and fts_voucher.pk_voucher=fts_voucher_b.pk_voucher
   and bd_custbank.defflag = 'Y'
   and fts_voucher_b.summary = '每日收入户自动归集'
   and fts_voucher.auditdate in
       (select max(auditdate)
          from fts_voucher
         where fts_voucher.summary = '每日收入户自动归集')  
   and fts_voucher_b.pk_account != '1162N51000000000032B'
   and bd_accid.accidcode='080101') 
   

转载于:https://www.cnblogs.com/sumsen/archive/2012/11/05/2755545.html