Comment obtenir une jointure de table Sql Temp avec des valeurs de colonne qui ne sont pas dans la table, remplacées par 0

Personnellement, je pense que vous devriez suivre la suggestion de @ influent: dérivez un modèle de tableau sur lequel vous pouvez laisser les valeurs que vous recherchez.

Dans l'éventualité où vous ne disposez pas de la logique ou des données requises pour dériver avec précision un tel modèle de table, il existe une autre option.
1. Remplissez chaque table avec des valeurs factices 0, afin qu'elles aient toutes les mêmes champs
2. UNION toutes les tables ensemble
3. GROUP tous les résultats reviennent à une ligne par mois et par produit

WITH
    padded_combined
AS
(

    SELECT month_year, prod_id, job_amt, spoil, good, 0 AS received_qty, 0 AS purchase_qty, 0 AS opening_balance, 0 AS closing_balance FROM #tmp_spoilt_good_job_amt
    UNION ALL
    SELECT month_year, prod_id, 0, 0, 0, received_qty, 0, 0, 0 FROM #tmp_received_qty
    UNION ALL
    SELECT month_year, prod_id, 0, 0, 0, 0, purchase_qty, 0, 0 FROM #tmp_purchase_qty
    UNION ALL
    SELECT month_year, prod_id, 0, 0, 0, 0, 0, opening_balance, 0 FROM #tmp_opening_balance
    UNION ALL
    SELECT month_year, prod_id, 0, 0, 0, 0, 0, 0, closing_balance FROM #tmp_closing_balance
)
SELECT
    month_year,
    prod_id,
    SUM(job_amt)            AS job_amt,
    SUM(spoil)              AS spoil,
    SUM(good)               AS good,
    SUM(received_qty)       AS received_qty,
    SUM(purchase_qty)       AS purchase_qty,
    SUM(opening_balance)    AS opening_balance,
    SUM(closing_balance)    AS closing_balance
FROM
    padded_combined
GROUP BY
    month_year,
    prod_id
ORDER BY
    month_year,
    prod_id

CREATE TABLE #table_with_all_months_prod_ids_using_cross_join (month_year, prod_id) 
GO

INSERT #table_with_all_months_prod_ids_using_cross_join 
SELECT t1.month_year, t2.prod_id 
FROM monthyeartable t1 
CROSS JOIN prodidtable t2

SELECT DISTINCT t0.month_year, t0.prod_id, ISNULL(t1.job_amt,0), ISNULL(t1.spoil,0), ISNULL(t1.good,0), ISNULL(t2.received_qty,0), ISNULL(t3.purchase_qty,0), ISNULL(t4.opening_balance,0), ISNULL(t5.closing_balance,0)     
        FROM #table_with_all_months_prod_ids_using_cross_join t0
        LEFT JOIN #tmp_spoilt_good_job_amt t1 ON t0.month_year = t1.month_year AND t0.prod_id = t1.prod_id
        LEFT JOIN #tmp_received_qty t2 ON t0.month_year = t2.month_year AND t0.prod_id = t2.prod_id
        LEFT JOIN #tmp_purchase_qty t3 ON t0.month_year = t3.month_year AND t0.prod_id = t3.prod_id
        LEFT JOIN #tmp_opening_balance t4 ON t0.month_year = t4.month_year AND t0.prod_id = t4.prod_id
        LEFT JOIN #tmp_closing_balance t5 ON t0.month_year = t5.month_year AND t0.prod_id = t5.prod_id 
WHERE NOT (t1.job_amt IS NULL AND t1.spoil IS NULL AND t1.good IS NULL AND t2.received_qty IS NULL AND t3.purchase_qty IS NULL AND t4.opening_balance IS NULL AND t5.closing_balance IS NULL) 

Premièrement, si vous avez ces 5 tables temporaires, cela signifie probablement qu'il existe une bien meilleure façon de le faire au niveau de la table source! Mais parce que vous vous posez le problème le plus important en les combinant, aucune des tables ne contient une combinaison de tous les month_year et prod_id. Vous devez donc le créer. La façon dont je choisis de le faire pour être complet est de:

