Why don't you use PIVOT ?

>I need to create a query with columns containing quarterly figures. I figured I could handle that with subqueries, but I am trying to make this work for a long time already and it still does not seem right.
>The final result now gives the correct figures, but I cannot add the last quarter to it because of the join with the GL accounts, if there is no result in the subquery, it drops the entire line.
>I must be doing something wrong, but I can't figure out how to correct it.
>
>I hardcoded the where clauses and removed some fields to make it more readable:
>
>

>SELECT  Departments.deName,
>		GLSub.gsAccount AS Account,
>		Total.itAmount AS Total,
>		JanMrt.itAmount AS JanMrt,
>		AprJun.itAmount AS AprJun,
>		JulSep.itAmount AS JulSep,
>		JanMrt.itAmount + AprJun.itAmount + JulSep.itAmount AS Test
>	FROM PayEmps
>	
>	JOIN Departments
>		ON Departments.DeKey = PayEmps.paDeKey
>		
>	JOIN Runs
>	ON Runs.ruKey = PayEmps.paRuKey
>
>	LEFT JOIN 
>	(SELECT IT.itGsKey,
>			PE.paDeKey,
>			SUM(IT.itAmount) AS itAmount
>			FROM dbo.HistItems IT 
>			JOIN Runs RU ON It.itRuKey = RU.ruKey
>			JOIN PayEmps PE ON IT.itEmKey = PE.paEmKey AND IT.itRuKey = PE.paRuKey
>			JOIN GlSub ON GlSub.gsKey = IT.itGsKey
>			WHERE RU.ruYear = 2015
>			  AND GLSub.gsAccount = '4101'
>			GROUP BY IT.itGsKey, PE.paDeKey) as Total
>	ON PayEmps.paDeKey = Total.paDeKey
>
>	LEFT JOIN 
>	(SELECT IT.itGsKey,
>			PE.paDeKey,
>			SUM(IT.itAmount) AS itAmount
>			FROM dbo.HistItems IT 
>			JOIN Runs RU ON It.itRuKey = RU.ruKey
>			JOIN PayEmps PE ON IT.itEmKey = PE.paEmKey AND IT.itRuKey = PE.paRuKey
>			JOIN GlSub ON GlSub.gsKey = IT.itGsKey
>			WHERE RU.ruYear = 2015
>			  AND MONTH(RU.ruStartDat) IN (1,2,3)
>			  AND GLSub.gsAccount = '4101'
>			GROUP BY IT.itGsKey, PE.paDeKey) as JanMrt
>	ON PayEmps.paDeKey = JanMrt.paDeKey
>
>	LEFT JOIN 
>	(SELECT IT.itGsKey,
>			PE.paDeKey,
>			SUM(IT.itAmount) AS itAmount
>			FROM dbo.HistItems IT 
>			JOIN Runs RU ON It.itRuKey = RU.ruKey
>			JOIN PayEmps PE ON IT.itEmKey = PE.paEmKey AND IT.itRuKey = PE.paRuKey
>			JOIN GlSub ON GlSub.gsKey = IT.itGsKey
>			WHERE RU.ruYear = 2015
>			  AND MONTH(RU.ruStartDat) IN (4,5,6)
>			  AND GLSub.gsAccount = '4101'
>			GROUP BY IT.itGsKey, PE.paDeKey) as AprJun
>	ON PayEmps.paDeKey = AprJun.paDeKey
>
>	LEFT JOIN 
>	(SELECT IT.itGsKey,
>			PE.paDeKey,
>			SUM(IT.itAmount) AS itAmount
>			FROM dbo.HistItems IT 
>			JOIN Runs RU ON It.itRuKey = RU.ruKey
>			JOIN PayEmps PE ON IT.itEmKey = PE.paEmKey AND IT.itRuKey = PE.paRuKey
>			JOIN GlSub ON GlSub.gsKey = IT.itGsKey
>			WHERE RU.ruYear = 2015
>			  AND MONTH(RU.ruStartDat) IN (7,8,9)
>			  AND GLSub.gsAccount = '4101'
>			GROUP BY IT.itGsKey, PE.paDeKey) as JulSep
>	ON PayEmps.paDeKey = JulSep.paDeKey
>
>	LEFT JOIN GLSub
>	ON Total.itGsKey = GLSub.gsKey
>	AND JanMrt.itGsKey = GLSub.gsKey
>	AND AprJun.itGsKey = GLSub.gsKey
>	AND JulSep.itGsKey = GLSub.gsKey
>	
>	WHERE Runs.ruYear = 2015
>		  AND GLSub.gsAccount = '4101'
>	GROUP BY Departments.deName, GLSub.gsAccount, 
>		Total.itAmount, JanMrt.itAmount, AprJun.itAmount, JulSep.itAmount
>	ORDER BY Departments.deName, GLSub.gsAccount
>