Pivotante un campo basado en la fecha de rango en la tabla B que es >= intervalo de fechas en Una tabla

Question 1

Estoy tratando de pivote de una fila de columnas con las que está basado en las fechas con formato 'yyyy-MMM", y mientras esto funciona como se esperaba para el mes actual período, no trabajo para los próximos meses, y no puedo averiguar cómo solucionar este problema y agradecería mucho cualquier comentario sobre esta.

Aquí un poco de fondo: He comprado la cantidad proveniente de un Proveedor de la tabla de resumen que incluye información acerca de un contrato, tales como tipo de contrato, cantidad, número de lote, la fecha de creación en formato 'yyyy-MMM' como la fecha real no importa para este propósito.

Cantidad restante es otro de los campos procedentes de un elemento de contabilidad mesa de entrada, que es esencialmente un resumen de los tipos de entradas con base en el número de lote.

Consume la Cantidad es lo que estoy tratando de pivote basa en el mes fue consumida y también viene de los movimientos de productos con un tipo de entrada de 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

RESULTADO (Sólo se muestra parcialmente):

Como se puede ver, la segunda ABC del expediente de la Tecnología muestra un consumo en 2021-Oct que es preciso, sin embargo, con base en que se contrajo la cantidad comprada también en 2021-Oct, y lo que queda en ese registro, las necesidades de consumo para ser 9,373 y es corto (383) en comparación con el 8,990 es mostrar con precisión en 2021-Oct. En este caso, el 383 consumo real está cayendo en el 2021-Nov, sin embargo, mi código no parece elige eso.

Este es el caso exacto en tanto Zebra technologies registros así, cuando el resto es 0, lo que significa que se ha consumido por completo, sin embargo, ya que el contrato de compra fechas para los dos de ellos están en 2021-Sep y el consumo en el futuro mes 2021-Oct, la secuencia de comandos SQL no elige eso.

Agradecería cualquier orientación para ayudar a solucionar este problema y gracias de antemano.

Question 2

Usted es pivotante basado en el VS.[Fecha], que es cuando el Lote fue comprado, no cuando se consume. Las últimas tres columnas sólo se muestran los números cuando los artículos fueron comprados Y consumidos en el MISMO mes.

En lugar de girar todo el conjunto de datos, mover el pivote en el tercer subconsulta. Esto simplifica la consulta por llegar la gran cantidad de detalles, incluido el Contracted_Qty y Remaining_Qty primero y luego unirse a la tercera subconsulta que es el pivote conjunto de Consumed_Qty por Mucho al de los tres 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_]
;

Yo no puedo probar esta consulta sin los datos, pero debe estar muy cerca.

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

Usted es pivotante basado en el VS.[Fecha], que es cuando el Lote fue comprado, no cuando se consume. Las últimas tres columnas sólo se muestran los números cuando los artículos fueron comprados Y consumidos en el MISMO mes.

En lugar de girar todo el conjunto de datos, mover el pivote en el tercer subconsulta. Esto simplifica la consulta por llegar la gran cantidad de detalles, incluido el Contracted_Qty y Remaining_Qty primero y luego unirse a la tercera subconsulta que es el pivote conjunto de Consumed_Qty por Mucho al de los tres 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_]
;

Yo no puedo probar esta consulta sin los datos, pero debe estar muy cerca.

Gracias por tu respuesta. La razón por la que estoy usando SUMA(resto) es porque el elemento de contabilidad mesa de entrada tiene varios tipos de entradas, la compra, los ajustes, el consumo. La suma de todos los tipos de entrada para cada número de lote es lo que me pone al resto de la cnt y se hace atar a mi montón de inventario de contabilidad. sin embargo, cuando ahora uso la CQ.Fecha en el pivote y quitar el >= fecha de unirse a la CQ, ahora se duplica el registro de la compra para cada mes del consumo de realizar la compra total por mucho incorrecto ahora. qué otros datos podría proporcionar para ayudar a explicar mi problema mejor? aprecio mucho su tiempo y ayuda con esto
El siguiente ajuste sería mover el pivote en la tercera sub consulta. En lugar de girar todo el conjunto de datos, obtener la gran cantidad de detalles, incluido el Contracted_Qty y Remaining_Qty y, a continuación, unirse a la tercera subconsulta que es el pivote conjunto de Consumed_Qty por Mucho al de los tres meses.
Todavía estoy curioso cómo usted puede conseguir la cantidad que queda por sumar algún número. Puede usted proporcionar algunos datos de ejemplo para demostrar?
No importa la cantidad restante. Me acabo de dar cuenta de su libro de contabilidad de la tabla debe registrar el número de todos en dos columnas. Cantidad restante es un número positivo o negativo dependiendo del tipo de entrada. Sumando esto produciría la cantidad restante. El nombre de la columna me desconcertó.
Sí que es correcto! Eso es lo que el libro de contabilidad de los registros de la tabla de cada entrada.
es una buena idea! Voy a tratar de mover el pivote en la subconsulta, que estaban en lo correcto acerca de la primera parte, sin embargo, yo tenía mi pivot basado en la fecha de compra lugar de el consumo de la fecha, y a continuación me agrupados por el lote no. que parecía haber solucionado el problema. pero me gusta la idea de mover el pivote para el tercer subconsulta, los mantendremos informados! Gracias
Respuesta actualizada basada en las opiniones en los comentarios

Pivotante un campo basado en la fecha de rango en la tabla B que es >= intervalo de fechas en Una tabla

Pregunta

Mejor respuesta

En otros idiomas

Esta página está en otros idiomas

Popular en esta categoría

Las preguntas más habituales en esta categoría