FULL OUTER JOIN copying strings using COALESCE

Question

It was a long day, maybe it's a simple question, but I'm stuck anyway.

I basically have two identical tables Salesand Forecasts. I am trying to create a view that selects rows from both tables and selects everything there is for this model + month + country. If both tables contain data, it Salestakes precedence, which means that rows Forecastshould be omitted.

To simplify the request, I use CTE. In fact, the layout of both tables is different, and many tables are combined, and also Forecastscontains history lines in which only the last should be displayed.

I created a simplified diagram and data to show you what I'm trying to do:

WITH Sales AS
(
    SELECT 
        ID, Model, Month, Country,
        Amount              = Count,
        [Forecast / Sales]  = 'Sales'
    FROM dbo.Sales
)
, Forecasts AS
(
    SELECT 
        ID, Model, Month, Country,
        Amount              = Count,
        [Forecast / Sales]  = 'Forecast'
    FROM dbo.Forecast
)
SELECT  ID = COALESCE(s.ID, fc.ID), 
        Model = COALESCE(s.Model, fc.Model), 
        Month = COALESCE(s.Month, fc.Month),
        Country = COALESCE(s.Country, fc.Country),
        Amount = COALESCE(s.Amount, fc.Amount),
        [Forecast / Sales] = COALESCE(s.[Forecast / Sales], fc.[Forecast / Sales])
FROM Sales s
FULL OUTER  JOIN Forecasts fc 
    ON s.Model = fc.Model
        AND s.Month = fc.Month
        AND s.Country = fc.Country
ORDER BY ID,Month,Country,Model

sql- : http://sqlfiddle.com/#!3/9081b/9/2

:

ID  MODEL   MONTH   COUNTRY AMOUNT  FORECAST / SALES
1   ABC December, 01 2013 00:00:00+0000 Germany 777 Sales
2   ABC January, 01 2014 00:00:00+0000  Germany 999 Sales
3   ABC February, 01 2014 00:00:00+0000 Germany 900 Sales
3   ABC February, 01 2014 00:00:00+0000 Germany 900 Sales
4   ABC January, 01 2014 00:00:00+0000  UK  600 Forecast
4   ABC February, 01 2014 00:00:00+0000 UK  444 Sales
5   ABC March, 01 2014 00:00:00+0000    UK  500 Forecast

ID ( ).

3   ABC February, 01 2014 00:00:00+0000 Germany 900 Sales
3   ABC February, 01 2014 00:00:00+0000 Germany 900 Sales

-, Sales Forecast -rows + + . Sales, Sales + Forecast Forecast , Sales?

Answer 1

Lamak . :

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 ;

: SQL-Fiddle

Answer 2

COALESCE, JOIN. Forecast , Model, Month, Country, ID 2 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 ║
╚════╩═══════╩═════════════════════════╩═════════╩═══════╝

ID 3 Sales:

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

COALESCE(s.ID, fc.ID), 2 ID 3

Answer 3

It seems you just want to return the whole set Salesand supplement it with entries from Forecaststhat are not found in Sales. For this, I would 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
);