how can i get the oppening + debit - credit = balance, in ms-sql using case

Question

How can I get the balance?

Here what I'm trying to do, oppeningBalance + Debit - Credit = Balance.

SELECT
GL.AccountId,
CA.Code,
CA.AccountName,
SUM(CASE GL.DrCr WHEN 2 THEN Amount END) AS Debit, 
SUM(CASE GL.DrCr WHEN 1 THEN Amount END) AS Credit,
Sum(CA.OpeningBalance + Debit - Credit) as Balance
FROM GeneralLedgerLine GL 
Join ClientAccount CA on CA.Id = GL.AccountId
Join GenralLedgerHeader GH on GL.GeneralLedgerHeaderId = GH.Id
GROUP BY
    GL.AccountId, CA.Code, CA.AccountName

this is the Query what i tried but it showing this error message:

Msg 207, Level 16, State 1, Line 19 Invalid column name 'Debit'. Msg 207, Level 16, State 1, Line 19 Invalid column name 'Credit'

You can't reference another column by the alias in the same query. — Sean Lange, Oct 11 '18 at 14:07

Squirrel · Accepted Answer · 2018-10-11T14:14:43.867

2

Change the Balance as follow

Sum(CA.OpeningBalance + CASE WHEN GL.DrCr = 2 THEN Amount ELSE -Amount END) as Balance

Note : you can't reference the alias on the same level

edited Oct 11 '18 at 14:14

answered Oct 11 '18 at 14:03

Squirrel

23,507
4
34
32

Chad Estes · Answer 2 · 2018-10-11T19:14:31.337

Try either of the following:

SELECT
GL.AccountId,
CA.Code,
CA.AccountName,
SUM(CASE GL.DrCr WHEN 2 THEN Amount END) AS Debit, 
SUM(CASE GL.DrCr WHEN 1 THEN Amount END) AS Credit,
Sum(CA.OpeningBalance + CASE GL.DrCr WHEN 2 THEN Amount END - CASE GL.DrCr WHEN 1 THEN Amount END) as Balance
FROM GeneralLedgerLine GL 
Join ClientAccount CA on CA.Id = GL.AccountId
Join GenralLedgerHeader GH on GL.GeneralLedgerHeaderId = GH.Id
GROUP BY
    GL.AccountId, CA.Code, CA.AccountName

OR

SELECT
  AccountId,
  Code,
  AccountName,
  Debit,
  Credit,
  Sum(OpeningBalance + Debit - Credit) as Balance
FROM ( SELECT
         GL.AccountId,
         CA.Code,
         CA.AccountName,
         SUM(CASE GL.DrCr WHEN 2 THEN Amount END) AS Debit, 
         SUM(CASE GL.DrCr WHEN 1 THEN Amount END) AS Credit,
         SUM(CA.OpeningBalance) AS OpeningBalance
       FROM GeneralLedgerLine GL 
       Join ClientAccount CA on CA.Id = GL.AccountId
       Join GenralLedgerHeader GH on GL.GeneralLedgerHeaderId = GH.Id
     GROUP BY
       GL.AccountId, CA.Code, CA.AccountName
) AS SubQuery

The reason you are getting an error is because your are defining the name for the column and attempting to reference it in the same operation, which is not possible as of SQL Server 2017. If you either convert your reference to the calculated value (shown in the top code block) or if you do your calculation on the calculated columns in an outer query (shown in bottom code block) you shouldn't have an issue.

EDIT: Sorry was doing this quickly from my phone yesterday while waiting in the doctor's office. Queries corrected.

Msg 130, Level 15, State 1, Line 9 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.@Chad Estes the first Query showing this error message — The_Programmer92, Oct 11 '18 at 18:23
Msg 8120, Level 16, State 1, Line 16 Column 'ClientAccount.OpeningBalance' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.@Chad Estes, and last one showing this error — The_Programmer92, Oct 11 '18 at 18:26

how can i get the oppening + debit - credit = balance, in ms-sql using case

2 Answers2