J'ai 5 tables temporaires que je veux combiner dans une nouvelle table temporaire via la colonne month_year et le prod_id. Lorsque les colonnes ne sont pas prod_id ou month_year et que les valeurs ne sont pas disponibles, je veux que la valeur de cette colonne (par exemple, job_amt ou receiver_qty) soit 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

J'ai essayé des jointures internes mais les valeurs se répètent ou certaines ne reflètent pas. quelle requête pourrais-je utiliser pour les combiner?

Je recherche la sortie suivante: entrez la description de l'image ici

0
tks.tman 15 nov. 2017 à 23:37

4 réponses

Meilleure réponse

Donc, ce type de relation a des performances très hebdomadaires, tout d'abord il est préférable de changer la structure des tables et toutes les autres choses liées au remplissage des données dans votre base de données.

Quoi qu'il en soit, ce query a été créé pour vos besoins actuels avec la structure de données actuelle, même si vous travaillez dessus query ou si vous le divisez en views, vous pouvez peut-être améliorer les performances:

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))

J'utilise FULL OUTER JOIN pour toute la partie de query parce que vous l'avez mentionné, peut-être que toutes les tables ont des valeurs possibles pour les colonnes de clés (month_year et prod_id).

1
Aqil 16 nov. 2017 à 00:54

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
1
MatBailie 16 nov. 2017 à 03:42
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) 
2
influent 15 nov. 2017 à 23:55

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
1
Matt 16 nov. 2017 à 02:43
47316802