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

Asked

Viewed 350 times

3

I have two tables, products and lots:

Products

codigo
qtdatual

Batches

codigo
qtdlote
validade

A 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 above example I am trying to bring only lots that in their sum are less or equal to the current quantity in stock, that is even if the total sum in batches was greater than the amount of products I would like to bring only/even lots that would go into the sum without exceeding the qtdatual.

Literal example

Products:

codigo: 1 qtdatual: 30

Batches:

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 on date) that in sum is equal to QTDATUAL of products, but the SUM will not bring any lot because the total sum is greater than products.

Will I have to make a plan for this?

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

sql select-sql stored-procedures

  • The biggest problem is that you cannot consider the orders, do you agree with me? Then what would be the choice parameter of which records will be selected?

  • It is in the example I gave does not really work, but the idea is to consider the validates that are the key point in this query, I do not know if HAVING would help in this issue, I am studying, the only solution I see would be the use of loop Procedure in the records to make the sums.

  • But what validity has to do if you have nothing to compare?

  • In batch control we know which lots are already expired, this way we have to bring first the oldest lots so in the example I purposely put lot 3 older than 2. In this research we have (and we need) what to compare, the sum of the lots is compared to the current stock taking into account the oldest lots by the validity.

  • If you wish to greater sum such that it is less than or equal to A I believe falls into the backpack problem (https://en.wikipedia.org/wiki/Knapsack_problem). I’m not sure you want such complexity. See this question: https://answall.com/questions/280985/knapsack-problem-problema-de-mochila-sql-server-cte-recursive

1 answer

1

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

Browser other questions tagged sql select-sql stored-procedures

You are not signed in. Login or sign up in order to post.