Help Needed with SQL Query for Inventory Management System

[diana45peters]

Hello Medion Community,

I recently developed a small inventory management system, and I'm encountering some difficulties with optimizing my SQL queries. Specifically, I need assistance with:

• Filtering stock movements by date (e.g., before 31/12/2019).

• Calculating the remaining stock quantities for each article reference.

• Ensuring the query returns a single line per article with the total remaining stock Well Now Urgent Care
  

Here's an example of the SQL query I'm working with:

SELECT 
    reference_article,
    SUM(CASE 
        WHEN type_mouvement = 'achat' THEN quantite 
        WHEN type_mouvement = 'vente' THEN -quantite 
        ELSE 0 
    END) AS quantite_stock
FROM 
    mouvement_stock
WHERE 
    date <= '2019-12-31'
GROUP BY 
    reference_article;

The issue I'm facing is that the query only sums quantities of the same reference and date when I include the date filter. However, when I test without the date parameter, the query works as expected but doesn't function correctly when generating the report.

Any insights or advice on how to resolve this issue or improve the query would be greatly appreciated!

Thank you for your help!

Best regards,
Eva