期初、期初余额查询

Question

我正在尝试根据客户和他们每日明智的cr/dr交易来计算期初和期末余额。

closing= opening+db -cr和

期初应为上一个日期的结账日期，如果该客户没有以前的记录，则应为0。

create table pj_test_cr_dr(
    cst_name varchar2(100),
    txn_dt number(8),
    cr_amt number(22,7),
    dr_amt number(22,7)
    );

insert into pj_test_cr_dr 
    values ('c1', to_char(sysdate-5,'yyyymmdd'),200,0);
insert into pj_test_cr_dr 
    values ('c1', to_char(sysdate-5,'yyyymmdd'),300,0);
insert into pj_test_cr_dr 
    values ('c1', to_char(sysdate-5,'yyyymmdd'),0,2000);
insert into pj_test_cr_dr 
    values ('c1', to_char(sysdate-4,'yyyymmdd'),100,0);
insert into pj_test_cr_dr 
    values ('c1', to_char(sysdate-4,'yyyymmdd'),400,0);
insert into pj_test_cr_dr 
    values ('c1', to_char(sysdate-4,'yyyymmdd'),0,2000);
insert into pj_test_cr_dr 
    values ('c1', to_char(sysdate-3,'yyyymmdd'),2000,2000);
insert into pj_test_cr_dr 
    values ('c1', to_char(sysdate-3,'yyyymmdd'),4000,2000);
insert into pj_test_cr_dr 
    values ('c1', to_char(sysdate-2,'yyyymmdd'),5000,0);
commit;

select  cst_name, txn_dt, sum(dr_amt) dr,sum(cr_amt) cr
    from pj_test_cr_dr 
    group by cst_name, txn_dt 
    order by 1,2;

查询输出

​

​

预期结果：

​

​

问候

Answer 1

试试这个：

select
    cst_name,
    debit,
    credit,
    lag(closing,1,0) over (partition by cst_name order by txn_dt) opening,
    closing
from (
    select 
        cst_name,
        txn_dt,
        sum(dr_amt) debit,
        sum(cr_amt) credit,
        sum(sum(dr_amt - cr_amt)) over (partition by cst_name order by txn_dt) closing
    from pj_test_cr_dr
    where txn_dt > 20170130
    group by cst_name, txn_dt
) t;

还要注意partition by cst_name。我认为你希望将不同客户的价值分开。

Answer 2

这里有一种方法可以做到这一点-全部在一个查询中(无子查询)，并且只使用分析函数(不与聚合混合-而不是选择distinct，因为每天可能有多个事务)。

select distinct cst_name, txn_dt,
         coalesce( sum(dr_amt - cr_amt) over (partition by cst_name order by txn_dt 
             range between unbounded preceding and 1 preceding), 0 )       as op_bal,
         sum(dr_amt) over (partition by cst_name, txn_dt)                  as daily_dr,
         sum(cr_amt) over (partition by cst_name, txn_dt)                  as daily_cr,
         sum(dr_amt - cr_amt) over (partition by cst_name order by txn_dt) as cl_bal
from     pj_test_cr_dr
order by cst_name, txn_dt   --   if needed
;

CST_NAME     TXN_DT     OP_BAL   DAILY_DR   DAILY_CR     CL_BAL
-------- ---------- ---------- ---------- ---------- ----------
c1         20170131          0       2000        500       1500
c1         20170201       1500       2000        500       3000
c1         20170202       3000       4000       6000       1000
c1         20170203       1000          0       5000      -4000