There are 2 tables Ledger and transaction
Ledger Table has below fields
LID,LName,Lgroup,Opbal
Transaction Table has Txno,Tandate,LID,TXnType,Amount,Narration
Below Are The data
Ledger Table
LID, LName, Lgroup, Opbal
1 Salary Expense 10000
2 Cash Asset 20000
3 Electricty Expense -1000
Transaction Table has
Txno, Tandate, LID,Amount
1 2022-01-01 2 -10000
1 2022-01-01 1 2000
1 2022-01-01 3 8000
Required Ledger Display
If Cash Ledger is selected
Txno Txndate Lanme Debit Credit Balance
1 2022-01-01 Salary 2000 18000
1 2022-01-01 Electricty 8000 10000
If Salary Ledger is selected
Txno Txndate Lanme Debit Credit Balance
1 2022-01-01 cash 2000 12000
If Electricity Ledger is selected
Txno Txndate Lanme Debit Credit balance
1 2022-01-01 cash 8000 7000
Have tried the below SQL but it does not work - for example if I query Salary ledger the all the ledger including Salary ledger is displayed, rather than the expected result I showed above.
SELECT T1.TxnNo,
T1.Txndate,
t2.LName,
ISNULL(T1.Debit, 0) AS Dr,
ISNULL(T1.Credit, 0) AS Cr
FROM [Transaction] T1
INNER JOIN (SELECT LID,
LName
FROM Ledger
WHERE LID IN (SELECT Ledger.LID FROM Ledger)) t2 ON T1.Lid = t2.LID
INNER JOIN (SELECT TxnNo, Lid FROM [Transaction] ) t3 ON t3.TxnNo = T1.TxnNo AND t2.LID = t3.Lid where t2.LID=1;
db<>fiddle here
The Logic is when a Ledger is selected the Other ledger posted against that ledger should be displayed as per the Voucher entry. IF as per the voucher entry if cash transaction is done cash voucher is passed debiting the expense like salary and electricity. So when we query on cash ledger the result should display Electricity and Salary and if salary OR electricity ledger is queried then Cash Ledger Should be displayed with amount of Salary Or Amount electricity as per the query