Requête de cumul des stocks

Primus · July 12, 2025, 2:36pm

Bonjour.

Je cherche à cumuler les lignes de stock de produits, comme on le ferait dans une feuille de calcul. J’ai conçu la requête suivante :

SELECT
CAL_ID,
CAL_DATE,
PRO_ID,
PRO_LIB,
ACH_ID,
VEN_ID,
ACH_PUHT,
COALESCE (ACH_QTE, 0) AS ACH_QTE, -- Quantité achetée
ACH_PUHT * ACH_QTE AS ACH_TTL,
VEN_PUHT,
COALESCE (VEN_QTE, 0) AS VEN_QTE, -- Quantité vendue
VEN_PUHT * VEN_QTE AS VEN_TTL,
ACH_QTE - VEN_QTE AS STO_QTE -- Quantité en stock
FROM TR_CALENDRIER C
LEFT OUTER JOIN T_ACHATS A ON C.CAL_ID = A.CAL_ID
RIGHT OUTER JOIN T_VENTES V ON C.CAL_ID = V.CAL_ID
LEFT OUTER JOIN TJ_ACHATS H ON A.ACH_ID = H.ACH_ID
RIGHT OUTER JOIN TJ_VENTES T ON V.VEN_ID = T.VEN_ID
LEFT OUTER JOIN T_PRODUITS P ON H.PRO_ID = P.PRO_ID
RIGHT OUTER JOIN T_PRODUITS P ON T.PRO_ID = P.PRO_ID

Mais cette requête est mal structurée puisqu’elle n’indique l’état du stock que lorsque le même jour, un achat et une vente sont réalisés. Ce que je voudrais, c’est obtenir le résultat comme rajouté en rouge dans la dernière colonne dans la copie d’écran suivante, c’est-à-dire effectuer le cumul ligne par ligne, soit : STO_QTE = STO_QTE de la ligne précédente + ACH_QTE - ACH_VEN :

Merci.

dbPrimus20250712.odb (16,8 Ko)

fpy · July 14, 2025, 8:23am

c’est pas le même problème Requête UNION et cumul ?

Primus · July 14, 2025, 8:46am

Non, dans le lien que tu cites, je cumulais les valeurs de 2 tables aux données identiques avec une requête UNION. Ici il s’agit d’une structure de valorisation d’un stock par la méthode CMUP (coût moyen unitaire pondéré) qui consiste, entre autre, à récupérer le montant de la ligne précédente, auquel on ajoute les entrées moins les sorties de la nouvelle ligne.

Dans un tableur, à supposer que :

Total entrées : colonne D
Total sorties : colonne G
Total stock : colonne J

Par exemple, pour la ligne 4, la formule de J4 est : =J3+D4-G4

fpy · July 14, 2025, 10:02am

ça va reposer le pb des limites de HSQLDB …
→ https://www.reddit.com/r/SQL/comments/11nx57u/cumulative_sum_or_running_sum/?tl=fr
https://openclassrooms.com/forum/sujet/requete-sql-pour-faire-une-somme-progressive

Primus · July 14, 2025, 3:49pm

Le lien d’Openclassrooms m’a permis d’avancer.

J’ai d’abord modifié ma première requête et enregistrée en vue (suppression du prix de vente et du total de vente, inutiles ici) :

CREATE VIEW V_STOCK AS
SELECT
CAL_ID,
CAL_DATE,
PRO_ID,
PRO_LIB,
ACH_ID,
VEN_ID,
ACH_PUHT,
COALESCE (ACH_QTE, 0) AS ACH_QTE,
ACH_PUHT * ACH_QTE AS ACH_TTL,
COALESCE (VEN_QTE, 0) AS VEN_QTE,
ACH_QTE - VEN_QTE AS STO_QTE
FROM TR_CALENDRIER C
LEFT OUTER JOIN T_ACHATS A1 ON C.CAL_ID = A1.CAL_ID
LEFT OUTER JOIN TJ_ACHATS A2 ON A1.ACH_ID = A2.ACH_ID
RIGHT OUTER JOIN T_VENTES V1 ON C.CAL_ID = V1.CAL_ID
RIGHT OUTER JOIN TJ_VENTES V2 ON V1.VEN_ID = V2.VEN_ID
LEFT OUTER JOIN T_PRODUITS P ON A2.PRO_ID = P.PRO_ID
RIGHT OUTER JOIN T_PRODUITS P ON V2.PRO_ID = P.PRO_ID