1. Créer une table Tally (en tant qu'expression de table commune [CTE]) à utiliser pour générer une table month_year
2. Créez un CTE de produits en réunissant tous les prod_ids distincts de vos tables temporaires
3. Créez un CTE MonthYearInputs pour être en mesure de déterminer les mois_years Max et Min représentés
4. Générez un CTE MonthYear pour héberger chaque combinaison mois_an possible entre les années MIN et MAX représentées dans vos données
5. Ensuite, une jointure cartésienne (CROSS) entre les ctes Mois Année et Produit vous donne toutes les combinaisons avec LEFT JOIN les autres tables.
6. Mettez simplement une instruction where pour supprimer les lignes qui n'ont aucune valeur dans TOUTES les tables et utilisez ISNULL() ou COALESCE() pour rendre les valeurs nulles 0.

Voici un exemple de travail: http://rextester.com/MCEO96178

;WITH cteTen AS (
    SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n)
)

, cteTally AS (
    SELECT
       n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM
       cteTen t1
       CROSS JOIN cteTen t2 --hundreds
       CROSS JOIN cteTen t3 --thousands
       --keep cross joining if need more than 1000 month
)

, cteProducts AS (
    SELECT DISTINCT prod_id FROM #tmp_spoilt_good_job_amt
    UNION
    SELECT DISTINCT prod_id FROM #tmp_received_qty
    UNION
    SELECT DISTINCT prod_id FROM #tmp_purchase_qty
    UNION
    SELECT DISTINCT prod_id FROM #tmp_opening_balance
    UNION
    SELECT DISTINCT prod_id FROM #tmp_closing_balance
)

, cteInputMonthYears AS (
    SELECT DISTINCT month_year FROM #tmp_spoilt_good_job_amt
    UNION
    SELECT DISTINCT month_year FROM #tmp_received_qty
    UNION
    SELECT DISTINCT month_year FROM #tmp_purchase_qty
    UNION
    SELECT DISTINCT month_year FROM #tmp_opening_balance
    UNION
    SELECT DISTINCT month_year FROM #tmp_closing_balance
)

, cteMaxMinMonthYears AS (
    SELECT
       MinMonthYear =  CAST(STUFF(MIN(month_year),3,0,'-01') AS DATETIME)
       ,MonthsDiff = DATEDIFF(MONTH,CAST(STUFF(MIN(month_year),3,0,'-01') AS DATETIME),CAST(STUFF(MAX(month_year),3,0,'-01') AS DATETIME)) + 1
    FROM
       cteInputMonthYears
)

, cteMonthYears AS (
    SELECT
       month_year = FORMAT(DATEADD(MONTH, t.n - 1, m.MinMonthYear),'MM-yyyy')
    FROM
       cteMaxMinMonthYears m
       INNER JOIN cteTally t
       ON m.MonthsDiff >= t.n
)

SELECT
    my.month_year
    ,job_amt = ISNULL(ja.job_amt,0)
    ,spoil = ISNULL(ja.spoil,0)
    ,good = ISNULL(ja.good,0)
    ,p.prod_id
    ,received_qty = ISNULL(r.received_qty,0)
    ,purchase_qty = ISNULL(pur.purchase_qty,0)
    ,opening_balance = ISNULL(o.opening_balance,0)
    ,closing_balance = ISNULL(c.closing_balance,0)
FROM
    cteMonthYears my
    CROSS JOIN cteProducts p
    LEFT JOIN #tmp_spoilt_good_job_amt ja
    ON my.month_year = ja.month_year
    AND p.prod_id = ja.prod_id
    LEFT JOIN #tmp_received_qty r
    ON my.month_year = r.month_year
    AND p.prod_id = r.prod_id
    LEFT JOIN #tmp_purchase_qty pur
    ON my.month_year = pur.month_year
    AND p.prod_id = pur.prod_id
    LEFT JOIN #tmp_opening_balance o
    ON my.month_year = o.month_year
    AND p.prod_id = o.prod_id
    LEFT JOIN #tmp_closing_balance c
    ON my.month_year = c.month_year
    AND p.prod_id = c.prod_id
WHERE
    NOT(ja.month_year IS NULL 
       AND r.month_year IS NULL
       AND pur.month_year IS NULL
       AND o.month_year IS NULL
       AND o.month_year IS NULL
       AND c.month_year IS NULL)
ORDER BY
    my.month_year
    ,p.prod_id