I have a structure of related tables. The main one is a table called MOVIMIENTO_ALMACEN with its column called CANTIDAD where it calculates the amount but according to the type of movement (" ENTRY " OR " EXIT ") and they make a subtraction calculation to know what the current stock is.
In the first query I do is for a module called KARDEX and its result is OK, I take the same query for a module called PRODUCT INVENTORY because of the difference in relating it to a table where it tells me the storage location of such products.
Then when I add LEFT JOIN to the KARDEX query for the PRODUCT INVENTORY the SUM() where I do the calculation is altered to random numbers.
This is the kardex query:
SELECT
id_producto,
descripcion,
lote,
serie,
codigo,
rscrsnso,
fechavencimiento,
sum(stock) AS stock
FROM
(
(
SELECT
ma.id_producto,
p.descripcion,
IFNULL(ma.lote, '') AS lote,
IFNULL(ma.serie, '') AS serie,
IFNULL(ma.codigo, '') AS codigo,
IFNULL(ada.rscrsnso, '') AS rscrsnso,
IFNULL(ada.fechavencimiento, '') AS fechavencimiento,
GREATEST(
COALESCE (
(
SUM(
CASE
WHEN ma.tip_movimiento = 'INGRESO' THEN
GREATEST(ma.cantidad, 0)
END
)
),
0
) - COALESCE (
(
SUM(
CASE
WHEN ma.tip_movimiento = 'SALIDA' THEN
GREATEST(ma.cantidad, 0)
END
)
),
0
),
0
) AS stock
FROM
movimiento_almacen ma
LEFT JOIN producto p ON p.id_producto = ma.id_producto
LEFT JOIN act_acta aac ON aac.idacta = ma.idacta
AND aac.is_active = 1
LEFT JOIN act_detalle_acta ada ON ada.idacta = aac.idacta
AND ada.iddetalleacta = ma.iddetalleacta
AND ada.is_active = 1
LEFT JOIN cliente cli ON (
cli.id_cliente = aac.id_cliente
)
AND cli.estado = 1
WHERE
p.estado = '1'
AND (
p.id_producto LIKE '%10253268%'
AND ma.id_producto LIKE '%10253268%'
OR (
p.descripcion LIKE '%10253268%'
OR ma.lote LIKE '%10253268%'
)
)
AND aac.id_cliente = 'CLI00066'
GROUP BY
ma.id_producto,
ma.lote,
ma.serie,
ma.codigo,
ada.fechavencimiento,
ada.rscrsnso
)
UNION
(
SELECT
si.id_producto,
p.descripcion,
IFNULL(si.lote, ''),
IFNULL(si.serie, ''),
IFNULL(si.codigo, ''),
IFNULL(si.rscrsnso, ''),
IFNULL(si.fechavencimiento, ''),
si.stock
FROM
stock_inicial si
LEFT JOIN producto p ON p.id_producto = si.id_producto
LEFT JOIN cliente cli ON (
cli.id_cliente = si.id_cliente
)
AND cli.estado = 1
WHERE
p.estado = '1'
AND si.id_cliente = 'CLI00066'
AND (
p.id_producto LIKE '%10253268%'
AND si.id_producto LIKE '%10253268%'
OR (
p.descripcion LIKE '%10253268%'
OR si.lote LIKE '%10253268%'
)
)
)
) t
GROUP BY
id_producto,
lote,
serie,
codigo,
fechavencimiento,
rscrsnso
ORDER BY
descripcion ASC,
fechavencimiento ASC
And this is the query for the product inventory:
SELECT
id_producto,
descripcion,
lote,
serie,
codigo,
modelo,
rscrsnso,
fechavencimiento,
COALESCE (stockInicials, 0) AS stockInicials,
SUM(ingreso) AS ma_ingreso,
SUM(upu_ingreso)AS upu_ingreso,
SUM(IFNULL(ingreso, upu_ingreso)) AS ingreso,
(COALESCE (SUM(IFNULL(ingreso, upu_ingreso)), 0) + COALESCE (stockInicials, 0)) AS sumaIngreso,
SUM(IFNULL(salida,upu_salida)) as salida,
((COALESCE (SUM(IFNULL(ingreso, upu_ingreso)), 0) + COALESCE (stockInicials, 0)) - COALESCE (SUM(IFNULL(salida,upu_salida)), 0)) AS stockss,
ubicacion,
CONCAT(descripcion,' / Lote:',lote,' / Serie:',serie,' / Codigo:',codigo,' / Modelo:',modelo,' / RS:',rscrsnso,' / Stock:',
((COALESCE (SUM(ingreso), 0) + COALESCE (stockInicials, 0)) - COALESCE (SUM(salida), 0)))AS b_descripcion,
idubicacion,
idacta,
iddetalleacta,
idfabricantes,
desfabricantes,
despresentacion,
idpresentacion,
desmodelos,
idmodelos
FROM
(
SELECT
ada.id_producto,
cli.rasocial AS razonsocial,
cli.ruc_cli AS ruc,
(
SELECT
p.descripcion
FROM
producto p
WHERE
p.id_producto = ada.id_producto
) AS descripcion,
IFNULL(ma.lote, '') AS lote,
IFNULL(ma.serie, '') AS serie,
IFNULL(ma.codigo, '') AS codigo,
IFNULL(ma.idmodelos,'')AS modelo,
IFNULL(ada.rscrsnso, '') AS rscrsnso,
IFNULL(ada.fechavencimiento, '') AS fechavencimiento,
SUM(IF (ma.tip_movimiento = 'INGRESO', upu.cantidad,0)) as upu_ingreso,
SUM(IF (ma.tip_movimiento = 'SALIDA', upu.cantidad,0)) AS upu_salida,
COALESCE (si.stockInicial, 0) AS stockInicials,
SUM(IF (ma.tip_movimiento = 'INGRESO', ma.cantidad,0)) AS ingreso,
SUM(IF (ma.tip_movimiento = 'SALIDA',ma.cantidad,0)) AS salida,
IFNULL(
(
SELECT CONCAT(are.descripcion,' ',upa.descripcion,' ', uni.descripcion,' ', upo.descripcion) AS descripcion
FROM ubi_areas are
LEFT JOIN ubi_pasadisos upa ON upa.idareas = are.idareas
LEFT JOIN ubi_niveles uni ON uni.idpasadisos = upa.idpasadisos
LEFT JOIN ubi_posiciones upo ON upo.idniveles = uni.idniveles
LEFT JOIN ubi_ubicacion ubi ON ubi.idposiciones = upo.idposiciones
LEFT JOIN cliente cli ON cli.id_cliente = ubi.id_cliente
WHERE ubi.idubicacion = upu.idubicacion
),'CUARENTENA'
)AS ubicacion,
upu.idubicacion AS idubicacion,
aac.idacta AS idacta,
ada.iddetalleacta AS iddetalleacta,
af.idfabricantes as idfabricantes,
af.descripcion as desfabricantes,
apre.descripcion as despresentacion,
apre.idpresentacion as idpresentacion,
IFNULL(amo.descripcion,'') as desmodelos,
IFNULL(amo.idmodelos,'') as idmodelos
FROM
movimiento_almacen ma
LEFT JOIN producto p ON p.id_producto = ma.id_producto AND p.estado = 1
LEFT JOIN act_acta aac ON (aac.idacta = ma.idacta)AND aac.is_active = 1
LEFT JOIN act_detalle_acta ada ON ada.idacta = aac.idacta AND ada.iddetalleacta = ma.iddetalleacta AND ada.is_active = 1
LEFT JOIN stock_inicial si ON si.id_producto = ada.id_producto AND si.lote = ada.lote AND si.rscrsnso = ada.rscrsnso AND si.fechavencimiento = ada.fechavencimiento
LEFT JOIN cliente cli ON cli.id_cliente = aac.id_cliente AND cli.estado = 1
LEFT JOIN ubi_producto_ubicacion upu ON upu.iddetalleacta = ada.iddetalleacta
LEFT JOIN aux_fabricantes af ON af.idfabricantes = p.idfabricantes
LEFT JOIN aux_presentacion apre ON p.idpresentacion = apre.idpresentacion
LEFT JOIN aux_modelos amo ON p.idmodelos = ma.idmodelos
WHERE aac.id_cliente = 'CLI00066'
AND(ada.id_producto LIKE '%PF0059%' OR p.descripcion LIKE '%PF0059%')
GROUP BY
ma.id_producto,
ma.lote,
ma.serie,
ma.codigo,
ada.fechavencimiento,
ada.rscrsnso
UNION ALL
SELECT
si.id_producto,
NULL,
NULL,
p.descripcion,
IFNULL(si.lote, '') AS lote,
IFNULL(si.serie, '') AS serie,
IFNULL(si.codigo, '') AS codigo,
IFNULL(si.idmodelos,'')AS modelo,
IFNULL(si.rscrsnso, '') AS rscrsnso,
IFNULL(si.fechavencimiento, '') AS fechavencimiento,
NULL upu_ingreso,
NULL upu_salida,
COALESCE (si.stockInicial, 0) AS stockInicials,
NULL AS ingreso,
NULL AS salida,
'STOCK INICIAL'AS ubicacion,
NULL AS idubicacion,
NULL AS idacta,
NULL AS iddetalleacta,
NULL AS idfabricantes,
NULL AS desfabricantes,
NULL AS despresentacion,
NULL AS idpresentacion,
NULL AS desmodelos,
NULL AS idmodelos
FROM
stock_inicial si
LEFT JOIN producto p ON p.id_producto = si.id_producto
WHERE si.id_cliente = 'CLI00066'
AND(si.id_producto LIKE '%PF0059%' OR p.descripcion LIKE '%PF0059%')
GROUP BY
si.id_producto,
si.lote,
si.serie,
si.codigo,
si.fechavencimiento,
si.rscrsnso
) AS a
GROUP BY
id_producto,
lote,
serie,
codigo,
fechavencimiento,
rscrsnso
ORDER BY
descripcion ASC,
fechavencimiento ASC
LIMIT 25
In the table ubi_producto_ubicacion there is a column called " cantidad " as in the table movimiento_almacen but the column cantidad of the table ubi_producto_ubicacion are at 0 and I do not understand why it alters the SUM() of the amounts of movimiento_almacen .