[RESOLVED] Translate this MySQL query to MS SQL

[ixalmida]

I have 2 views I need to join in MS SQL:

1. Total Estimated Costs per cost center

2. Total Actual Costs per cost center

This is more of a UNION/JOIN, with the estimated/actual costs filling in ZEROES where there is no record to join in the other table.

So for example:

Job Number    Job Code(etc)       Est Cost    Actual Cost
101FW9091     11011                4967.00       5125.97
101FW9091     11012                      0         75.00
101FW9091     11013                 985.00             0
101FW9091     11014                 687.00        595.25
etc...

In the above sample output, the Job Code "11012" has no related value in the estimated table, so it fills in a 0 and the job code "11013" has no related value in the actual table, so it fills in a 0. Note that these tables are joining on multiple columns, any of which may exclude it from the joined output.

I know how to write this query in MySQL, but I'm stumped on how to do it in t-SQL.

Here's how I'd do the query in MySQL:

SELECT
     IF(Actual_Cost IS NOT NULL, Actual_Cost, 0) Actual_Cost,
     IF(Est_Cost IS NOT NULL, Est_Cost, 0) Est_Cost,
     Job_Number,
     Bseg,
     Cost_Type,
     Phase_Code,
     Job_Type,
     Project_Manager
FROM
     (SELECT * FROM Est_Costs) UNION (SELECT * FROM Actual_Costs)
GROUP BY
     Job_Number, Bseg, Cost_Type, Phase_Code, Job_Type, Project_Manager

Any thoughts on a MS SQL equivalent?

[ixalmida]

Come to think of it, the above MySQL query wouldn't work because the UNION isn't equal, but hopefully you get the idea.

[ixalmida]

I believe the solution for with this was to move the UNION out of the FROM statement. Just make sure your column list is identical in the two queries (note: a trick here is to rename columns - the data type must still match).

SELECT...FROM...GROUP BY
UNION ALL
SELECT...FROM...GROUP BY