Home » SQL & PL/SQL » SQL & PL/SQL » Oracle function to get account balance (19c)
Oracle function to get account balance [message #687831] |
Fri, 23 June 2023 05:45 |
|
Unclefool
Messages: 85 Registered: August 2021
|
Member |
|
|
I have customers, which have multiple accounts. For each account I keep a separate balance.
As you can see below I can produce a detailed daily summary of each account. What would be the best way to wrap this detail into a function, which returns only the final balance of the account?
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE TABLE CUSTOMERS (
CUSTOMER_ID, FIRST_NAME, LAST_NAME,IS_ACTIVE) AS
SELECT 'A355135', 'Otto', 'Vatsch','Y' FROM DUAL UNION ALL
SELECT 'B375480', 'Sophia', 'Fazio','Y' FROM DUAL UNION ALL
SELECT 'C378853', 'Brian', 'Vendome','Y' FROM DUAL UNION ALL
SELECT 'D379171', 'John', 'Carucci','Y' FROM DUAL UNION ALL
SELECT 'E379466', 'Bonnie', 'Winterbottom','Y' FROM DUAL UNION ALL
SELECT 'F379739', 'Debra', 'Caygle','Y' FROM DUAL UNION ALL
SELECT 'G379994', 'Jerry', 'Torciano','Y' FROM DUAL UNION ALL
SELECT 'H380235', 'Karl', 'Malden','Y' FROM DUAL UNION ALL
SELECT 'I380492', 'Joseph', 'Zaza','Y' FROM DUAL UNION ALL
SELECT 'J380753', 'Tom', 'Micelli','Y' FROM DUAL UNION ALL
SELECT 'K380989', 'Lisa', 'Saladino','Y' FROM DUAL UNION ALL
SELECT 'L381307', 'Jeff', 'Lebowitz','Y' FROM DUAL UNION ALL
SELECT 'M381569', 'Brian', 'Zanona','Y' FROM DUAL UNION ALL
SELECT 'N381823', 'Seth', 'Bobet','Y' FROM DUAL UNION ALL
SELECT 'O382059', 'Mitch', 'Weinreb','Y' FROM DUAL UNION ALL
SELECT 'P382319', 'Roz', 'Stern','Y' FROM DUAL UNION ALL
SELECT 'Q382564', 'Zoey', 'Zanzone','Y' FROM DUAL UNION ALL
SELECT 'R382815', 'Diane', 'Stein','Y' FROM DUAL UNION ALL
SELECT 'S441015', 'Tony', 'Dimeo','Y' FROM DUAL UNION ALL
SELECT 'T441333', 'Faith', 'Carrucci','Y' FROM DUAL UNION ALL
SELECT 'X098533', 'Brian', 'Tessio','Y' FROM DUAL UNION ALL
SELECT 'Y098273', 'Cheryl', 'Brasi','Y' FROM DUAL UNION ALL
SELECT 'Z098555', 'Peter', 'Clemenza','Y' FROM DUAL;
CREATE TABLE CUSTOMER_ACCOUNTS (
ACCOUNT_NUMBER,
CUSTOMER_ID, VENDOR_ID,
IS_ACTIVE) AS
SELECT 'B17ARWYYZRCU2Q2',
'D379171', 8, 'Y' FROM DUAL UNION ALL
SELECT '0T81Z07CS6LXQ7Z',
'D379171', 7, 'Y' FROM DUAL UNION ALL
SELECT 'YWYXC3Q5N9XZ7S',
'D379171', 7, 'Y' FROM DUAL UNION ALL
SELECT '612ZKAQ66VA3W3',
'D379171', 4, 'Y' FROM DUAL UNION ALL
SELECT 'THVQD6M9LR7AVK', 'E379466', 5, 'Y' FROM DUAL UNION ALL
SELECT 'CFM9K06ZR98R9H2', 'K380989', 1, 'Y' FROM DUAL UNION ALL
SELECT '0Z8NQN6JZRZWFPX',
'K380989', 2, 'Y' FROM DUAL UNION ALL
SELECT 'BCHD9TW78W67S1D',
'K380989', 3, 'Y' FROM DUAL UNION ALL
SELECT '01SUV1Y3BJTCFXY',
'X098533', 1, 'Y' FROM DUAL UNION ALL
SELECT 'TAJ3N5EB9ZX7AD',
'X098533', 2, 'Y' FROM DUAL UNION ALL
SELECT 'Y88JTBCP8SUFY8',
'X098533', 3, 'Y' FROM DUAL UNION ALL
SELECT '06LP3CYJLS01F2L',
'Y098273', 7, 'Y' FROM DUAL UNION ALL
SELECT 'TFWVBRC5QHQLC4', 'Y098273', 8, 'Y' FROM DUAL UNION ALL
SELECT '0Z76WT5NTLRZPTW', 'Z098555', 10, 'Y' FROM DUAL;
create table transactions (
transaction_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
account_number VARCHAR2(15),
transaction_type varchar2(1),
transaction_amount NUMBER(10,2),
transaction_date DATE
);
insert into transactions(
account_number, transaction_type, transaction_amount, transaction_date)
SELECT '0Z8NQN6JZRZWFPX', 'D', 1000 * LEVEL, date '2023-03-29' + level * interval '2' day from dual
connect by level <= 3
union all
SELECT '0Z8NQN6JZRZWFPX', 'C', 500 * LEVEL, date '2023-03-30' + level * interval '2' day from dual
connect by level <= 2
WITH daily_summary AS
(
SELECT
account_number,
transaction_date
, SUM (DECODE (transaction_type, 'C', transaction_amount, 0)) AS credit_total
, SUM (DECODE (transaction_type, 'D', transaction_amount , 0)) AS debit_total
FROM transactions
GROUP BY account_number, transaction_date
)
SELECT d.*
, SUM (debit_total - credit_total)
OVER (ORDER BY transaction_date) AS balance_to_date
FROM daily_summary d
WHERE account_number ='0Z8NQN6JZRZWFPX'
ORDER BY transaction_date;
ACCOUNT_NUMBER TRANSACTION_DATE CREDIT_TOTAL DEBIT_TOTAL BALANCE_TO_DATE
0Z8NQN6JZRZWFPX 31-MAR-2023 00:00:00 0 1000 1000
0Z8NQN6JZRZWFPX 01-APR-2023 00:00:00 500 0 500
0Z8NQN6JZRZWFPX 02-APR-2023 00:00:00 0 2000 2500
0Z8NQN6JZRZWFPX 03-APR-2023 00:00:00 1000 0 1500
0Z8NQN6JZRZWFPX 04-APR-2023 00:00:00 0 3000 4500
Looking to pass in the account number and produce this output.
SELECT
CA.ACCOUNT_NUMBER,
C.FIRST_NAME,
C.LAST_NAME,
CA.IS_ACTIVE,
Get_Acccount_Balance('0Z8NQN6JZRZWFPX')
FROM CUSTOMER_ACCOUNTS CA
JOIN customers c ON ca.customer_id = c.customer_id
ACCOUNT_NUMBER FIRST_NAME LAST_NAME IS_ACTIVE BALANCE
0Z8NQN6JZRZWFPX Lisa Saladino Y 4500
Note:if the credit total is higher than the balance could be negative.
|
|
|
|
Re: Oracle function to get account balance [message #687833 is a reply to message #687832] |
Fri, 23 June 2023 11:30 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> SELECT CA.ACCOUNT_NUMBER,
2 C.FIRST_NAME,
3 C.LAST_NAME,
4 CA.IS_ACTIVE,
5 get_account_balance(ca.account_number) AS balance
6 FROM CUSTOMER_ACCOUNTS CA
7 INNER JOIN customers c
8 ON ca.customer_id = c.customer_id
9 order by 1
10 /
ACCOUNT_NUMBER FIRST_ LAST_NAME I BALANCE
--------------- ------ ------------ - ----------
01SUV1Y3BJTCFXY Brian Tessio Y
06LP3CYJLS01F2L Cheryl Brasi Y
0T81Z07CS6LXQ7Z John Carucci Y
0Z76WT5NTLRZPTW Peter Clemenza Y
0Z8NQN6JZRZWFPX Lisa Saladino Y 4500
612ZKAQ66VA3W3 John Carucci Y
B17ARWYYZRCU2Q2 John Carucci Y
BCHD9TW78W67S1D Lisa Saladino Y
CFM9K06ZR98R9H2 Lisa Saladino Y
TAJ3N5EB9ZX7AD Brian Tessio Y
TFWVBRC5QHQLC4 Cheryl Brasi Y
THVQD6M9LR7AVK Bonnie Winterbottom Y
Y88JTBCP8SUFY8 Brian Tessio Y
YWYXC3Q5N9XZ7S John Carucci Y
14 rows selected.
No need of the function:
SQL> SELECT CA.ACCOUNT_NUMBER,
2 C.FIRST_NAME,
3 C.LAST_NAME,
4 CA.IS_ACTIVE,
5 (SELECT SUM(CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END
6 * transaction_amount)
7 FROM transactions
8 WHERE account_number = ca.account_number -- one account
9 OR ca.account_number IS NULL) -- all accounts
10 AS balance
11 FROM CUSTOMER_ACCOUNTS CA
12 INNER JOIN customers c
13 ON ca.customer_id = c.customer_id
14 order by 1
15 /
ACCOUNT_NUMBER FIRST_ LAST_NAME I BALANCE
--------------- ------ ------------ - ----------
01SUV1Y3BJTCFXY Brian Tessio Y
06LP3CYJLS01F2L Cheryl Brasi Y
0T81Z07CS6LXQ7Z John Carucci Y
0Z76WT5NTLRZPTW Peter Clemenza Y
0Z8NQN6JZRZWFPX Lisa Saladino Y 4500
612ZKAQ66VA3W3 John Carucci Y
B17ARWYYZRCU2Q2 John Carucci Y
BCHD9TW78W67S1D Lisa Saladino Y
CFM9K06ZR98R9H2 Lisa Saladino Y
TAJ3N5EB9ZX7AD Brian Tessio Y
TFWVBRC5QHQLC4 Cheryl Brasi Y
THVQD6M9LR7AVK Bonnie Winterbottom Y
Y88JTBCP8SUFY8 Brian Tessio Y
YWYXC3Q5N9XZ7S John Carucci Y
14 rows selected.
Or better:
SQL> with
2 balances as (
3 select nvl(account_number,'XXXXXXXXXXXXXXX') account_number,
4 SUM(CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END
5 * transaction_amount) balance
6 FROM transactions
7 group by rollup(account_number)
8 )
9 SELECT CA.ACCOUNT_NUMBER,
10 C.FIRST_NAME,
11 C.LAST_NAME,
12 CA.IS_ACTIVE,
13 b.balance
14 FROM CUSTOMER_ACCOUNTS CA INNER JOIN customers c ON ca.customer_id = c.customer_id
15 left outer join balances b on b.account_number = nvl(ca.account_number,'XXXXXXXXXXXXXXX')
16 order by 1
17 /
ACCOUNT_NUMBER FIRST_ LAST_NAME I BALANCE
--------------- ------ ------------ - ----------
01SUV1Y3BJTCFXY Brian Tessio Y
06LP3CYJLS01F2L Cheryl Brasi Y
0T81Z07CS6LXQ7Z John Carucci Y
0Z76WT5NTLRZPTW Peter Clemenza Y
0Z8NQN6JZRZWFPX Lisa Saladino Y 4500
612ZKAQ66VA3W3 John Carucci Y
B17ARWYYZRCU2Q2 John Carucci Y
BCHD9TW78W67S1D Lisa Saladino Y
CFM9K06ZR98R9H2 Lisa Saladino Y
TAJ3N5EB9ZX7AD Brian Tessio Y
TFWVBRC5QHQLC4 Cheryl Brasi Y
THVQD6M9LR7AVK Bonnie Winterbottom Y
Y88JTBCP8SUFY8 Brian Tessio Y
YWYXC3Q5N9XZ7S John Carucci Y
14 rows selected.
|
|
|
Goto Forum:
Current Time: Sun Sep 29 01:00:02 CDT 2024
|