Giro um campo com base no intervalo de datas na tabela B, que é >= intervalo de data em Uma tabela

Question 1

Eu estou tentando pivô de uma linha em colunas que são baseados em datas formatadas 'yyyy-MMM" e enquanto isso funciona conforme o esperado para um mês atual período, ele não funcionar para o futuro meses e eu não consigo descobrir como corrigir isso e gostaria muito de apreciar qualquer feedback sobre este.

Aqui está um pouco do plano de fundo: Eu comprei quantidade proveniente de um Fornecedor de tabela de resumo que inclui informações sobre um contrato como contrato tipo, quantidade, número do lote, a data de criação formatado no formato 'yyyy-MMM' como a data real não importa para essa finalidade.

Quantidade restante é outro campo vindo de um item de contabilidade tabela de entrada que é, essencialmente, somando-se todos os tipos de entradas com base no número de lote.

Quantidade consumida é o que eu estou tentando dinâmica com base no mês foi consumido e é também vindo de as entradas contábeis de item com uma entrada de filtro de tipo

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

RESULTADO (mostrando Apenas parcialmente):

Como você pode ver, o segundo ABC do registro da Tecnologia mostra um consumo em 2021-Out que é preciso, no entanto, com base em que o montante contratado adquirido também em 2021-Out, e o que resta no registro, o consumo precisa ser 9,373 e é curto (383), em comparação com o 8,990 é exatamente apresentar em 2021-Out. Neste caso, a 383 consumo real está caindo em 2021-Nov, no entanto, o meu código não parece para comprar.

Este é o caso exato em ambos Zebra technologies registros, onde o restante é 0, o que significa que ele tem sido totalmente consumido, no entanto, uma vez que o contrato de compra datas para ambas estão em 2021-Set, e o consumo é de, no futuro, mês 2021-Out, o script SQL não comprar.

Eu apreciaria qualquer orientação para ajudar a resolver isto e obrigado antecipadamente.

Question 2

Você está girando com base no VS.[Data], que é quando o Lote foi comprado, não quando ele foi consumido. As três últimas colunas mostrará apenas os números quando os itens foram comprados E consumidos no MESMO mês.

Em vez de giro, todo o conjunto de dados, mova o pivô para a terceira subconsulta. Isso simplifica a consulta, obtendo o Monte de detalhes, incluindo o Contracted_Qty e Remaining_Qty primeiro e, em seguida, ingressar para o terceiro subconsulta que é o articulado conjunto de Consumed_Qty por sorte, por três meses.

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

Eu não posso testar essa consulta sem os dados, mas deve ser muito perto.

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

Você está girando com base no VS.[Data], que é quando o Lote foi comprado, não quando ele foi consumido. As três últimas colunas mostrará apenas os números quando os itens foram comprados E consumidos no MESMO mês.

Em vez de giro, todo o conjunto de dados, mova o pivô para a terceira subconsulta. Isso simplifica a consulta, obtendo o Monte de detalhes, incluindo o Contracted_Qty e Remaining_Qty primeiro e, em seguida, ingressar para o terceiro subconsulta que é o articulado conjunto de Consumed_Qty por sorte, por três meses.

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

Eu não posso testar essa consulta sem os dados, mas deve ser muito perto.

Obrigado pela sua resposta. A razão que eu estou usando SOMA(restantes) é porque o item de contabilidade tabela de entrada tem vários tipos de entrada, de compra, de ajustes, de consumo. A soma de todos os tipos de entrada para cada número de lote é o que me deixa com a restante quantidade e amarrar aos meus monte de inventário de contabilidade. no entanto, quando eu usar agora CQ.Data em que a dinâmica e remover a >= data de participar no CQ, agora duplica o registro de compra para cada mês de consumo de fazer a compra total por lote incorreto agora. que outros dados que eu poderia oferecer para ajudar a explicar o meu problema melhor? aprecio muito seu tempo para me ajudar com isso
O próximo ajuste seria mover o pivô para a terceira consulta de sub-rotina. Em vez de giro, todo o conjunto de dados, obter o Monte de detalhes, incluindo o Contracted_Qty e Remaining_Qty e, em seguida, ingressar para o terceiro subconsulta que é o articulado conjunto de Consumed_Qty por sorte, por três meses.
Eu ainda estou curioso para saber como você pode obter a quantidade restante pela soma de um número. Você pode fornecer alguns dados de exemplo para demonstrar?
Nunca mente sobre a quantidade restante. Só eu percebi a sua contabilidade tabela de registro de cada número em duas colunas. Quantidade restante é um número positivo ou negativo, dependendo do tipo de entrada. Somando isto produziria a quantidade restante. O nome da coluna me jogou fora.
Sim, isso é correto! Isso é como a contabilidade tabela de registros de cada entrada.
isso é uma boa idéia! Vou tentar mover o pivô para a subconsulta, você estava certo sobre a primeira parte, porém, eu tinha a minha dinâmica com base na data de aquisição, em vez de o consumo de data, e em seguida, agrupados pelo lote nº. , que parecia não ter corrigido o problema. mas eu gosto da ideia de mudar o pivô para a terceira subconsulta, irá mantê-lo! Obrigado

Giro um campo com base no intervalo de datas na tabela B, que é >= intervalo de data em Uma tabela

Pergunta

Melhor resposta

Em outros idiomas

Esta página está em outros idiomas

Popular nesta categoria

Perguntas populares nesta categoria