How to get Sql Temp Table Join with column values that are not in table, replaced by 0

Question

I have 5 temp tables that I want to combine into a new temp table via the month_year column and the prod_id. Where the columns are not prod_id or month_year and values are not available, I want that column's value(e.g. job_amt or received_qty) to be 0.

#tmp_spoilt_good_job_amt
month_year  job_amt     spoil   good    prod_id
07-2017         40      10        20    2
08-2017         827     0         210   3
09-2017         27      1         27    2
09-2017         732     22        345   3
10-2017         50      0         6     2
10-2017         1130    55        50    3
11-2017         300     0         0     4

#tmp_received_qty
month_year  received_qty    prod_id
 08-2017    32                 2
 08-2017    2500               3
 09-2017    2200               2
 11-2017    2500               4

#tmp_purchase_qty
month_year  purchase_qty    prod_id
 09-2017    11                 2

#tmp_opening_balance 
month_year  opening_balance   prod_id
08-2017              32         2
08-2017             2500        3
09-2017               22        2
09-2017             2300        3
10-2017             2163        2
10-2017             2023        3
11-2017             2500        4

#tmp_closing_balance
month_year  closing_balance     prod_id
08-2017         2300                3
08-2017         32                  2
09-2017         2213                2
09-2017         1998                3
10-2017         1687                3
10-2017         2163                2
11-2017         2400                4

I tried some inner joins but the values were repeating or some were not reflecting. what query could I use to get these combined?

I am looking for the following output:

Answer 1

Personally, I think you should go with @influent's suggest: derive a template table on to which you can left the values you're looking for.

In the eventuality that you don't have the required logic or data to accurately derive such a template table, there is another option.
1. Pad out each table with dummy 0 values, so that they all have the same fields
2. UNION all the tables together
3. GROUP all the results back down to one row per month per product

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

Answer 2

First if you have these 5 temp tables that probably means there is a much much much better way of doing this at the source table level! But because you asked you most significant problem with combining them is that not 1 of the tables hold a combination of every month_year and prod_id. So you have to create it. The way I choose to do this for completeness sake is to:

1. Create a Tally table (as a common table expression [CTE]) for use in generating a month_year table
2. Create a Products CTE by unioning all distinct prod_ids from your temp tables
3. Create a MonthYearInputs CTE to be able to determine the Max and Min month_years represented
4. Generate a MonthYear CTE to house every possible month_year combination between the MIN & MAX years represented in your data
5. Then a cartisean (CROSS) join between the MonthYear & Product ctes give you all of the combinations to LEFT JOIN the other tables to.
6. Simply put a where statement in to remove the rows that have no values in ALL of the tables and use ISNULL() or COALESCE() to make the null values 0.

Here is a working example: 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

Answer 3

So, this kind of relation ships have very week performance, first of all it is better to change the tables structure and all other thing that related to fill data in you database.

Any way, this query created for your current needs with current data structure, also if you work on this query or divided it in to some views maybe you can improve performance:

SELECT CASE WHEN ISNULL(srpo.month_year , '-1') <> '-1' THEN srpo.month_year 
            WHEN ISNULL(c.month_year , '-1') <> '-1' THEN c.month_year 
       END AS month_year,
       CASE WHEN ISNULL(srpo.prod_id, -1) <> -1 THEN srpo.prod_id 
            WHEN ISNULL (c.prod_id, -1) <> -1 THEN c.prod_id 
       END AS prod_id, 
       CASE WHEN ISNULL(c.closing_balance, -1) = -1 THEN 0 else c.closing_balance END AS closing_balance,
       srpo.job_amt,srpo.spoil,srpo.good ,srpo.received_qty,srpo.purchase_qty, srpo.opening_balance
FROM   #tmp_closing_balance AS c FULL OUTER JOIN

(SELECT CASE WHEN ISNULL(srp.month_year , '-1') <> '-1' THEN srp.month_year 
            WHEN ISNULL(o.month_year , '-1') <> '-1' THEN o.month_year 
       END AS month_year,
       CASE WHEN ISNULL(srp.prod_id, -1) <> -1 THEN srp.prod_id 
            WHEN ISNULL (o.prod_id, -1) <> -1 THEN o.prod_id 
       END AS prod_id, 
       CASE WHEN ISNULL(o.opening_balance, -1) = -1 THEN 0 else o.opening_balance END AS opening_balance,
       srp.job_amt,srp.spoil,srp.good ,srp.received_qty,srp.purchase_qty
FROM   #tmp_opening_balance AS o FULL OUTER JOIN

(SELECT CASE WHEN ISNULL(sr.month_year , '-1') <> '-1' THEN sr.month_year 
            WHEN ISNULL(p.month_year , '-1') <> '-1' THEN p.month_year 
       END AS month_year,
       CASE WHEN ISNULL(sr.prod_id, -1) <> -1 THEN sr.prod_id 
            WHEN ISNULL (p.prod_id, -1) <> -1 THEN p.prod_id 
       END AS prod_id, 
       CASE WHEN ISNULL(p.purchase_qty, -1) = -1 THEN 0 else p.purchase_qty END AS purchase_qty,
       sr.job_amt,sr.spoil,sr.good ,sr.received_qty 
FROM #tmp_purchase_qty AS p FULL OUTER JOIN 
     (SELECT CASE WHEN ISNULL(s.month_year , '-1') <> '-1' THEN s.month_year 
            WHEN ISNULL(r.month_year , '-1') <> '-1' THEN r.month_year 
       END AS month_year,
       CASE WHEN ISNULL(s.prod_id, -1) <> -1 THEN s.prod_id 
            WHEN ISNULL (r.prod_id, -1) <> -1 THEN r.prod_id 
       END AS prod_id,
       CASE WHEN ISNULL(s.job_amt, -1) = -1 THEN 0 else s.job_amt END AS job_amt,
       CASE WHEN ISNULL(s.spoil, -1) = -1 THEN 0 else s.spoil END AS spoil,
       CASE WHEN ISNULL(s.good, -1) = -1 THEN 0 else s.good END AS good,
       CASE WHEN ISNULL(r.received_qty, -1) = -1 THEN 0 else r.received_qty END AS received_qty
FROM #tmp_spoilt_good_job_amt AS s FULL OUTER JOIN  
     #tmp_received_qty AS r ON s.prod_id = r.prod_id AND
                              LTRIM(rtrim(s.month_year)) = LTRIM(rtrim(r.month_year))) AS sr ON
     sr.prod_id = p.prod_id AND LTRIM(rtrim(sr.month_year)) = LTRIM(rtrim(p.month_year)) ) AS srp ON
     srp.prod_id = o.prod_id AND LTRIM(rtrim(srp.month_year)) = LTRIM(rtrim(o.month_year))) AS srpo ON
     srpo.prod_id = c.prod_id AND LTRIM(rtrim(srpo.month_year)) = LTRIM(rtrim(c.month_year))

I use FULL OUTER JOIN for all part of query because you mentioned that, may be all tables have possible values for keys columns(month_year and prod_id).

Answer 4

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)