Home » SQL & PL/SQL » SQL & PL/SQL » opening and closing balance (oracle 19c)
opening and closing balance [message #683388] |
Thu, 07 January 2021 02:58 |
|
learner123
Messages: 5 Registered: January 2021
|
Junior Member |
|
|
Hi Experts,
I am trying to generate a report for opening and closing balance from a a table transaction_detail where this particular table contains below
information. below account and date wise amount should sum up and display in the separate column as closing balance grouping acc_num and
Trans_Date and closing balance should be displayed as a opening balance to next date like as shown in output.
Example: for account number 111 and date 1/1/2021 there are 4 transaction after summing it should be 1000-100+200+300 = 1400
1400 should be displayed as closing balance for all 1/1/2021 and 111, then 1400 should be opening balance to 2/1/2021.
kindly help me to achieve this and please refer the attached files also. please forgive me i don't know add this in tags.
Table : Transaction_Detail
Trans_Date Acc_Num Trans_Type Amount
1/1/2021 111 CREDIT 1000
1/1/2021 111 DEBIT -100
1/1/2021 111 CREDIT 200
1/1/2021 111 CREDIT 300
2/1/2021 111 DEBIT -40
2/1/2021 111 CREDIT 70
2/1/2021 111 DEBIT -10
3/1/2021 111 CREDIT 100
1/1/2021 222 CREDIT 100
Output
---------
Trans_Date Acc_Num Trans_Type Amount Opening_Balance Closing_Balance
1/1/2021 111 CREDIT 1000 0 1400
1/1/2021 111 DEBIT -100 0 1400
1/1/2021 111 CREDIT 200 0 1400
1/1/2021 111 CREDIT 300 0 1400
2/1/2021 111 DEBIT -40 1400 1420
2/1/2021 111 CREDIT 70 1400 1420
2/1/2021 111 DEBIT -10 1400 1420
3/1/2021 111 CREDIT 100 1420 1520
1/1/2021 222 CREDIT 100 0 100
with t as
(
Select '01-Jan-2021' Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 1000 Amount From Dual
Union All
Select '01-Jan-2021' Trans_Date , 111 Acc_Num, 'DEBIT' Trans_Type, -100 Amount From Dual
Union All
Select '01-Jan-2021' Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 200 Amount From Dual
Union All
Select '01-Jan-2021' Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 300 Amount From Dual
Union All
Select '02-Jan-2021' Trans_Date , 111 Acc_Num, 'DEBIT' Trans_Type, -40 Amount From Dual
Union All
Select '02-Jan-2021' Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 70 Amount From Dual
Union All
Select '02-Jan-2021' Trans_Date , 111 Acc_Num, 'DEBIT' Trans_Type, -10 Amount From Dual
Union All
Select '03-Jan-2021' Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 100 Amount From Dual
Union All
Select '01-Jan-2021' Trans_Date , 222 Acc_Num, 'CREDIT' Trans_Type, 100 Amount From Dual
)
select * from t
-
Attachment: 4.PNG
(Size: 27.00KB, Downloaded 1213 times)
[Updated on: Thu, 07 January 2021 05:36] Report message to a moderator
|
|
|
opening and closing balance [message #683389 is a reply to message #683388] |
Thu, 07 January 2021 04:50 |
|
learner123
Messages: 5 Registered: January 2021
|
Junior Member |
|
|
Dear Experts,
Can you please help with with the below report.
Transaction_Detail
Trans_Date Acc_Num Trans_Type Amount
1/1/2021 111 CREDIT 1000
1/1/2021 111 DEBIT -100
1/1/2021 111 CREDIT 200
1/1/2021 111 CREDIT 300
2/1/2021 111 DEBIT -40
2/1/2021 111 CREDIT 70
2/1/2021 111 DEBIT -10
3/1/2021 111 CREDIT 100
1/1/2021 111 CREDIT 100
Output
Trans_Date Acc_Num Trans_Type Amount Opening_Balance Closing_Balance
1/1/2021 111 CREDIT 1000 0 1400
1/1/2021 111 DEBIT -100 0 1400
1/1/2021 111 CREDIT 200 0 1400
1/1/2021 111 CREDIT 300 0 1400
2/1/2021 111 DEBIT -40 1400 1420
2/1/2021 111 CREDIT 70 1400 1420
2/1/2021 111 DEBIT -10 1400 1420
3/1/2021 111 CREDIT 100 1420 1520
1/1/2021 222 CREDIT 100 0 100
with t as
(
Select '01-Jan-2021' Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 1000 Amount From Dual
Union All
Select '01-Jan-2021' Trans_Date , 111 Acc_Num, 'DEBIT' Trans_Type, -100 Amount From Dual
Union All
Select '01-Jan-2021' Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 200 Amount From Dual
Union All
Select '01-Jan-2021' Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 300 Amount From Dual
Union All
Select '02-Jan-2021' Trans_Date , 111 Acc_Num, 'DEBIT' Trans_Type, -40 Amount From Dual
Union All
Select '02-Jan-2021' Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 70 Amount From Dual
Union All
Select '02-Jan-2021' Trans_Date , 111 Acc_Num, 'DEBIT' Trans_Type, -10 Amount From Dual
Union All
Select '03-Jan-2021' Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 100 Amount From Dual
Union All
Select '01-Jan-2021' Trans_Date , 222 Acc_Num, 'CREDIT' Trans_Type, 100 Amount From Dual
)
select * from t
|
|
|
Re: opening and closing balance [message #683392 is a reply to message #683388] |
Thu, 07 January 2021 06:17 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
First of all, '01-Jan-2021' is string and not a date. Anyway:
with t as
(
select to_date('01-Jan-2021','DD-MON-YYYY') Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 1000 Amount From Dual
Union All
select to_date('01-Jan-2021','DD-MON-YYYY') Trans_Date , 111 Acc_Num, 'DEBIT' Trans_Type, -100 Amount From Dual
Union All
select to_date('01-Jan-2021','DD-MON-YYYY') Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 200 Amount From Dual
Union All
select to_date('01-Jan-2021','DD-MON-YYYY') Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 300 Amount From Dual
Union All
select to_date('02-Jan-2021','DD-MON-YYYY') Trans_Date , 111 Acc_Num, 'DEBIT' Trans_Type, -40 Amount From Dual
Union All
select to_date('02-Jan-2021','DD-MON-YYYY') Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 70 Amount From Dual
Union All
select to_date('02-Jan-2021','DD-MON-YYYY') Trans_Date , 111 Acc_Num, 'DEBIT' Trans_Type, -10 Amount From Dual
Union All
select to_date('03-Jan-2021','DD-MON-YYYY') Trans_Date , 111 Acc_Num, 'CREDIT' Trans_Type, 100 Amount From Dual
Union All
select to_date('01-Jan-2021','DD-MON-YYYY') Trans_Date , 222 Acc_Num, 'CREDIT' Trans_Type, 100 Amount From Dual
)
select t.*,
nvl(
sum(amount) over(
partition by acc_num order by trans_date
range between unbounded preceding and 1 preceding
),
0
) opening_balance,
sum(amount) over(
partition by acc_num order by trans_date
) closing_balance
from t
order by acc_num,
trans_date
/
TRANS_DAT ACC_NUM TRANS_ AMOUNT OPENING_BALANCE CLOSING_BALANCE
--------- ---------- ------ ---------- --------------- ---------------
01-JAN-21 111 CREDIT 1000 0 1400
01-JAN-21 111 DEBIT -100 0 1400
01-JAN-21 111 CREDIT 200 0 1400
01-JAN-21 111 CREDIT 300 0 1400
02-JAN-21 111 DEBIT -40 1400 1420
02-JAN-21 111 DEBIT -10 1400 1420
02-JAN-21 111 CREDIT 70 1400 1420
03-JAN-21 111 CREDIT 100 1420 1520
01-JAN-21 222 CREDIT 100 0 100
9 rows selected.
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 01:58:41 CDT 2024
|