Le pivotement d'un domaine en fonction de la plage de dates dans la table B, qui est >= de la plage de dates dans le tableau A

Question 1

Je suis en train de faire pivoter une ligne dans les colonnes qui sont basées sur des dates mises en forme "aaaa-MMM" et tout cela fonctionne comme prévu pour une période de douze mois, il n'a pas de travail pour les mois à venir et je ne peux pas comprendre comment résoudre ce problème et vous serions reconnaissants de tout commentaire.

Voici un peu de contexte: J'ai acheté la quantité venant d'un Vendeur résumé de la table qui contient des informations sur un contrat comme un contrat type, la quantité, le numéro de lot, la date de création formaté en "aaaa-MMM", comme la date réelle n'a pas d'importance pour ce but.

Quantité restante est un autre domaine à venir à partir d'un élément de comptabilité d'entrée de la table qui est essentiellement résumant toutes sortes d'entrées en fonction de son numéro de lot.

Consommé la Quantité est ce que je suis en train de pivot basé sur le mois, il a été consommé et est également à venir à partir de l'élément écritures avec une entrée de type de filtre

SELECT * 
FROM 
(
SELECT VS.[Vendor Name], VS.[Vendor No_] AS 'Vendor_No', VS.[Date] AS 'Date', 
SUM(VS.Quantity) AS 'Contracted_Quantity',
SUM(CQ.Consumed_Qty*-1) AS 'Consumed_Qty',
SUM(RQ.Remaining_Qty) AS Remaining_Qty,
'Contract Type' = 
CASE 
  WHEN VS.[Contract Type] = 1 THEN 'CONTRACT A'
  WHEN VS.[Contract Type] = 2 THEN 'CONTRACT B'
  ELSE 'OTHERS'
  END 
FROM
(SELECT [Document No_],[Vendor No_],[Lot No_],FORMAT([Date Created], 'yyyy-MMM') AS 'Date'
      ,[Purch_ Contract No_],[Contract Type],[Quantity] FROM [A].[dbo].[Company$Volume Summary]) VS

/*to identify remaining quantity by lot (Lot no. is included in my VS alias statement)*/
LEFT JOIN (SELECT [Lot No_] ,SUM([Remaining Quantity]) AS Remaining_Qty FROM [A].[dbo].[Company$Item Ledger Entry]
  GROUP BY [Lot No_]) RQ on RQ.[Lot No_] = VS.[Lot No_]

/*to identify consumed volume, if consumption is in future month compared to purchase month, it doesn't populate, this is where I believe the problem is*/
LEFT JOIN (SELECT [Lot No_],FORMAT([Posting Date], 'yyyy-MMM') AS 'Date',SUM([Quantity]) AS Consumed_Qty
FROM [A].[dbo].[Company$Item Ledger Entry]
WHERE [Entry Type] = '5' 
GROUP BY [Lot No_], Format([Posting Date], 'yyyy-MMM')) CQ on CQ.[Lot No_] = VS.[Lot No_] and CQ.Date >= VS.[Date]

GROUP BY VS.[Vendor Name], FORMAT(VS.[Date Created],'yyyy-MMM'), PT.[Contract Type], VS.[Vendor No_]
) cs 
PIVOT
(
 SUM(Consumed_Qty)
  for Date in ([2021-Sep], [2021-Oct], [2021-Nov])
  ) pvt

RÉSULTAT (Seulement montrant partiellement):

Comme vous pouvez le voir, la seconde ABC de la Technologie de l'enregistrement s'affiche une consommation en 2021-Oct ce qui est exact, cependant, se fondant sur ce montant acheté également en 2021-Oct, et qu'est-ce que restant dans cet enregistrement, la consommation doit être 9,373 et est court (383) par rapport à la 8,990 c'est précisément l'affichage en 2021-Oct. Dans ce cas, l'383 réel de la consommation est en baisse en 2021-Nov, cependant, mon code ne semble pas ramasser ça.

C'est exactement le cas dans les deux Zebra technologies des registres et où le reste est 0, ce qui signifie qu'il a été entièrement consommé, cependant, puisque le contrat d'achat des dates pour les deux d'entre eux sont en 2021-Sep et de la consommation dans le futur mois 2021-Oct, le script SQL ne pas ramasser ça.

