How to display ledger report

Question

0.00/5 (No votes)

See more:

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 DrCr
1 Salary Expense 10000 DR
2 Cash Asset 20000 DR
3 Electricty Expense 1000 DR

Transaction Table has

Txno, Tandate, LID, debit credit
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.

What I have tried:

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 WHERE LID = 3)) t2 ON T1.Lid = t2.LID
     INNER JOIN (SELECT TxnNo, Lid FROM [Transaction] WHERE Lid = 3) t3 ON t3.TxnNo = T1.TxnNo                                                                    ;

Posted 29-Jun-22 8:12am

Wave 13536632

Updated 30-Jun-22 18:02pm

v2

Add a Solution

Comments

CHill60 30-Jun-22 5:21am

Part of the problem with trying to help you is that the column names in your query do not match the schema of the tables with the sample data - e.g. there are no columns called Debit and Credit - help us to help you and give us the correct table schemas/sample data.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2022-06-29T23:53:00

Your joins are overly complex.

Look at the sub-query

SQL

SELECT LID,
                        LName
                 FROM @Ledger
                 WHERE LID NOT IN (SELECT LID FROM @Ledger WHERE LID = 3)

That is exactly the same as

SQL

SELECT LID,
                        LName
                 FROM @Ledger
                 WHERE LID <> 3

Except the 2nd version is more performant and easier to understand.

Now I can see that you are attempting to extract records where LID is not 3 AND where LID is 3 - which actually returns nothing. Perhaps this article will help explain why Visual Representation of SQL Joins[^]

Your expected results do not seem to match the sample data you provided - for example the Cash Ledger is listing everything except Cash. Try something like the following

SQL

SELECT T1.TxnNo,
       T1.Txndate,
       L.LName,
       ISNULL(T1.Debit, 0) AS Dr,
       ISNULL(T1.Credit, 0) AS Cr
FROM @Transaction T1
INNER JOIN @Ledger L ON T1.Lid = L.LID
WHERE L.LID = 2 --Cash. Change to 1 for Salary or 3 for Electricity
-- Alternatively use WHERE L.LName = 'Cash' for clarity

You also appear to need a running balance. Try googling for "SQL Running Total" e.g. https://betterprogramming.pub/4-ways-to-calculate-a-running-total-with-sql-986d0019185c[^]