Get sum of previous records in query and add or subtract the following results-CodePudding

Case: I select an initial date and an end date, it should bring me the movements of all the products in that date range, but if there were movements before the initial date (records in table), I want to obtain the previous sum (prevData)

if the first move is exit 5 and the second move is income 2.

I would have in the first row (prevData-5), second row would have (prevData-5 2) and thus have a cumulative.

The prevData would be calculated as the sum of the above, validating the product id of the record, I made the query but if the product has 10 movements, I would do the query 10 times, and how would I identify the sum of another product_id?

SELECT
ik.id,
ik.quantity,
ik.date,
ik.product_id,
#balance = (SELECT SUM(quantity) FROM table_kardex WHERE product_id = ik.product_id AND id < ik.id)
from table_kardex  ik
where ik.date between '2021-11-01'  and  '2021-11-15'
order by ik.product_id,ik.id asc

I hope you have given me to understand, I will be attentive to any questions.

#table_kardex

id|quantity|date|product_id
1  8       2020-10-12  2
2  15      2020-10-12  1
3  5       2021-11-01  1
4  10      2021-11-01  2
5  -2      2021-11-02  1
6  -4      2021-11-02  2

#result
id|quantity|date|product_id|saldo 
3  5       2021-11-01  1    20  (15 5)
5  -2      2021-11-02  1    18  (15 5-2)
4  10      2021-11-01  2    18  (8 10-4)
6  -4      2021-11-02  2    14  (15 5-2)

Use MySQL 5.7

CodePudding user response：

If you're using MySQL 8 , then analytic functions can be used here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY date) rn,
              SUM(quantity) OVER (PARTITION BY product_id ORDER BY date) saldo
    FROM table_kardex
    WHERE date BETWEEN '2021-11-01' AND '2021-11-15'
)

SELECT id, quantity, date, product_id, saldo
FROM cte
WHERE rn > 1
ORDER BY product_id, date;

CodePudding user response：

MySQL 5.7

Try this:

SELECT *
FROM (
    SELECT product_id, 
           t1.`date`, 
           SUM(t2.quantity) - t1.quantity cumulative_quantity_before, 
           SUM(t2.quantity) cumulative_quantity_after
    FROM table t1
    JOIN table t2 USING (product_id)
    WHERE t1.`date` >= t2.`date`
      AND t1.`date` <= @period_end
    GROUP BY product_id, t1.`date`, t1.quantity
) prepare_data
WHERE `date` >= @period_start;

CodePudding user response：

The easiest solution is to use the window function SUM OVER to get the running total. In the second step reduce this to the date you want to have this started:

SELECT id, quantity, date, product_id, balance
FROM
(
    SELECT
      id,
      quantity,
      date,
      product_id,
      SUM(quantity) OVER (PARTITION BY product_id ORDER BY id) AS balance
    from table_kardex ik
    where date < DATE '2021-11-16'
) cumulated
WHERE date >= DATE '2021-11-01'  
ORDER BY product_id, id;

UPDATE: You have changed your request to mention that you are using an old MySQL version (5.7). This doesn't support window functions. In that case use your original query. If I am not mistaken, though, #balance = (...) is invalid syntax for MySQL. And according to your explanation you want id <= ik.id, not id < ik.id:

SELECT
  ik.id,
  ik.quantity,
  ik.date,
  ik.product_id,
  (
    SELECT SUM(quantity)
    FROM table_kardex 
    WHERE product_id = ik.product_id AND id <= ik.id
  ) AS balance
FROM table_kardex ik
WHERE ik.date >= DATE '2021-11-01' AND ik.date < DATE '2021-11-16'
ORDER BY ik.product_id, ik.id;

The appropriate indexes for this query are:

create index idx1 on table_kardex (date, product_id, id);
create index idx2 on table_kardex (product_id, id, quantity);