I have two tables, products and lots:
Products
codigo
qtdatual
Lots
codigo
qtdlote
validade
A% 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'm 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 / lots that would enter the sum without exceeding select .
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 lots 1 and 3 (note the catch in the date) that in the sum is equal to QTDATUAL of products, but the SUM does not bring any lot because the sum total 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 about this question, I ended up reversing the validity case, but the select could be based on the same answer as the examples above, actually I would have to bring the new lots, considering that the old ones have already been sold, so I would bring lots 1 and 2, but there it would be a question of adding DESC on validity, it will not change the issue.