(前情简介:GL模块中gl_je_lines表中每一条journal_line都会有accounted_dr与accounted_cr两个栏位,表示该条line发生的借方金额与贷方金额; 在gl_balances表中每一条记录都会存储一个account在某一历史会计期间的期初借方历史发生总额begin_balance_dr, 期初贷方历史发生总额begin_balance_cr.)
最近在tuning一张Trial Balance by Date的报表.需求是这样的:
用户输入两个日期start_date, end_date, 报表找出某个SOB下在这段时间内所有account的起始余额beginning_balance, 借方发生额dr_amount, 贷方发生额cr_amount, 总发生额(借-贷)net_change, 结末余额ending_balance.
原来报表是这样计算的(红色为计算结果栏位).
1) 首先,对每一个account,调用一个函数计算出start_date的beginning_balance.
计算方法:
找出start_date所在的period_name,根据period_name从gl_balances表中找出这个account的begin_balance_dr, begin_balance_cr.二者相减,得出该account在该period的期初余额.period_beginning_balance
然后到gl_je_lines表中,找到这个account从该期间第一天到start_date-1所发生的所有借贷额.sum(accounted_dr) => period_bydate_dr,
sum(accounted_cr) => period_bydate_cr.二者相减,得出该account在该期间内实际发生额:period_net_change.
最后, period_beginning_balance + period_net_change, 得出该天的beginning_balance.
2) 从gl_je_lines表中,sum从start_date到end_date该account发生的所有借贷额.
sum(accounted_dr) => dr_amount ,sum(accounted_cr) => cr_amount
dr_mount – cr_mount => net_change.
3) Beginning_balance + net_change = ending_balance
因为需要对gl_je_lines这一恐怖的表做sum动作,所以随着输入start_date与end_date之间时间段的加大,这张报表的恐怖系数指数上升.
在对报表的SQL折腾了数天以后,突然想到一个问题,就是在GL模块中,对于所有的历史会计期间, gl_je_lines中在该期间发生的借贷额,以及历史发生的总的借贷额,都会sum到gl_balances这张表里面.,也就是说, 我们上面所做的sum动作,其实很大部分GL早已经为我们做好了.
例如说我们要求从2006-4-5 到 2006-8-5之间某个sob的beginning_balance, dr_amount, cr_amount, net_change, ending_balance.
按照上面的说法,假若2006-4-5所在会计期间第一天是2006-4-1, 2006-8-5所在会计期间第一天是2006-8-1.
我们的begin_balance依然按照第一种方法获取.
dr_amount的取得,我们可以使用2006-8-6的beginning_dr 减去 2006-4-5的beginning_dr,
cr_amount也可以用同样的方法取得.
而两个beginning_dr的取得,我们在方法一的第一步其实已经取得,只是它没有使用:
Begin_balance_dr + period_bydate_dr = beginning_dr
Begin_balance_cr + period_bydate_cr = beginning_cr
也就是说我们利用方法一的第一步分别取得start_date与end_date的begin_dr与begin_cr.
利用这4个数值就可以拼的我们所需要的所有栏位值了.
求得end_date的两个数值作为ending_dr, ending_cr.
Beginning_balance = beginning_dr – beginning_cr
Dr_amount = ending_dr – beginning_dr
Cr_amount = ending_cr – beginning_cr
Net_change = dr_amnount – cr_amount
Ending_balance = ending_dr – ending_cr
按照这种方法,我们sum的知识2006-4-1到2006-4-5以及2006-8-1到2006-8-5这两个小区间的journal,而没有像方法一一样sum所有的.
