Suppose I have two table same as above
Accounts
|
ACCTID
|
ACCTNAME
|
ACCT TYPE
|
Opening Balance
|
Date
|
|
12
|
CASH ACCT
|
DEBIT
|
1200
|
1-4-2023
|
|
13
|
BANK ACCT
|
DEBIT
|
1300
|
1-4-2023
|
|
14
|
SUPPLIER ACCT 1
|
CREDIT
|
1400
|
1-4-2023
|
|
15
|
SUPPLIER ACCT 2
|
CREDIT
|
800
|
1-4-2023
|
|
16
|
EXPESNES1
|
DEBIT
|
900
|
1-4-2023
|
Transactions Table
|
Accno
|
Acct Name
|
Acct Type
|
Debit amt
|
Credit amt
|
Date
|
|
12
|
CASH ACCT
|
DEBIT
|
5000
|
0
|
1-4-2023
|
|
12
|
CASH ACCT
|
DEBIT
|
1000
|
0
|
1-4-023
|
|
12
|
CASH ACCT
|
CREDIT
|
0
|
2000
|
1-4-2023
|
|
12
|
CASH ACCT
|
DEBIT
|
1000
|
0
|
2-4-2023
|
|
13
|
BANK ACCT
|
CREDIT
|
0
|
200
|
1-4-2023
|
|
13
|
BBANK ACCT
|
DEBIT
|
4000
|
0
|
2-4-2023
|
|
13
|
BANK ACCT
|
CREDIT
|
300
|
0
|
3-4-2023
|
|
14
|
SUPPLIER ACCT
|
CREDIT
|
0
|
400
|
1-4-2023
|
|
14
|
SUPPLIER ACCT
|
CREDIT
|
0
|
300
|
3-4-2023
|
|
15
|
SUPPLIER ACCT 2
|
DEBIT
|
2000
|
0
|
1-4-2023
|
|
15
|
SUPPLIER ACCT 2
|
CREDIT
|
0
|
1000
|
4-4-2023
|
I HVE TO DISPLAY VALUE LIKE THIS
Opening balance will show according to credit and debit acct type for example
From date : 2023-04-01 to date 2023-04-02
|
Acct name
|
Opening balance(cr)
|
Opening balance (dr)
|
Credit(sum)
|
Debit(sum)
|
Closing credit
|
Closing debit
|
|
Casah acct
|
0
|
1200
|
2000
|
6000
|
0
|
5200
|
|
Bank acct
|
0
|
1300
|
600
|
4000
|
0
|
4700
|
|
Supplier acct
|
1400
|
0
|
700
|
100
|
2200
|
0
|
|
Supplier acct2
|
800
|
0
|
2000
|
2000
|
1800
|
|
|
|
|
|
|
|
|
|
Opening balance will show debit side and credit side according to account type
And opening balance will be change according to date . I mean closing balance will become to opening balance on next date
I have to display the record according to date range wise (date between wise )
Please help me in this situation I will highly thank full to you