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 Go to next message
learner123
Messages: 4
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 118 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 Go to previous messageGo to next message
learner123
Messages: 4
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3045
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.
Re: opening and closing balance [message #683394 is a reply to message #683392] Thu, 07 January 2021 06:36 Go to previous message
learner123
Messages: 4
Registered: January 2021
Junior Member
Hi Solomon,

Thanks for your help, i tried with unbound but included one column extra in my query.
thanks a lot again.

Learner

[Updated on: Thu, 07 January 2021 06:37]

Report message to a moderator

Previous Topic: SQL query.
Next Topic: DBMS_HS_PASSTHROUGH - Database Link as Input Parameter
Goto Forum:
  


Current Time: Tue Apr 13 18:51:32 CDT 2021