SQL view, better way? 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 &gt; 0     This line is a bug
GROUP by JobNumber, CostCenter
HAVING sum(Amount) + sum(cost) &gt; 0   --bug correction
First off, it's not a stored procedure, it's a view.

That said, the main change I would make is to use a CTE, once you have upgraded.

ALTER view [dbo].[JobCost_EstVsAct]
    --SELECT * FROM JobCost_EstVsAct Where jobnumber = '122773'
AS
WITH cte1 as (
    SELECT JobNumber, CostCenter, Amount, 0 as cost
    FROM Avanti_ActiveJobBudgets AJB
),
cte2 as (
    SELECT jobnumber, costcentre as CostCenter, 0 as Amount, cost as Act
    FROM Avanti_ActiveCostDetails
),
cte3 as (
    Select * From cte1
    Union
    Select * From cte2
)
SELECT JobNumber, CostCenter, sum(Amount) as Est, sum(cost) as Act
FROM cte3
--where cost + Amount &gt; 0     /* This line is a bug */
GROUP by JobNumber, CostCenter
HAVING sum(Amount) + sum(cost) &gt; 0   --bug correction


Replacing the cteX with more appropriate names.