sql – Select with SUM until sum B is equal to or less than A

Question:

I have two tables, products and lots:

Products

codigo
qtdatual

Lots

codigo
qtdlote
validade

An example select would be:

SELECT P.CODIGO, L.QTDLOTE, L.VALIDADE
FROM LOTES L
INNER JOIN PRODUTOS P ON(P.CODIGO = L.CODIGO)
WHERE (P.QTDATUAL >= (SELECT SUM(QTDLOTE) FROM LOTES WHERE (CODIGO = P.CODIGO) ORDER BY VALIDADE))

In the example above, I am trying to bring only the batches that in their sum are less than or equal to the current quantity in stock, that is, even if the total sum in batches was greater than the quantity of products I would like to bring only/up to the batches that would enter in the sum without exceeding the qtdatual .

Literal example

Products:

codigo: 1 qtdatual: 30

Lots:

1: codigo: 1 qtdlote: 10 validade: 01/2018
2: codigo: 1 qtdlote: 15 validade: 02/2018
3: codigo: 1 qtdlote: 20 validade: 03/2017

In the select I would like to bring batches 1 and 3 (note the catch on the date) which in the sum equals the QTDATUAL of products, but SUM will not bring any batch because the total sum is greater than the products.

Will I have to do a procedure for this?

ps.: I'm going to add an addendum here because after looking and thinking more about this question, I ended up reversing the validity case, but the select could be based on the same answer from the examples above, in fact I would have to bring the newer batches , considering that the old ones have already been sold, so I would bring lots 1 and 2, but then it would be a matter of adding the DESC in the validity period, it won't change the issue.

Answer:

Here is a possible solution to your problem.

;WITH CTE AS
( SELECT ROW_NUMBER() OVER (ORDER BY validade DESC) AS idRowNumber
       , codigo
       , qtdlote
       , validade
    FROM Lotes
)
, CTE_Recursivo as
( SELECT idRowNumber
       , codigo
       , qtdlote
       , validade
       , vlSoma = qtdlote 
    FROM CTE
   WHERE idRowNumber = 1

   UNION ALL

  SELECT A.idRowNumber
       , A.codigo
       , A.qtdlote
       , A.validade
       , vlSoma = A.qtdlote + B.vlSoma
    FROM CTE A
         INNER JOIN CTE_Recursivo B On A.idRowNumber = B.idRowNumber +1
)
SELECT *
  FROM CTE_Recursivo
 WHERE vlSoma <= (SELECT TOP(1) qtdatual FROM Produtos WHERE Produtos.codigo = CTE_Recursivo.codigo)