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 Go to next message
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.

Report message to a moderator

Re: Oracle function to get account balance [message #687832 is a reply to message #687831] Fri, 23 June 2023 10:44 Go to previous messageGo to next message
Unclefool
Messages: 85
Registered: August 2021
Member
CREATE OR REPLACE FUNCTION get_account_balance(
 i_account_number IN TRANSACTIONS.ACCOUNT_NUMBER%TYPE
) RETURN TRANSACTIONS.TRANSACTION_AMOUNT%TYPE
IS
 v_balance TRANSACTIONS.TRANSACTION_AMOUNT%TYPE;
BEGIN
 SELECT SUM(
          CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END
          * transaction_amount
        )
 INTO   v_balance
 FROM   transactions
 WHERE  account_number = i_account_number -- one account
 OR     i_account_number IS NULL;         -- all accounts

 RETURN v_balance;
END;
/

SELECT CA.ACCOUNT_NUMBER,
      C.FIRST_NAME,
      C.LAST_NAME,
      CA.IS_ACTIVE,
      get_account_balance(ca.account_number) AS balance
FROM   CUSTOMER_ACCOUNTS CA
      INNER JOIN customers c
      ON ca.customer_id = c.customer_id;

Report message to a moderator

Re: Oracle function to get account balance [message #687833 is a reply to message #687832] Fri, 23 June 2023 11:30 Go to previous message
Michel Cadot
Messages: 68647
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.

Report message to a moderator

Previous Topic: Letter and TIMESTAMP CONCAT (INSERT error)
Next Topic: How create such attached table by travelling view and its dependent objects
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Apr 27 12:39:57 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.