SQL

Question

J'écris une requête pour résumer les ventes par mois. Mon problème est que ma requête ne renvoie que des enregistrements pendant des mois avec des ventes. Par exemple, je regarde sur une période de 15 mois. Mais pour une partie spécifique de l'exemple ci-dessous, seuls 3 des 15 mois avaient des ventes.

J'espère avoir 15 disques et les autres ont des 0 pour les ventes. La raison pour laquelle j'espère des enregistrements supplémentaires est que je veux prendre l'écart type de cela, et la suppression des enregistrements a un impact sur ce calcul.

Exemple de code:

SELECT I.PartNumber as PartNumber,
YEAR(O.CreateDate) as CreateDateYear,
MONTH(O.CreateDate) as CreateDateMonth,
COUNT(*) as TotalDemand
FROM OrderDetails OD
INNER JOIN Orders O on O.Id = OD.OrderId
INNER JOIN Items I on I.Id = OD.ItemId
WHERE 
O.CreateDate >= '1-1-2016' 
AND O.CreateDate <= '3-31-2017'
AND I.PartNumber = '5144831-2'
GROUP BY I.PartNumber, YEAR(O.CreateDate) , MONTH(O.CreateDate);

Exemple de sortie de courant:

Part # | Year | Month | Demand
5144831-2   2017    1   1
5144831-2   2017    2   3
5144831-2   2016    3   1

Sortie souhaitée:

Je voudrais une ligne supplémentaire telle que:

5144831-2   2016    11  0

Pour montrer qu'il n'y a eu aucune vente en novembre 2016.

J'ai une table temporaire # _date_array2 avec les mois / années possibles, je pense que j'ai besoin d'aide pour intégrer une jointure à gauche.

sql sql-server counting

1

Zak Fischer 30 nov. 2017 à 01:22

Answer 1

Si vous souhaitez utiliser la jointure gauche, vous ne pourrez pas l'utiliser directement avec la jointure interne. Vous pouvez faire la jointure interne à l'intérieur de la parenthèse, puis effectuer la jointure gauche à l'extérieur pour éviter de jouer avec les résultats de la jointure gauche. Essaye ça:

SELECT Z.PartNumber as PartNumber,
YEAR(O.CreateDate) as CreateDateYear,
MONTH(O.CreateDate) as CreateDateMonth,
COUNT(Z.OrderId) as TotalDemand
FROM Orders O 
LEFT JOIN 
(
    SELECT OrderId, PartNumber
    FROM
    OrderDetails OD 
    INNER JOIN Items I ON I.Id = OD.ItemId
    AND I.PartNumber = '5144831-2'
) Z
ON O.Id = Z.OrderId 
AND O.CreateDate >= '1-1-2016' 
AND O.CreateDate <= '3-31-2017'
GROUP BY Z.PartNumber, YEAR(O.CreateDate) , MONTH(O.CreateDate);

Pour obtenir un décompte de 0 pour les mois sans commande, évitez d'utiliser count (*) et utilisez count (OrderId) comme indiqué ci-dessus.

Remarque - Vous devrez vous assurer que la table Orders a tous les mois et toutes les années disponibles, c'est-à-dire s'il n'y a pas de valeur CreateDate de, disons, novembre 2016 dans la table Orders (table de gauche dans la jointure), la sortie ne produira pas non plus l'entrée de ce mois.

Modifier: Pouvez-vous essayer ceci:

SELECT Z.PartNumber as PartNumber,
YEAR(O.CreateDate) as CreateDateYear,
MONTH(O.CreateDate) as CreateDateMonth,
COUNT(O.OrderId) as TotalDemand
FROM Orders O 
RIGHT JOIN 
(
    SELECT OrderId, PartNumber
    FROM
    OrderDetails OD 
    INNER JOIN Items I ON I.Id = OD.ItemId
    AND I.PartNumber = '5144831-2'
) Z
ON O.Id = Z.OrderId 
AND O.CreateDate >= '1-1-2016' 
AND O.CreateDate <= '3-31-2017'
GROUP BY Z.PartNumber, YEAR(O.CreateDate) , MONTH(O.CreateDate);

1

Vash 30 nov. 2017 à 01:42

Answer 2

Sur la base de tous vos commentaires sur d'autres articles, etc., il semble que vous ayez une table qui a une plage de dates que vous voulez et que vous voulez pouvoir exécuter l'analyse pour plusieurs / tous les numéros de pièce. Donc, le principal problème est que vous aurez besoin d'une jointure cartésienne entre votre table de dates et les références qui ont été vendues pendant cette période afin de réaliser des «0» lorsqu'ils ne sont pas vendus.

;WITH cteMaxMinDates AS (
    SELECT
       MinDate = MIN(DATEFROMPARTS(CreateDateYear,CreateDateMonth,1))
       ,MaxDate = MAX(DATEFROMPARTS(CreateDateYear,CreateDateMonth,1))
    FROM
       #_date_array2
)

;WITH cteOrderDetails AS (
    SELECT
       d.CreateDateYear
       ,d.CreateDateMonth
       ,I.PartNumber
    FROM
       #_date_array2 d
       INNER JOIN Orders o
       ON d.CreateDateMonth = MONTH(o.CreateDate)
       AND d.CreateDateYear = YEAR(o.CreateDate)
       INNER JOIN OrderDetails od
       ON o.Id = od.OrderId
       INNER JOIN Items i
       ON od.ItemId = i.Id
       AND i.PartNumber = '5144831-2'
)

, cteDistinctParts AS (
    SELECT DISTINCT PartNumber
    FROM
       cteOrderDetails
)

SELECT
    d.CreateDateYear
    ,d.CreateDateMonth
    ,I.PartNumber
    ,COUNT(od.PartNumber) as TotalDemand
FROM
    #_date_array2 d
    CROSS JOIN cteDistinctParts p
    LEFT JOIN cteOrderDetails od
    ON d.CreateDateYear = od.CreateDateYear
    AND d.CreateDateMonth = od.CreateDateMonth
    AND p.PartNumber = od.PartNumber
GROUP BY
    d.CreateDateYear
    ,d.CreateDateMonth
    ,I.PartNumber

Pour obtenir TOUS les numéros de pièce, supprimez simplement la condition de jointure AND i.PartNumber = '5144831-2'.

0

Matt 29 nov. 2017 à 22:54

Answer 3

En supposant que vous ayez des ventes de quelque chose chaque mois, la solution la plus simple consiste à passer à l'agrégation conditionnelle:

SELECT '5144831-2' as PartNumber,
       YEAR(O.CreateDate) as CreateDateYear,
       MONTH(O.CreateDate) as CreateDateMonth,
       SUM(CASE WHEN I.PartNumber = '5144831-2' THEN 1 ELSE 0 END) as TotalDemand
FROM OrderDetails OD INNER JOIN
     Orders O 
     ON O.Id = OD.OrderId INNER JOIN
     Items I 
     ON I.Id = OD.ItemId
WHERE O.CreateDate >= '2016-01-01' AND
      O.CreateDate <= '2017-03-31'
GROUP BY YEAR(O.CreateDate) , MONTH(O.CreateDate);

Remarque: il s'agit en quelque sorte d'un hack pour résoudre le problème. Des solutions plus robustes impliquent de générer les dates et d'utiliser LEFT JOIN (ou une fonctionnalité similaire). Cependant, c'est souvent le moyen le plus rapide d'obtenir le résultat.

1

Gordon Linoff 29 nov. 2017 à 22:27

SQL - Suivi des ventes mensuelles

3 réponses

De nouvelles questions