FULL OUTER JOIN duplicating rows using COALESCE

The problem with your query isn't the use of COALESCE, but simply with the JOIN. There are 2 rows in the Forecast table that have the same combination of Model, Month, Country, rows with ID 2 and 3:

╔════╦═══════╦═════════════════════════╦═════════╦═══════╗
║ ID ║ Model ║          Month          ║ Country ║ Count ║
╠════╬═══════╬═════════════════════════╬═════════╬═══════╣
║  2 ║ ABC   ║ 2014-02-01 00:00:00.000 ║ Germany ║  1100 ║
║  3 ║ ABC   ║ 2014-02-01 00:00:00.000 ║ Germany ║   900 ║
╚════╩═══════╩═════════════════════════╩═════════╩═══════╝

Both of them join with the row ID 3 from the Sales table:

╔════╦═══════╦═════════════════════════╦═════════╦═══════╗
║ ID ║ Model ║          Month          ║ Country ║ Count ║
╠════╬═══════╬═════════════════════════╬═════════╬═══════╣
║  3 ║ ABC   ║ 2014-02-01 00:00:00.000 ║ Germany ║   900 ║
╚════╩═══════╩═════════════════════════╩═════════╩═══════╝

And since your query is using COALESCE(s.ID, fc.ID), then you get 2 rows with ID 3 in the results


Lamak's answer provides the reason for the duplicate rows in the result. Here is one solution:

WITH Sales AS
( ... )
, Forecasts AS
( ...)
, Combos AS                             -- get all distinct
(                                       -- model + month + country  
   SELECT Model, Month, Country         -- combinations
   FROM Sales                           -- from Sales
 UNION                                             -- this is UNION DISTINCT
   SELECT Model, Month, Country
   FROM Forecasts                       -- and Forecasts
)
SELECT  ID = COALESCE(s.ID, f.ID), 
        c.Model, 
        c.Month,
        c.Country,
        Amount = COALESCE(s.Amount, f.Amount),
        [Forecast / Sales] = COALESCE(s.[Forecast / Sales], 
                                      f.[Forecast / Sales])
FROM Combos c
  LEFT JOIN Sales s
    ON  s.Model = c.Model
    AND s.Month = c.Month
    AND s.Country = c.Country
  LEFT JOIN Forecasts f 
    ON  s.Model IS NULL           -- join Forecasts only if there is no Sales
    AND f.Model = c.Model
    AND f.Month = c.Month
    AND f.Country = c.Country
ORDER BY ID, Month, Country, Model ;

Test at: SQL-Fiddle


It appears you simply want to return the entire Sales set and complement it with entries from Forecasts that are not found in Sales. For that, I would probably just use UNION ALL like this:

WITH Sales AS
(
  ...
)
, Forecasts AS
(
  ...
)

SELECT ID, Model, Month, Country, Amount, [Forecast / Sales]
FROM Sales

UNION ALL

SELECT ID, Model, Month, Country, Amount, [Forecast / Sales]
FROM Forecasts
WHERE NOT EXISTS
(
  SELECT Model, Month, Country
  INTERSECT
  SELECT Model, Month, Country
  FROM Sales
);