Basculante di un campo in base a un intervallo di date nella tabella B che è >= intervallo di date in Una tabella

Question 1

Sto cercando di spostare una riga in colonne che sono basati su formattato date "yyyy-MMM" e mentre questo funziona come previsto per un periodo di un mese, non riesce a lavorare per il futuro di mesi e non riesco a capire come risolvere questo problema e sarebbe molto apprezzato qualche feedback su questo.

Ecco un po ' di sfondo: Ho acquistato quantità proveniente da un Fornitore tabella di sintesi che include informazioni su un contratto come contratto tipo, la quantità, il numero di lotto, la data di creazione formattato in 'yyyy-MMM' come la data effettiva non importa per questo scopo.

Quantità rimanente è un altro campo provenienti da un elemento di contabilità tabella di voce che è essenzialmente sommando tutti i tipi di voci basate sul numero di lotto.

Consumato Quantità è quello che sto cercando di pivot in base al mese in cui è stata consumata e anche provenienti da la voce di voci di contabilità con una voce tipo di filtro

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

RISULTATO (mostrando Solo parzialmente):

Come si può vedere, il secondo ABC Tecnologia record mostra un consumo nel 2021-Ott precisa, tuttavia, che l'importo contratto acquistati anche nel 2021 Ottobre, e quello che rimane nel record, il consumo deve essere 9,373 e ' breve (383) rispetto al 8,990 è la visualizzazione accurata nel 2021-Ott. In questo caso il 383 consumo effettivo è di cadere nel 2021-Nov, tuttavia, il mio codice non sembra che prendere.

Questo è il caso esatto in entrambi Zebra technologies record e, dove il resto è 0, il che significherebbe che è stato completamente consumato, tuttavia, dal momento che il contratto di acquisto date per entrambi sono nel 2021-Set e il consumo è in futuro mese 2021-Ott, lo script SQL non captarlo.

Apprezzerei qualsiasi guida per aiutare a risolvere questo problema e grazie in anticipo.

Question 2

Si sono piroettanti base del VS.[Data], che è quando il Lotto è stato acquistato, non quando è stato consumato. Le ultime tre colonne mostrerà solo i numeri quando gli elementi sono stati acquistati E consumati nello STESSO mese.

Invece perno l'intero set di dati, spostare il pivot nel terzo sottoquery. Questo semplifica la query da ottenere il Sacco di dettagli tra cui l'Contracted_Qty e Remaining_Qty prima e poi si uniscono per il terzo sottoquery che è il pivot set di Consumed_Qty da Sacco da tre mesi.

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_]
;

Io non posso testare questa query senza dati, ma dovrebbe essere molto vicino.

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

Si sono piroettanti base del VS.[Data], che è quando il Lotto è stato acquistato, non quando è stato consumato. Le ultime tre colonne mostrerà solo i numeri quando gli elementi sono stati acquistati E consumati nello STESSO mese.

Invece perno l'intero set di dati, spostare il pivot nel terzo sottoquery. Questo semplifica la query da ottenere il Sacco di dettagli tra cui l'Contracted_Qty e Remaining_Qty prima e poi si uniscono per il terzo sottoquery che è il pivot set di Consumed_Qty da Sacco da tre mesi.

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_]
;

Io non posso testare questa query senza dati, ma dovrebbe essere molto vicino.

Grazie per la risposta. Il motivo per cui sto utilizzando la SOMMA(rimanenti) è perché la voce di contabilità tabella di voce è più tipi di voce, di acquisto, le regolazioni, i consumi. La somma di tutti i tipi di ingresso per ogni numero di lotto è quello che mi fa al restante qty e cravatta per il mio sacco di inventario di contabilità. tuttavia, quando io uso CQ.Data il perno e rimuovere il >= data join su CQ, ora duplica l'acquisto record per ogni mese del consumo totale acquistato da molti non corretto ora. altri dati posso fornire per aiutare a spiegare il mio problema è di meglio? apprezzo molto il vostro tempo e aiuto con questo
La regolazione successiva sarebbe quella di spostare il pivot nel terzo sub query. Invece perno l'intero set di dati, ottenere il Sacco di dettagli tra cui l'Contracted_Qty e Remaining_Qty e quindi aggiungere il terzo sottoquery che è il pivot set di Consumed_Qty da Sacco da tre mesi.
Io sono ancora curioso di sapere come è possibile ottenere quantità rimanente dalla somma di un certo numero. Si può fornire alcuni dati di esempio per dimostrare?
Non importa la quantità rimanente. Ho appena realizzato la vostra contabilità tabella deve registrare ogni numero in due colonne. Quantità rimanente è un numero positivo o negativo a seconda del tipo di voce. Sommando questo vi darà la quantità rimanente. Il nome della colonna che mi ha buttato fuori.
Sì è corretto! Ecco come la contabilità tabella dei record di ogni voce.
è una buona idea! Cercherò di spostare il pivot in sottoquery, avevi ragione sulla prima parte, però, ho avuto il mio pivot basato sulla data di acquisto, invece, il consumo di data, e poi sono raggruppati per il lotto n. che sembrava aver risolto il problema. ma mi piace l'idea di spostare il perno per il terzo sottoquery, vi terremo aggiornati! Grazie

Basculante di un campo in base a un intervallo di date nella tabella B che è >= intervallo di date in Una tabella

Domanda

Migliore risposta

In altre lingue

Questa pagina è in altre lingue

Popolare in questa categoria

Domande popolari in questa categoria