SELECT
X.cWorkorder AS Work_Order_Number,
SUM(X.Installed) AS Installed,
SUM(X.Credit_Memo) AS Credit_Memo,
SUM(X.EPP_CM) AS EPP_CM
FROM (
SELECT
dbo.WOR.cWorkOrder,
ISNULL(dbo.Installs.cost, 0) AS Installed,
0 AS Credit_Memo,
0 AS EPP_CM
FROM
dbo.WOR
INNER JOIN dbo.Installs ON dbo.Installs.cWorkOrder = dbo.WOR.cWorkOrder
WHERE
dbo.WOR.Completed >= '9/1/2008'
AND
dbo.WOR.Completed < '9/2/2008'
UNION ALL
SELECT
dbo.WOR.cWorkOrder,
0 AS Installed,
ISNULL(dbo.cfrecon.pmt, 0) As Credit_Memo,
0 AS EPP_CM
FROM
dbo.WOR
INNER JOIN dbo.cfrecon ON dbo.cfrecon.cWorkOrder = dbo.WOR.cWorkOrder
WHERE
dbo.WOR.Completed >= '9/1/2008'
AND
dbo.WOR.Completed < '9/2/2008'
UNION ALL
SELECT
dbo.WOR.cWorkOrder,
0 AS Installed,
0 AS Credit_Memo,
ISNULL(dbo.payments.nPayment, 0) As EPP_CM
FROM
dbo.WOR
INNER JOIN dbo.payments ON dbo.payments.cWorkOrder = dbo.WOR.cWorkOrder
WHERE
dbo.payments.PmtMethod = 'CM'
AND
dbo.WOR.Completed >= '9/1/2008'
AND
dbo.WOR.Completed < '9/2/2008') X
GROUP BY
X.cWorkOrder
ORDER BY
1, 2, 3, 4