Puedes valerte de las funciones de ventana, para realizar el cálculo que deseas.
De la manera que primero se me ocurre es la realización de una suma de la cantidad comprada, tanto hasta la fila anterior como hasta la fila actual para, comparando ese valor con el total disponible, calcular cual es el remanente de esa compra (suponiendo que, sin un control de lotes, despachan siempre primero lo de las compras más antiguas, que ya es mucho suponer, pero es lo que entiendo que quieres lograr.
Mi solución está basada en el uso de CTE y funciones de ventana, y la voy explicando paso a paso.
- Los CTE
Compra, CompraDet, ArtDisponible simulan tus tablas originales, no he incluido todas sus columnas para que quede clara cual es la idea de la solución, sin todo el ruido extra, ni he seguido necesariamente sus nombres, ya que tu pregunta no incluye su estructura ni datos en formato de texto, por lo que he seguido mi propio estilo. Lo que si he dejado es la columna Estatus para mostrar a donde trasladar los filtros que tienes actualmente en las consultas.
- El CTE
Disponible hace el cálculo del total disponible por artículo
- el CTE
CompraOrdenada simula tu primera consulta, obteniendo las compras y ordenandolas. Además, realiza dos cálculos adicionales:
- La columna
CompraAcumuladaAnterior tiene el acumulado de las compras hasta antes de la compra de la fila actual. La primera compra de cada artículo tendrá NULL en esta columa.
- La columna
CompraAcumulada tiene el acumulado de las compras incluyendo la compra de la fila actual.
- Finalmente, se encuentra la consulta final, que tira de las anteriores. Hago un
left join entre CompraOrdenada y Disponible. Acá, el cálculo del saldo disponible de cada compra es trivial, gracias a que en cada fila tenemos el acumulado de las compras hasta antes y después de la compra que representa la fila, lo que queda en la columna DisponibleDeLaCompra:
- Si el acumulado de las compras, incluyendo la compra actual, es menor o igual al disponible del artículo, sabemos que toda la cantidad comprada se encuentra disponible, por lo que devolvemos ese valor.
- Si no, pero el acumulado de las compras hasta antes de la compra actual es menor o igual al disponible, sabemos que solo parte de la compra se encuentra disponible y podemos calcularla sacando la diferencia del disponible total del producto menos el acumulado de compras hasta antes de la compra actual.
- Si no se cumple ninguna de las anteriores, los productos comprados ya se han agotado y el disponible es cero.
En código:
with
Compra as (
select *
from (values (1, cast('20200601' as date), 'Concluido')
, (2, '20200617', 'Concluido')
, (3, '20200630', 'Cancelado')
, (4, '20200718', 'Concluido')
, (5, '20200726', 'Concluido')
) q1 (idCompra, FechaEmision, Estatus)
)
,
CompraDet as (
select *
from (values (1, 'LN000001', 50, 3496.1)
, (1, 'LN000002', 10, 121.12)
, (2, 'LN000001', 20, 3546.85)
, (2, 'LN000002', 4, 144.12)
, (3, 'LN000001', 17, 3217.14)
, (3, 'LN000002', 24, 119.54)
, (4, 'LN000001', 4, 3546.85)
, (4, 'LN000002', 37, 114.54)
, (5, 'LN000001', 10, 3546.85)
, (5, 'LN000002', 11, 119.31)
) q1 (idCompra, Articulo, Cantidad, Costo)
)
,
ArtDisponible as (
select *
from (values ('LN000001', 'AA11', 10)
, ('LN000001', 'AA22', 12)
, ('LN000001', 'CC18', 8)
, ('LN000002', 'AA11', 54)
) q1 (Articulo, Ubicacion, Disponible)
)
,
Disponible as (
select Articulo, sum(Disponible) DisponibleTotal
from ArtDisponible
group by Articulo
)
,
CompraOrdenada as (
select a.Articulo
, a.idCompra
, b.FechaEmision
, a.Cantidad
, a.Costo
, rank() over (partition by a.Articulo order by b.FechaEmision desc, a.idCompra desc) Orden
, sum(a.Cantidad) over (partition by a.Articulo order by b.FechaEmision desc, a.idCompra desc rows between unbounded preceding and 1 preceding) CompraAcumuladaAnterior
, sum(a.Cantidad) over (partition by a.Articulo order by b.FechaEmision desc, a.idCompra desc rows between unbounded preceding and current row) CompraAcumulada
from CompraDet a
inner join Compra b on b.idCompra = a.idCompra
where b.Estatus = 'Concluido'
)
select c.Articulo
, c.idCompra
, c.FechaEmision
, c.Cantidad
, c.Costo
, c.Orden
, case
when c.CompraAcumulada <= d.DisponibleTotal then c.Cantidad
when coalesce(c.CompraAcumuladaAnterior, 0) <= d.DisponibleTotal then d.DisponibleTotal - coalesce(c.CompraAcumuladaAnterior, 0)
else 0
end DisponibleDeLaCompra
from CompraOrdenada c
left join Disponible d on d.Articulo = c.Articulo
where c.Articulo = 'LN000001'
order by c.Articulo, c.Orden;
Lo que me entrega el siguiente resultado:
Articulo idCompra FechaEmision Cantidad Costo Orden DisponibleDeLaCompra
LN000001 5 2020-07-26 10 3546.85 1 10
LN000001 4 2020-07-18 4 3546.85 2 4
LN000001 2 2020-06-17 20 3546.85 3 16
LN000001 1 2020-06-01 50 3496.10 4 0