J'apprécierais toute orientation pour aider à résoudre ce problème et merci d'avance.

Question 2

Vous êtes pivotante basé sur les VS.[Date], date à laquelle le Lot a été acheté, pas quand il a été consommé. Les trois dernières colonnes indiquent uniquement les numéros lorsque les articles ont été achetés ET consommés dans le MÊME mois.

Au lieu de faire pivoter l'ensemble du jeu de données, déplacer le pivot dans le troisième sous-requête. Cela simplifie la requête en obtention de la Beaucoup de détails, y compris les Contracted_Qty et Remaining_Qty d'abord, puis rejoindre la troisième sous-requête qui est le pivoté ensemble de Consumed_Qty au sort par le de trois mois.

SELECT VS.[Vendor Name], VS.[Vendor No_] AS 'Vendor_No',
VS.[Contracted_Quantity],
RQ.Remaining_Qty AS Remaining_Quantity,
'Contract Type' = 
CASE 
  WHEN VS.[Contract Type] = 1 THEN 'CONTRACT A'
  WHEN VS.[Contract Type] = 2 THEN 'CONTRACT B'
  ELSE 'OTHERS'
  END,
CQ.[2021-Sep], CQ.[2021-Oct], CQ.[2021-Nov]

FROM
    (
    /* List of Lots with details */
    SELECT 
        [Document No_],[Vendor No_],[Lot No_], [Purch_ Contract No_],[Contract Type],[Quantity] AS [Contracted_Quantity]
    FROM [A].[dbo].[Company$Volume Summary]
    ) VS

    /* Remaining Quantity for each Lot */
    LEFT JOIN (
        SELECT [Lot No_] , SUM([Remaining Quantity]) AS Remaining_Quantity 
        FROM [A].[dbo].[Company$Item Ledger Entry]
        GROUP BY [Lot No_]
        ) RQ on RQ.[Lot No_] = VS.[Lot No_]

    /* Consumed Quantity per Lot for three months*/
    LEFT JOIN (

        SELECT [Lot No_], [2021-Sep], [2021-Oct], [2021-Nov]
        FROM 
        (
        SELECT [Lot No_], FORMAT([Posting Date], 'yyyy-MMM') AS 'Date', SUM([Quantity]) AS Consumed_Qty
        FROM [A].[dbo].[Company$Item Ledger Entry]
        WHERE [Entry Type] = '5' 
        GROUP BY [Lot No_], Format([Posting Date], 'yyyy-MMM')
        ) AS src
        PIVOT
        (
         SUM(Consumed_Qty)
          for [Date] in ([2021-Sep], [2021-Oct], [2021-Nov])
          ) pvt

        ) CQ on CQ.[Lot No_] = VS.[Lot No_]
;

Je ne peux pas tester cette requête sans les données, mais il devrait être très proche.

RobertT · Answer 1 · 2021-11-23T20:47:23

Vous êtes pivotante basé sur les VS.[Date], date à laquelle le Lot a été acheté, pas quand il a été consommé. Les trois dernières colonnes indiquent uniquement les numéros lorsque les articles ont été achetés ET consommés dans le MÊME mois.

Au lieu de faire pivoter l'ensemble du jeu de données, déplacer le pivot dans le troisième sous-requête. Cela simplifie la requête en obtention de la Beaucoup de détails, y compris les Contracted_Qty et Remaining_Qty d'abord, puis rejoindre la troisième sous-requête qui est le pivoté ensemble de Consumed_Qty au sort par le de trois mois.

SELECT VS.[Vendor Name], VS.[Vendor No_] AS 'Vendor_No',
VS.[Contracted_Quantity],
RQ.Remaining_Qty AS Remaining_Quantity,
'Contract Type' = 
CASE 
  WHEN VS.[Contract Type] = 1 THEN 'CONTRACT A'
  WHEN VS.[Contract Type] = 2 THEN 'CONTRACT B'
  ELSE 'OTHERS'
  END,
