Select Mysql for Kardex Balance

2

Dear Colleagues. A query is required in the following way.

My question is how I would have to perform the select to generate the balance field. Thanks for your help.

Greetings

    
asked by EvolutionxD 07.06.2016 в 18:13
source

2 answers

1

If you want to do it in a single query, you need to use auxiliary variables. Here an example, consider that you must execute the 3 sentences to obtain the desired result:

SET @TOTAL := 0;
SET @ARTICULO := '';
SELECT T.id, T.articulo, T.tipomov, T.cantidad, T.saldo
FROM (
SELECT id, articulo, tipomov, cantidad,
  CASE 
    WHEN (@ARTICULO = '' OR @ARTICULO = articulo) AND tipomov = '01-INGRESO' THEN @TOTAL := @TOTAL + cantidad
    WHEN (@ARTICULO = '' OR @ARTICULO = articulo) AND tipomov = '02-SALIDA' THEN @TOTAL := @TOTAL - cantidad
    WHEN @ARTICULO != articulo AND tipomov = '01-INGRESO' THEN @TOTAL := 0 + cantidad
    WHEN @ARTICULO != articulo AND tipomov = '02-SALIDA' THEN @TOTAL := 0 - cantidad
  END AS saldo,
  @ARTICULO := articulo
FROM kardex
ORDER BY articulo, id
) T;
    
answered by 07.06.2016 / 18:26
source
1

excellent sql, but I think it can be reduced a bit. You can try the following:

SQL

we set a variable

SET @T:0; 

We create sentence

SELECT articulo, tipomov, cantidad, IF(tipomov ='01-INGRESO',(@T:[email protected]+cantidad),(@T:[email protected])) AS saldo FROM kardex;
    
answered by 07.06.2016 в 20:13