Merging two Select queries with different where statements 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:



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
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 ;