Ensuite :

SELECT T1.CAL_DATE, T1.PRO_LIB, T1.ACH_PUHT, T1.ACH_QTE, T1.VEN_QTE, SUM(T2.ACH_QTE - T2.VEN_QTE) AS STO_QTE
FROM V_STOCK T1
LEFT OUTER JOIN V_STOCK T2 ON T1.PRO_LIB = T2.PRO_LIB AND T1.CAL_DATE >= T2.CAL_DATE
GROUP BY T1.CAL_DATE, T1.PRO_LIB, T1.ACH_PUHT, T1.ACH_QTE, T1.VEN_QTE
ORDER BY T1.CAL_DATE, T1.PRO_LIB

J’obtiens bien le cumul des quantités achetées et vendues comme dans une feuille de calcul, le but final étant d’obtenir ceci :

C’est-à-dire, le cumul des montants du stock et le prix d’achat corrigé afin d’obtenir la valorisation du stock.

Merci déjà pour le lien, je vais voir si j’y arrive assez facilement…

Primus · July 17, 2025, 4:21pm

Je repars presque depuis le début puisque je viens de m’apercevoir que cette requête de base retournait des résultats “incorrects” :

SELECT
CAL_ID,
CAL_DATE,
PRO_ID,
PRO_LIB,
ACH_ID,
VEN_ID,
ACH_PUHT,
COALESCE (ACH_QTE, 0) AS ACH_QTE,
ACH_PUHT * ACH_QTE AS ACH_TTL,
COALESCE (VEN_QTE, 0) AS VEN_QTE
FROM TR_CALENDRIER C
LEFT OUTER JOIN T_ACHATS A1 ON C.CAL_ID = A1.CAL_ID
LEFT OUTER JOIN TJ_ACHATS A2 ON A1.ACH_ID = A2.ACH_ID
RIGHT OUTER JOIN T_VENTES V1 ON C.CAL_ID = V1.CAL_ID
RIGHT OUTER JOIN TJ_VENTES V2 ON V1.VEN_ID = V2.VEN_ID
LEFT OUTER JOIN T_PRODUITS P ON A2.PRO_ID = P.PRO_ID
RIGHT OUTER JOIN T_PRODUITS P ON V2.PRO_ID = P.PRO_ID

Dans les tables, il y a le 01-01-2025 un achat et une vente. Dans cette requête, ces deux lignes s’affichent. Mais, en testant, j’ai supprimé la vente ; l’achat n’est plus retourné par la requête. Pourtant, la jointure externe gauche est censée afficher tous les enregistrements de C et uniquement les enregistrements de A1 pour lesquels le contenu des champs liés des deux tables est identique, pour citer le message indiqué dans la propriété de la jointure. Comme il y a bien un enregistrement de A1 à cette date, pourquoi ne s’affiche-t-il pas ? A contrario, les lignes de ventes correspondant aux dates sans achat s’affichent normalement…

fpy · July 17, 2025, 6:59pm

je ne sais pas qui à part @Villeroy aura la motivation pour décortiquer ce truc

valioud · July 15, 2025, 6:12am

Il y a une gestion de stocks déjà développée ici :
https://beaussier.com/sections/viewtopic.php?p=3323

Primus · July 17, 2025, 10:21am

J’avais déjà jeté un oeil sur cette base, complexe, et qui ne répond pas à ce que j’attends.

Le souci principal c’est le stock initial que je ne sais pas comment définir. J’ai essayé d’intégrer les valeurs initiales (quantité, CMUP et montant total) dans la table des produits mais sans plus. Le besoin initial est de calculer le nouveau CMUP (après chaque achat) avec cette formule :

CMUP = (Montant du stock initial + Montant de l'entrée) / (Quantité du stock initial + Quantité de l'entrée)

Et naturellement, les valeurs du stock initial ne sont utilisées que pour le premier achat (et/ou) vente puisqu’ensuite, ce sont les nouvelles valeurs qui sont effectives, après mise à jour du stock. Pour les quantités c’était facile, mais pour les montants c’est autre chose.