I there a better way to write this query? I feel sure it can be done in one statement with joins, but I couldn't get it, and I'm afraid I've made it too slow. What suggestions do you have? I'm running it in SQL Server 2000 (yeah, I know, upgrade is coming).
Basically, I want to match estimated and actual costs, but sometimes the estimate is done on 1 cost center, and the actual costs are set to another cost center (hence the possibility of having null in est or act. I want to get all possible combinations for that job.
ALTER view [dbo].[JobCost_EstVsAct] --SELECT * FROM JobCost_EstVsAct Where jobnumber = '122773' as SELECT JobNumber, CostCenter, sum(Amount) as Est, sum(cost) as Act FROM ( SELECT JobNumber, CostCenter, Amount, 0 as cost FROM Avanti_ActiveJobBudgets AJB UNION SELECT jobnumber, costcentre as CostCenter, 0 as Est, cost as Act FROM Avanti_ActiveCostDetails ) temp --where cost + Amount > 0 This line is a bug GROUP by JobNumber, CostCenter HAVING sum(Amount) + sum(cost) > 0 --bug correction