问题:9期对账单期初不等于9期资金对账汇总表对账单期初
分析过程:
--银行对账单9期期初取数逻辑
select
fdebit,
fcredit,
fbalance ,
forder
from
( select
sum(FDebitStartAmt) FDebit,
sum(FCreditStartAmt) FCredit,
sum(FMonthStartAmt) FBalance ,
0 as forder
from
t_cas_journalBalance
where
FType = 3
and FIsInit = 1
and FAccountBankId in (
'c6oAAAFiHLv7Mm5e', 'c6oAAAFjCrj7Mm5e'
)
and FCurrencyId ='dfd38d11-00fd-1000-e000-1ebdc0a8100dDEB58FDC'
union
all select
sum(FDebitAmount) FDebit,
sum(FCreditAmount) FCredit,
0 FBalance ,
1 as forder
from
t_cas_bankstatement
where
FCompanyId ='c6oAAAEqVwfM567U'
and FAccountBankId ='c6oAAAFiHLv7Mm5e'
and FCurrencyId = 'dfd38d11-00fd-1000-e000-1ebdc0a8100dDEB58FDC'
and FPeriodId IS NOT NULL
and FCreateDate < {2018-08-31}
) b
order by
forder
--资金对账汇总表中9期银行对账单期初取数逻辑:初始化余额+借减贷差额
select
sum(SubResult) Result
from
( select
FMonthStartAmt SubResult
from
T_CAS_JournalBalance
where
FIsInit =0
and FCurrencyId ='dfd38d11-00fd-1000-e000-1ebdc0a8100dDEB58FDC'
and FPeriodId='c6oAAABPVbeCOIxM'
and FType= 3
and FAccountBankID in (
'c6oAAAFiHLv7Mm5e'
)
union
all select
sum(FCreditAmount-FDebitAmount) SubResult
from
t_cas_Bankstatement
where
FCreateDate >={2018-05-01}
and FCreateDate <= {2018-08-31}
and FCurrencyId = 'dfd38d11-00fd-1000-e000-1ebdc0a8100dDEB58FDC'
and FCompanyId in (
'c6oAAAEqVwfM567U'
)
and FAccountBankId in (
'c6oAAAFiHLv7Mm5e'
)
) SubQuery
由于8期对账单发生额中 贷方金额存在空值 导致sum(FCreditAmount-FDebitAmount) 计算时取不到8期的发生额导致
--查询错误数据脚本
select count(*) from T_CAS_BankStatement where FCREDITAMOUNT is null
select count(*) from T_CAS_BankStatement where FDebitAmountis null
推荐阅读