Click here to Skip to main content
15,922,574 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:

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	Credit opening balance	Debit opening balance	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 



What I have tried:

i need help please to get the solution please
Posted
Updated 4-Apr-23 17:45pm
v2

1 solution

Debit or credit is a type of balance; not an "account".

Account types include: Assets and liabilities; short term (e.g. cash; payables) or longterm (loans).

A certain account may tend to have a debit or credit balance, but it can also go the other way; e.g. overdrawn; turning a debit balance into a credit balance.

The sum of ALL debits and credits before a given date is the "opening balance" for that date (for that account); regardless of the sign of the balance.
 
Share this answer
 
Comments
samhasan 5-Apr-23 10:33am    
Hi..Thanks for your respon..i share over a sample schema with data..acct type to just show its creditor or debitor account ..from one table which is called transection table i can find the all details which i need..but when its show closing balance at dr side it should show closing balance at cr side o same as opening balance ..i tried to clearfy may be you got it..thanx
so please help me with some example with sql query (using two tables (one table have only opening balance) seconfd table with have crdit and debit amount for each account ..thanx

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900