CQ.[2021-Sep], CQ.[2021-Oct], CQ.[2021-Nov]

FROM
    (
    /* List of Lots with details */
    SELECT 
        [Document No_],[Vendor No_],[Lot No_], [Purch_ Contract No_],[Contract Type],[Quantity] AS [Contracted_Quantity]
    FROM [A].[dbo].[Company$Volume Summary]
    ) VS

    /* Remaining Quantity for each Lot */
    LEFT JOIN (
        SELECT [Lot No_] , SUM([Remaining Quantity]) AS Remaining_Quantity 
        FROM [A].[dbo].[Company$Item Ledger Entry]
        GROUP BY [Lot No_]
        ) RQ on RQ.[Lot No_] = VS.[Lot No_]

    /* Consumed Quantity per Lot for three months*/
    LEFT JOIN (

        SELECT [Lot No_], [2021-Sep], [2021-Oct], [2021-Nov]
        FROM 
        (
        SELECT [Lot No_], FORMAT([Posting Date], 'yyyy-MMM') AS 'Date', SUM([Quantity]) AS Consumed_Qty
        FROM [A].[dbo].[Company$Item Ledger Entry]
        WHERE [Entry Type] = '5' 
        GROUP BY [Lot No_], Format([Posting Date], 'yyyy-MMM')
        ) AS src
        PIVOT
        (
         SUM(Consumed_Qty)
          for [Date] in ([2021-Sep], [2021-Oct], [2021-Nov])
          ) pvt

        ) CQ on CQ.[Lot No_] = VS.[Lot No_]
;

Je ne peux pas tester cette requête sans les données, mais il devrait être très proche.

Merci pour votre réponse. La raison pour laquelle je suis en utilisant la SOMME(reste) c'est parce que l'élément de comptabilité d'entrée de table comporte plusieurs types d'entrée, d'achat, d'ajustements, de la consommation. La somme de tous les types d'entrée pour chaque numéro de lot est ce qui me reste qté et ça ne colle pas à ma beaucoup inventaire du livre. cependant, lorsque j'utilise maintenant les CQ.La Date et le pivot et supprimer le >= date de rejoindre à la CQ, il est maintenant de dupliquer l'enregistrement d'achat pour chaque mois de la consommation faisant l'achat total par beaucoup incorrect maintenant. quelles sont les autres données pourrais-je fournir pour aider à expliquer mon problème? beaucoup remercie de votre temps et de m'aider avec ce
Le prochain ajustement serait de déplacer le point de pivot dans la troisième sous-requête. Au lieu de faire pivoter l'ensemble du jeu de données, obtenir le Lot de détails, y compris les Contracted_Qty et Remaining_Qty, puis rejoindre la troisième sous-requête qui est le pivoté ensemble de Consumed_Qty au sort par le de trois mois.
Je suis toujours curieux de voir comment vous pouvez obtenir la quantité restant en additionnant un nombre. Pouvez-vous fournir des exemples de données pour démontrer?
Jamais l'esprit sur la quantité restante. Je viens de réaliser votre livre table doit enregistrer chaque numéro en deux colonnes. Quantité restante est soit un nombre positif ou négatif en fonction du type d'entrée. Résumant cela donnerait la quantité restante. Le nom de la colonne m'a jeté.
Et oui c'est correct! C'est comment le grand livre des enregistrements de la table chaque entrée.
c'est une bonne idée! Je vais essayer de déplacer le pivot dans la sous-requête, vous aviez raison au sujet de la première partie cependant, j'ai eu mon pivot basé sur la date de l'achat au lieu de la consommation de date, et ensuite, j'ai regroupés par le lot no. ce qui semble avoir résolu le problème. mais j'aime l'idée de déplacer le pivot de la troisième sous-requête, vous tiendrons au courant! Merci
De réponses mis à jour sur la base des commentaires dans les commentaires

Le pivotement d'un domaine en fonction de la plage de dates dans la table B, qui est >= de la plage de dates dans le tableau A

La question

La meilleure réponse

Dans d'autres langues

Cette page est dans d'autres langues

Populaires dans cette catégorie

Questions populaires dans cette catégorie