En esta publicación del blog, discutiremos una consulta SQL que maneja una resta en ejecución para calcular las cantidades recibidas frente a las cantidades esperadas. Esta consulta fue inspirada por una publicación en un foro en el sitio web SQLTeam.
La pregunta original se trataba de mostrar las cantidades recibidas frente a las cantidades esperadas, y si había alguna escasez, debería mostrarse en la última línea. El primer conjunto de resultados proporcionado en la pregunta se veía así:
| Artículo | Cantidad Esperada | Cantidad Recibida | Escasez |
|---|---|---|---|
| Artículo01 | 30 | 45 | 5 |
| Artículo01 | 20 | 45 | 5 |
| Artículo02 | 40 | 38 | 2 |
| Artículo03 | 50 | 90 | 10 |
| Artículo03 | 30 | 90 | 10 |
| Artículo03 | 20 | 90 | 10 |
El objetivo era transformar este conjunto de resultados en un segundo conjunto de resultados, donde las cantidades recibidas se ajustan en función de las cantidades esperadas. El segundo conjunto de resultados debería verse así:
| Artículo | Cantidad Esperada | Cantidad Recibida | Escasez |
|---|---|---|---|
| Artículo01 | 30 | 30 | 0 |
| Artículo01 | 20 | 15 | 5 |
| Artículo02 | 40 | 38 | 2 |
| Artículo03 | 50 | 50 | 0 |
| Artículo03 | 30 | 30 | 0 |
| Artículo03 | 20 | 10 | 10 |
La consulta proporcionada en la publicación del foro tenía algunas limitaciones en términos de escalabilidad, pero funcionaba bien para conjuntos de datos más pequeños. Aquí hay un ejemplo de la consulta:
SELECT ex.articulo,
ex.CantidadEsperada,
ex.CantidadEsperada - CASE
WHEN Escasez > umbralParaEscasez AND Escasez - umbralParaEscasez <= CantidadEsperada THEN Escasez - umbralParaEscasez
WHEN Escasez > umbralParaEscasez AND Escasez - umbralParaEscasez > CantidadEsperada THEN CantidadEsperada
ELSE 0
END AS 'CantidadRecibida',
CASE
WHEN Escasez > umbralParaEscasez AND Escasez - umbralParaEscasez <= CantidadEsperada THEN Escasez - umbralParaEscasez
WHEN Escasez > umbralParaEscasez AND Escasez - umbralParaEscasez > CantidadEsperada THEN CantidadEsperada
ELSE 0
END AS 'EscasezTotal'
FROM (
SELECT e1.dt,
e1.articulo,
ISNULL(SUM(e2.CantidadEsperada), 0) AS 'umbralParaEscasez'
FROM Esperado e1
LEFT JOIN Esperado e2 ON e1.articulo = e2.articulo AND e1.dt < e2.dt
GROUP BY e1.dt, e1.articulo
) minValEscasez
INNER JOIN Esperado ex ON ex.articulo = minValEscasez.articulo AND ex.dt = minValEscasez.dt
INNER JOIN Recibido rec ON minValEscasez.articulo = rec.articulo
La consulta utiliza una tabla derivada para calcular el umbral para el valor de “Escasez”. Luego une esta tabla derivada con las tablas “Esperado” y “Recibido” para calcular las cantidades recibidas ajustadas.
El autor de la consulta explicó su proceso de pensamiento al llegar a la solución. Primero analizó los requisitos y los datos proporcionados. Luego creó las tablas necesarias y datos ficticios para probar su consulta. Después de varias iteraciones, logró obtener el resultado deseado y publicó su solución.
Es importante tener en cuenta que si bien esta consulta funciona bien para conjuntos de datos más pequeños, es posible que no sea escalable para conjuntos de datos más grandes. El autor mencionó que comienza a volverse lento cuando hay más de 500,000 a 600,000 registros en la tabla “Esperado”. Por lo tanto, es importante considerar el tamaño de los datos al utilizar este enfoque.
En conclusión, la consulta discutida en esta publicación del blog proporciona una solución para manejar una resta en ejecución en SQL Server. Permite el cálculo de las cantidades recibidas frente a las cantidades esperadas, teniendo en cuenta cualquier escasez. Si bien puede no ser adecuada para conjuntos de datos grandes, puede ser un enfoque útil para problemas más pequeños con estructuras similares.