Home » SQL & PL/SQL » SQL & PL/SQL » running balance query (10g, win8.1)
 running balance query [message #650083] |
Thu, 14 April 2016 12:15  |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
I have a table. Column BILL contains amount receivable and column RECEIVED contains the amount received.
create table t1
(
customerid number(4),
transaction_date DATE,
bill number(10),
received number(10)
);
insert into t1 values(1,'10-APR-2016',null,100000);
insert into t1 values(1,'10-APR-2016',null,200000);
insert into t1 values(1,'11-APR-2016',50000,null);
insert into t1 values(1,'12-APR-2016',null,100000);
insert into t1 values(1,'13-APR-2016',4000,null);
insert into t1 values(1,'13-APR-2016',null,20000);
insert into t1 values(1,'14-APR-2016',8000,null);
The data is
SQL> select * from t1;
CUSTOMERID TRANSACTI BILL RECEIVED
---------- --------- --------- ---------
1 10-APR-16 100000
1 10-APR-16 200000
1 11-APR-16 50000
1 12-APR-16 100000
1 13-APR-16 4000
1 13-APR-16 20000
1 14-APR-16 8000
7 rows selected.
My question is: how can I show a running_balance column in the query
like below
CUSTOMERID TRANSACTI BILL RECEIVED running_balance
---------- --------- --------- --------- ---------
1 10-APR-16 100000 100000
1 10-APR-16 200000 300000
1 11-APR-16 50000 250000
1 12-APR-16 100000 350000
1 13-APR-16 4000 346000
1 13-APR-16 20000 366000
1 14-APR-16 8000 358000
Please.
Thanks
rzkhan
|
|
|
|
| Re: running balance query [message #650084 is a reply to message #650083] |
Thu, 14 April 2016 12:44  |
 |
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Just use SUM in its analytical form:
SQL> select customerid, transaction_date, bill, received,
2 sum(nvl(received,0)-nvl(bill,0)) over
3 (partition by customerid order by transaction_date, rowid)
4 balance
5 from t1
6 order by customerid, transaction_date, rowid
7 /
CUSTOMERID TRANSACTION BILL RECEIVED BALANCE
---------- ----------- ---------- ---------- ----------
1 10-APR-2016 100000 100000
1 10-APR-2016 200000 300000
1 11-APR-2016 50000 250000
1 12-APR-2016 100000 350000
1 13-APR-2016 4000 346000
1 13-APR-2016 20000 366000
1 14-APR-2016 8000 358000
7 rows selected.
Note that '10-APR-2016' is not a date it is a string, the proof if I try to convert it to a date:
SQL> select to_date('10-APR-2016') from dual;
select to_date('10-APR-2016') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
|
|
|
|
|
|
| Re: running balance query [message #650091 is a reply to message #650084] |
Fri, 15 April 2016 00:16 |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
Thanks Michel.
and as for normalization. actually I have two different tables which are used for bill and receipts. And the query gets the data in the same format as I have given in the table.
I also have to join some other tables for different data. But my main problem was the running sum which has been very kindly explained by Michel.
Thanks a lot ..
Best wishes
[Updated on: Fri, 15 April 2016 00:18] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: running balance query [message #650177 is a reply to message #650147] |
Mon, 18 April 2016 12:47 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Rather then changing the nls_date_format, always use a mask in your to_date. For example to always convert a stored date string using the american format of DD-MON-YYYY use
SELECT TO_DATE('10-APR-2016','DD-MON-YYYY','NLS_DATE_LANGUAGE = American') FROM DUAL;
It will work no matter where the code is run in any language as long as the varchar2 string is always formatted the same. That being said, a date should ALWAYS be stored in a date column!!
[Updated on: Mon, 18 April 2016 12:48] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: running balance query [message #653787 is a reply to message #650218] |
Tue, 19 July 2016 01:34 |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
I am worried about the last 0 in the balance column
AG@orcl:>ed
Wrote file afiedt.buf
1 select customerid, transactiondate, nvl(sum_sale,0) as bill,nvl(sum_receipts,0) as received,
2 sum(nvl(sum_sale,0)-nvl(sum_receipts,0)) over
3 (partition by customerid order by transactiondate, rowid)
4 balance
5 from customer_ledger
6* order by customerid, transactiondate, rowid
AG@orcl:>/
CUSTOMERID TRANSACTI BILL RECEIVED BALANCE
---------- --------- ---------- ---------- ----------
443 31-DEC-15 5500 0 5500
443 14-FEB-16 3440 2500 6440
443 15-FEB-16 0 3440 3000
443 22-FEB-16 3660 3660 3000
443 03-MAR-16 0 2000 1000
443 05-MAR-16 4100 0 5100
443 06-MAR-16 0 1500 3600
443 14-MAR-16 2010 2100 3510
443 20-MAR-16 3280 1510 5280
443 21-MAR-16 0 2280 3000
443 26-MAR-16 0 2000 1000
443 27-MAR-16 5792 0 6792
443 28-MAR-16 0 2790 4002
443 02-APR-16 0 1500 2502
443 03-APR-16 4260 2260 4502
443 10-APR-16 2660 0 7162
443 11-APR-16 0 2660 4502
443 16-APR-16 0 2000 2502
443 24-APR-16 0 1000 1502
443 05-MAY-16 900 2402 0
20 rows selected.
Any idea please?
|
|
|
|
|
|
| Re: running balance query [message #653790 is a reply to message #653788] |
Tue, 19 July 2016 02:08 |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
It wont solve the 0 problem in the last record still..
AG@orcl:>ed
Wrote file afiedt.buf
1 select customerid, transactiondate, nvl(sum_sale,0) as bill,nvl(sum_receipts,0) as received,
2 sum(nvl(sum_receipts,0)-nvl(sum_sale,0)) over
3 (partition by customerid order by transactiondate, rowid)
4 balance
5 from customer_ledger
6* order by customerid, transactiondate, rowid
AG@orcl:>/
CUSTOMERID TRANSACTI BILL RECEIVED BALANCE
---------- --------- ---------- ---------- ----------
443 31-DEC-15 5500 0 -5500
443 14-FEB-16 3440 2500 -6440
443 15-FEB-16 0 3440 -3000
443 22-FEB-16 3660 3660 -3000
443 03-MAR-16 0 2000 -1000
443 05-MAR-16 4100 0 -5100
443 06-MAR-16 0 1500 -3600
443 14-MAR-16 2010 2100 -3510
443 20-MAR-16 3280 1510 -5280
443 21-MAR-16 0 2280 -3000
443 26-MAR-16 0 2000 -1000
443 27-MAR-16 5792 0 -6792
443 28-MAR-16 0 2790 -4002
443 02-APR-16 0 1500 -2502
443 03-APR-16 4260 2260 -4502
443 10-APR-16 2660 0 -7162
443 11-APR-16 0 2660 -4502
443 16-APR-16 0 2000 -2502
443 24-APR-16 0 1000 -1502
443 05-MAY-16 900 2402 0
20 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: running balance query [message #675634 is a reply to message #675633] |
Thu, 11 April 2019 01:09 |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
Test Case.
Three tables. CUSTOMERS, INVOICE and AMT_RCD
One View. example_view1
create table customers(
customerid number(5),
customername varchar2(60),
opening_balance number(7)
);
insert into customers values(1,'Customer One',200);
drop table invoice;
create table invoice(
customerid number(5),
empid number(5),
orderid number(5),
orderdate date,
productid number(5),
net_amount number(7)
);
insert into invoice values(1,1,1,SYSDATE,1,100);
insert into invoice values(1,1,1,SYSDATE,2,150);
insert into invoice values(1,1,3,SYSDATE,2,50);
drop table amt_rcd;
create table amt_rcd (
customerid number(5),
empid number(5),
trans_date DATE,
received number(7)
);
insert into amt_rcd values (1,1,SYSDATE-1,30);
insert into amt_rcd values (1,1,SYSDATE,20);
create view example_view1 as
select customerid,'Opening Balance' as narration, null as empid,null as orderid,null as trans_date,null as productid,opening_balance as net_amount, 0 as received from customers
where customerid=1
UNION ALL
select customerid,'Sale' as narration,empid,orderid,orderdate as trans_date,productid,net_amount as net_amount, 0 as received from invoice inv
UNION ALL
select customerid,'Payment' as narration,empid,null as orderid,trans_date as trans_date,null as productid,null as net_amount,received as recevied
from amt_rcd;
The query shows following error.
AG@orcl:>select customerid, trans_date, nvl(net_amount,0) as bill,nvl(received,0) as received,
2 sum(nvl(received,0)-nvl(net_amount,0)) over
3 (partition by customerid order by trans_date, rowid)
4 balance
5 from example_view1
6 order by customerid, trans_date, rowid
7 /
(partition by customerid order by trans_date, rowid)
*
ERROR at line 3:
ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.
AG@orcl:>
[Updated on: Thu, 11 April 2019 01:10] Report message to a moderator |
|
|
|
|
|
|
|
| Re: running balance query [message #675650 is a reply to message #675647] |
Thu, 11 April 2019 04:23 |
|
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You missed to specify your result.
From your posted query and result:
SQL> select customerid, narration, empid, orderid, trans_date, productid,
2 nvl(net_amount,0) as net_amount, nvl(received,0) as received,
3 sum(nvl(net_amount,0)-nvl(received,0))
4 over(partition by customerid order by trans_date nulls first, orderid, productid)
5 balance
6 from example_view1
7 order by customerid, trans_date nulls first, orderid, productid
8 /
CUSTOMERID NARRATION EMPID ORDERID TRANS_DATE PRODUCTID NET_AMOUNT RECEIVED BALANCE
---------- --------------- ---------- ---------- ------------------- ---------- ---------- ---------- ----------
1 Opening Balance 200 0 200
1 Payment 1 10/04/2019 11:08:13 0 30 170
1 Sale 1 1 11/04/2019 11:08:06 1 100 0 270
1 Sale 1 1 11/04/2019 11:08:06 2 150 0 420
1 Sale 1 3 11/04/2019 11:08:06 2 50 0 470
1 Payment 1 11/04/2019 11:08:13 0 20 450
6 rows selected.
|
|
|
|
|
|
| Re: running balance query [message #675683 is a reply to message #675663] |
Fri, 12 April 2019 01:08 |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
My one query is:
AG@orcl:>;
1* select * from vw_cledger_taj where customerid=762
AG@orcl:>/
CUSTOMERID NARRATION EMPID ORDERID TRANS_DAT PRODUCTID NET_AMOUNT RECEIVED
---------- --------------- ---------- ---------- --------- ---------- ---------- ----------
762 Opening Balance 500 0
762 Sale 5 3 09-APR-19 1 300000 0
762 Sale 9 6 12-APR-19 2 500000 0
762 Sale 9 6 12-APR-19 2 33 0
762 Payment 2 12-APR-19 300
and when I run to get running balance
AG@orcl:>select j.trans_date,j.customerid,j.narration,j.productid,j.net_amount,j.received ,
2 sum(nvl(net_amount,0)-nvl(received,0))
3 over(partition by customerid order by trans_date nulls first, orderid) as balance
4 from vw_cledger_taj j
5 where customerid=762
6 order by trans_date nulls first, orderid;
TRANS_DAT CUSTOMERID NARRATION PRODUCTID NET_AMOUNT RECEIVED BALANCE
--------- ---------- --------------- ---------- ---------- ---------- ----------
762 Opening Balance 500 0 500
09-APR-19 762 Sale 1 300000 0 300500
12-APR-19 762 Sale 2 500000 0 800533
12-APR-19 762 Sale 2 33 0 800533
12-APR-19 762 Payment 300 800233
There is an error in
12-APR-19 762 Sale 2 500000 0 800533
12-APR-19 762 Sale 2 33 0 800533
It shows 800533 for 3rd and 4th record. While it should be 80500 and 80533 respectively.
Can anyone see where is the problem please?
|
|
|
|
|
|
Goto Forum:
Current Time: Wed May 28 15:05:51 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
.