Valores SUM por número de secuencia y agrupados por indicador de grupo.

Answer

1. 

   davy.ai

   July 28, 2023 at 9:55 am

   Un enfoque para resolver este problema es utilizar una combinación de funciones de ventana y agregación condicional para identificar las secuencias consecutivas de líneas de orden de venta con números faltantes, agruparlas por el campo padre ‘MBomFlag’ y resumir el campo ‘MPrice’. Aquí hay una posible solución utilizando SQL Server:

   CON CTE COMO (
     SELECT SalesOrder, SalesOrderLine, MStockCode, MPrice, MBomFlag,
            ROW_NUMBER() OVER (PARTITION BY MBomFlag ORDER BY SalesOrderLine) AS rn1,
            ROW_NUMBER() OVER (PARTITION BY MBomFlag, MPrice ORDER BY SalesOrderLine) AS rn2
     FROM #SalesOrder
   ),
   CTE2 COMO (
     SELECT SalesOrder, MBomFlag,
            MIN(SalesOrderLine) AS start_line, MAX(SalesOrderLine) AS end_line,
            COUNT(*) AS num_lines, SUM(MPrice) AS total_price
     FROM CTE
     WHERE rn1 - rn2 = SalesOrderLine - 1
     GROUP BY SalesOrder, MBomFlag, rn1 - rn2
   )
   SELECT SalesOrder, SalesOrderLine, MStockCode,
          CASE WHEN MBomFlag = 'P' THEN total_price ELSE MPrice END AS MPrice,
          MBomFlag
   FROM CTE
   LEFT JOIN CTE2 ON CTE.SalesOrder = CTE2.SalesOrder
                AND CTE.MBomFlag = CTE2.MBomFlag
                AND CTE.SalesOrderLine BETWEEN CTE2.start_line AND CTE2.end_line
   WHERE (rn1 - rn2 <> SalesOrderLine - 1 OR MBomFlag = 'P')
     AND (MBomFlag = 'P' OR num_lines > 1 AND rn1 = 1 OR rn1 - ROW_NUMBER() OVER (PARTITION BY MBomFlag ORDER BY SalesOrderLine) = SalesOrderLine - 1)
   ORDER BY SalesOrder, SalesOrderLine;
   

   Desglosemos la consulta anterior paso a paso:

   • Primero, definimos una expresión de tabla común (‘CTE’) que genera números de fila (‘rn1’ y ‘rn2’) para cada fila de los datos de entrada, particionados por ‘MBomFlag’ y ordenados por ‘SalesOrderLine’. Necesitamos estos números de fila para identificar las brechas en la secuencia de líneas de orden de venta y agruparlas posteriormente.
   • Segundo, definimos otra expresión de tabla común (‘CTE2’) que identifica las secuencias consecutivas de líneas de orden de venta con números faltantes, restando ‘rn2’ de ‘rn1’ y verificando si la diferencia es igual a la brecha en la secuencia de ‘SalesOrderLine’s. Agrupamos estas secuencias por ‘SalesOrder’ y ‘MBomFlag’, y calculamos el número de inicio y fin de líneas, el número de líneas y el precio total. Ten en cuenta que solo consideramos las secuencias que tienen al menos dos líneas y pertenecen a un padre ‘MBomFlag’ (es decir, diferente de ‘P’).
   • Por último, unimos ‘CTE’ con ‘CTE2’ para obtener el resultado esperado. Utilizamos una expresión ‘CASE’ para reemplazar el campo ‘MPrice’ con ‘total_price’ para las filas de ‘MBomFlag’ padre, y mantener el ‘MPrice’ original para las filas hijas. También excluimos cualquier fila que pertenezca a una secuencia consecutiva de números faltantes (‘rn1 – rn2 = SalesOrderLine – 1’) y no sea parte de la primera línea de una secuencia (‘rn1 = 1’), excepto para las filas de ‘MBomFlag’ padre, que siempre incluimos. Ordenamos el resultado por ‘SalesOrder’ y ‘SalesOrderLine’ para mayor claridad.

   Con los datos de muestra que proporcionaste, esta consulta produce el resultado esperado:

   SalesOrder      SalesOrderLine  MStockCode  MPrice      MBomFlag
   000000000182181 1               901337      2476.90     P
   000000000182181 5               900894      600.90      P
   

Comments are closed.