pjha pjha - 4 years ago 159
SQL Question

opening and closing balance query

I'm trying to figure out the opening/closing balance based on the customer and their daily wise cr/dr transaction, below query is working till 2 days records but for 3 and more records opening is not coming correct and so the closing. Appreciate if any help provided.

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'),220,0);

insert into pj_test_cr_dr values ('c1', to_char(sysdate-5,'yyyymmdd'),0,300);

insert into pj_test_cr_dr values ('c1', to_char(sysdate-5,'yyyymmdd'),0,500);

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);


Select cst_name,txn_dt, LAG(closing, 1 ,0) OVER (order by cst_name,txn_dt) as opening,
debit, credit,
(LAG(closing,1,0) OVER (order by cst_name,txn_dt )+ closing) as closing
from (select cst_name,txn_dt, 0 as OPEN, SUM(dr_amt) as debit, SUM(cr_amt) as credit,
sum(dr_amt) - sum(cr_amt) as closing
FROM pj_test_cr_dr
where txn_dt >20170130
group by cst_name,txn_dt
) a;

query output incorrect record


Answer Source

You are using lag on old closing column. You want to calculate it first in the subquery and then use lag on it:

  LAG(closing, 1 ,0) OVER (order by cst_name,txn_dt) AS opening
  (SELECT cst_name,
    (LAG(closing,1,0) OVER (order by cst_name,txn_dt )+ closing) AS closing
    (SELECT cst_name,
      SUM(dr_amt) debit,
      SUM(cr_amt) credit,
      SUM(dr_amt) - SUM(cr_amt) closing
    FROM pj_test_cr_dr
    WHERE txn_dt > 20170130
    GROUP BY cst_name,
    ) a
  ) t;

With that said, you seem to have some logic problem here. Please check.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download