Hello,
I would like to generate a list of closing stock grouped by year per product. I have used the below SQL query but it is not giving me the correct data as in for example closing stock for year 2018 is correct but for 2019 it isn’t as it is not cumulative. It just gets the movement for 2019 without cumulatively summing up from 2018. Hope it makes sense. I’m using stock movement to determine the closing stock. Kindly if anyone can assist. The below is the SQL query:
SELECT
YEAR(s.datem) AS YEAR,
MONTHNAME(s.datem) AS MONTH,
p.ref AS Item_Code,
p.label AS Item_Description,
sum(s.value) AS Qty,
e.label as Warehouse,
p.cost_price,
sum(s.value*p.cost_price) as Value
FROM
llx_stock_mouvement s
JOIN llx_product p ON
s.fk_product = p.rowid
JOIN llx_product_fournisseur_price pf ON
p.rowid=pf.fk_product
JOIN llx_entrepot e
ON
s.fk_entrepot = e.rowid
GROUP BY p.ref, YEAR(s.datem), s.fk_entrepot
