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

Question

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

I have two tables, products and lots:

Products

codigo
qtdatual

Lots

codigo
qtdlote
validade

An select example 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 lots that in its sum are less than or equal to the current amount in stock, i.e. even if the total sum in lots was greater than the amount of products I would like to bring only / until the lots that would enter the sum without exceeding 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 select I would like to bring batches 1 and 3 (note the catch in the date) that in the sum is equal to QTDATUAL of products, but the SUM will not bring any batch because the total sum is greater than products.

Will I have to do a procedure for this?

Ps.: I'm going to make an addendum here because after looking and thinking more about this question, I ended up reversing the in case of validity, but select can be based on the same answer as the above examples, in fact I would have to bring the newer batches, considering that the old ones have already been sold, then I would bring the batches 1 and 2, but then it would be a matter of adding the DESC in validity, it will not change the question.

3

sql stored-procedures sql-select

Author: Marcelo, 2018-08-17

Source

1 answers

score 1 · Answer 1

Follows 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)