I have 2 tables in sqlserver
table1 is A Header Named : Accounts
Table 2 Is Body Named : transaction
Accounts :
| AccID | Accname | AccSubCode |
|---|---|---|
| 1 | Account1 | 3 |
| 2 | Account2 | 3 |
| 3 | Account3 | 1 |
Transaction:
| ID | ACCID | Debt | Credit | Post |
|---|---|---|---|---|
| 1 | 1 | 500 | 1 | |
| 2 | 1 | 300 | 0 | |
| 3 | 1 | 100 | 0 |
So I want the result show All Accounts Name Where AccSubcode = 3 And Post = 1
then Column show sum( debt ) - Sum (Credit ) as balance Column
I tried Code But it's Show me AccName if only has a Actions in transaction Table
and i want to Get All Accounts Name where AccSubcode = 3 And Post = 1 whith the balance Column even there is no any Actions For AccID in transaction table like this
| AccName | ACCID | balance |
|---|---|---|
| Accounts1 | 1 | 500 |
| Accounts2 | 2 | 0 |
i tried this Code but show only Accounts name if there is a transaction in transaction table
SELECT
A.AccName
,Max(ISNULL(A.AccID, 0)) AS [AccID]
,SUM(ISNULL(T.Debt, 0)) - SUM(ISNULL(T.Credit, 0))AS [Balance]
FROM Accounts AS A
LEFT JOIN Transaction AS T
ON A.AccID = T.AccID
where A.AccSubCode = 3 and T.Post = 1
GROUP BY A.AccID, A.AccName
To list all accounts even those with post = 0, then join by AccID and T.Post = 1 as follows :
SELECT A.AccName,
Max(ISNULL(A.AccID, 0)) AS [AccID],
SUM(ISNULL(T.Debt, 0)) - SUM(ISNULL(T.Credit, 0)) AS [Balance]
FROM Accounts AS A
LEFT JOIN Transactions AS T ON A.AccID = T.AccID AND T.Post = 1
WHERE A.AccSubCode = 3
GROUP BY A.AccID, A.AccName
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments