0

I have this query below in which I am trying to get results for the month and year to date (account balance).

SELECT T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit)* -1 AS 'Balance'
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-09-01' AND '2014-09-30'
AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName
UNION 
SELECT  T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit)* -1 AS 'YTD Balance'
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-01-01' AND '2014-09-30'
AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName
ORDER BY T3.AcctCode

The results are as below:

enter image description here

The problem I have is that the Balance and Year to Date balance columns are stacked on on top of the other. How can I modify this query to have 4 columns, i.e: AcctCode, AcctName, Balance, YTD Balance? Using SQL Server 2008

  • And why tf (Debit-Credit)* -1? Why not just (Credit-Debit)? – ypercubeᵀᴹ Apr 7 '15 at 13:57
2

Since the two parts are identical except for the WHERE conditions where the first is just a restriction of the second, you can use one query, with the 2nd condition and an altered condition for the 1st sum, i.e. a CASE expression:

SELECT T3.AcctCode, T3.AcctName, 
       SUM(CASE WHEN T0.TaxDate BETWEEN '2014-09-01' AND '2014-09-30'
             THEN T1.Debit - T1.Credit 
             ELSE 0 END)* -1          AS Balance,
       SUM(T1.Debit - T1.Credit)* -1  AS [YTD Balance] 

FROM OJDT T0
  INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
  INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
  INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-01-01' AND '2014-09-30'
  AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName ;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.