Calcul de quantité en stock

**Zeus_Appolos** · 26/02/2019, 21h17

Bonsoir Tous, soyez béni, sans vous je suis inutile

je souhaite calculer la quantité en stock de mes matériels. mais avec la requête que j'ai faite, les sommes des quantités sont doublés. vraiment c'est bizare

Mes tables

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
/*La tale RECEPTION / ENTREE */
CREATE TABLE RECEPTION (
    CommandeId  			INT  			NOT NULL,
    ReceptionId 			INT  			NOT NULL auto_increment, 
    ReceptionDate  			DATE 		NOT NULL,
    Observation				VARCHAR(150) 	NOT NULL,
    ModifieLe 				DATE 		NOT NULL,
    ModifieA						TIME 			NOT NULL,
    ModifiePar				VARCHAR(50) 	NOT NULL,
    CONSTRAINT RECEPTION_PK PRIMARY KEY (ReceptionId),
    CONSTRAINT RECEPTION_COMMANDE_FK FOREIGN KEY (CommandeId) REFERENCES COMMANDE_FOURNISSEUR(CommandeId) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;
 
/*La tale LIGNE_RECEPTION */
CREATE TABLE LIGNE_RECEPTION (
    LigneReceptionId  			INT  			NOT NULL auto_increment, 
    ReceptionQuantite    	DECIMAL(6,2)   	NOT NULL,
    FormeId         		INT 			NOT NULL ,
    MaterielId   			INT 				NOT NULL,
    ReceptionId				INT 				NOT NULL,
    CONSTRAINT RECEPTION_PK PRIMARY KEY (LigneReceptionId),
    CONSTRAINT RECEPTION_LIGNE_MATERIEL_FK FOREIGN KEY (MaterielId) REFERENCES MATERIEL (MaterielId) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT RECEPTION_LIGNE_RECEPTION_FK FOREIGN KEY (ReceptionId) REFERENCES RECEPTION(ReceptionId) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT RECEPTION_LIGNE_FORME_FK FOREIGN KEY (FormeId) REFERENCES FORME(FormeId) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;
 
/*La tale SORTIE / AFFECTATION */
CREATE TABLE SORTIE (
    SortieId 				INT  			NOT NULL auto_increment, 
    SortieDate  			DATE 		NOT NULL,
    Observation				VARCHAR(150) 	NOT NULL,
    SortieMotif				VARCHAR(50) 	NOT NULL,
    ServiceId         		INT ,
    ModifieLe 				DATE 		NOT NULL,
    ModifieA						TIME 			NOT NULL,
    ModifiePar				VARCHAR(50) 	NOT NULL, 
    CONSTRAINT SORTIE_PK PRIMARY KEY (SortieId),
    CONSTRAINT SORTIE__SERVICE_FK FOREIGN KEY (ServiceId) REFERENCES SERVICES (ServiceId) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;
 
/*La tale LIGNE SORTIE / DOTATION*/
CREATE TABLE LIGNE_SORTIE (
    LigneSortieId 				INT  			NOT NULL auto_increment, 
    SortieQuantite    		DECIMAL(6,2)   	NOT NULL,
    FormeId         		INT 			NOT NULL,
    MaterielId   			INT 			NOT NULL, 
    CONSTRAINT LIGNE_SORTIE_PK PRIMARY KEY (LigneSortieId),
    CONSTRAINT LIGNE_SORTIE_FORME_FK FOREIGN KEY (FormeId) REFERENCES FORME (FormeId) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT LIGNE_SORTIE_MATERIEL_FK FOREIGN KEY (MaterielId) REFERENCES MATERIEL (MaterielId) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;

ma requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT DISTINCT(t1.MaterielId),t1.MaterielLibelle, COALESCE(SUM(t1.ReceptionQuantite),0) som_qte_recu, COALESCE(SUM(t2.SortieQuantite),0) som_qte_sortie, COALESCE(SUM(t1.ReceptionQuantite),0)-COALESCE(SUM(t2.SortieQuantite),0) qte_en_stock
FROM
(
    SELECT lr.MaterielId, m.MaterielLibelle, lr.ReceptionQuantite
    from ligne_reception lr 
    INNER JOIN materiel m ON lr.MaterielId=m.MaterielId
) t1
LEFT OUTER JOIN
(
    SELECT ls.MaterielId,m.MaterielLibelle, ls.SortieQuantite, s.SortieDate
    FROM ligne_sortie ls
    INNER JOIN sortie s ON ls.SortieId=s.SortieId
    INNER JOIN materiel m ON m.MaterielId=ls.MaterielId
) t2 ON t2.MaterielId = t1.MaterielId
GROUP BY t1.MaterielId,t1.MaterielLibelle

mes écrans sont joints

Merci de m'aider

**Zeus_Appolos** · 26/02/2019, 23h49

est-ce possible de suivre l'execution de la requete select ligne par ligne ? si oui comment ? si non y a t'il une autre possibilité?

**escartefigue** · 27/02/2019, 09h37

Bonjour

Comme ça :
j'ai simplifié le nom des tables et des colonnes pour mes tests
TMAT ((MAIDT, MALIB) matériel
TREC(REIDT, REQTE, MAIDT lignes réceptions
TSOR(SOIDT, SOQTE, MAIDT) lignes sorties

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select MA.MAIDT
     , MA.MALIB
     , coalesce(SR.QTCUM, 0) as CUMENT
     , coalesce(SS.QTCUM, 0) as CUMSOR
     , coalesce(SR.QTCUM, 0) - coalesce(SS.QTCUM,0) as STOCK
from TMAT as MA
left join (select MAIDT
                , sum(REQTE) as QTCUM
           from TREC
           group by MAIDT) 
     as SR
  on SR.MAIDT=MA.MAIDT         
left join (select MAIDT
                , sum(SOQTE) as QTCUM
           from TSOR
           group by MAIDT) 
     as SS         
  on SS.MAIDT=MA.MAIDT

Mais votre stock n'a pas de sens : il faut tenir compte de la date et donc ajouter une jointure avec les tables RECEPTION et SORTIE, à vous de faire l'adaptation nécessaire

**Zeus_Appolos** · 27/02/2019, 14h54

Merci beaucoup escartefigue, votre methode marche bien. Je n'ai pas pensé aux groupements internes. merci bien

concernant la date j'y ai pensé.
ma methode est que j'ai ajouter un having (date=current_date()) les group by pour faire le cumul du jour.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
select m.MaterielId
         , m.MaterielLibelle
         , coalesce(SR.QTCUMRECU, 0) as CUMRECU
         , coalesce(SS.QTCUMSORTIE, 0) as CUMSOR
         , coalesce(SR.QTCUMRECU, 0) - coalesce(SS.QTCUMSORTIE,0) as STOCK
    from materiel as m
    left join 
    (
        select lr.MaterielId, r.ReceptionDate
                    , sum(lr.ReceptionQuantite) as QTCUMRECU
               from ligne_reception lr
               inner join reception r ON lr.ReceptionId=r.ReceptionId
               group by lr.MaterielId, r.ReceptionDate
        	   HAVING r.ReceptionDate=NOW()
 
    ) SR
    on SR.MaterielId=m.MaterielId        
    left join 
    (
        select ls.MaterielId, s.SortieDate
                    , sum(ls.SortieQuantite) as QTCUMSORTIE
               from ligne_sortie ls
               inner join sortie s ON ls.SortieId=s.SortieId
               group by ls.MaterielId, s.SortieDate
               HAVING s.SortieDate=CURRENT_DATE()
    ) SS         
    on SS.MaterielId=m.MaterielId

ensuite pour le calcul du stock actuelle, je possede une table STOCK

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE STOCK (
    MaterielId   			INT 			NOT NULL, 
    QuantiteEnstock    		DECIMAL(6,2)   	NOT NULL,
    QuantiteStockVirtuel   	DECIMAL(6,2)   	NOT NULL,
    Observation				VARCHAR(50) 	NOT NULL,
    FormeId         		INT 			NOT NULL ,
    ModifieLe 				DATE 		NOT NULL,
    ModifieA						TIME 			NOT NULL,
    ModifiePar				VARCHAR(50) 	NOT NULL,
    CONSTRAINT STOCK_PK PRIMARY KEY (MaterielId),
    CONSTRAINT STOCK_FORME_FK FOREIGN KEY (FormeId) REFERENCES FORME (FormeId) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;

je souhaite recupérer la date de derniere MAJ(ModifieLe) du materiel et sa quantité en stock (QuantiteEnstock) dans la table STOCK

puis je compare cette date aux dates ReceptionDate et SortieDate tel que ModifieLe<SortieDate; ModifieLe<ReceptionDate

si oui QuantiteEnstock=QuantiteEnstock+ReceptionQte (reception matériel) et QuantiteEnstock=QuantiteEnstock-SortieQte

Est-ce une bonne methode par rapport aux dates en liaison avec le stock actuelle ? votre avis et conseils

**escartefigue** · 27/02/2019, 15h43

La nouvelle quantité en stock Q1 c'est (Q0 + entrées> date de Q0 - sorties> date de Q0) avec Q0 = stock initial
Donc votre clause HAVING ne va pas sauf si Q0 est systématiquement réévalué tous les jours. C'est donc risqué

Il est préférable d'extraire toutes les entrées et toutes les sorties dont la date de mouvement est supérieure à la date de calcul du stock initial Q0
il faut donc supprimer la colonne date du SELECT et du GROUP BY dans les tables dérivées SR et SS, elle ne vous sert à rien et va vous donner un résultat par date qui ne vous sert à rien
Remplacez la clause HAVING par un filtre WHERE (puisque ça ne dépend pas du regroupement) pour vérifier que la date du mouvement est supérieure à la date de calcul de Q0

De plus, il est dangereux d'utiliser des fonctions telles que now() (non standard SQL) ou current_date (standard), car un traitement doit pouvoir être déclenché en différé (par exemple en cas de plantage du batch la nuit et reprise le lendemain). C'est pourquoi il est recommandé d'utiliser une variable

**Zeus_Appolos** · 27/02/2019, 17h12

Merci beaucoup escartefigue, après vos conseils si je vous comprend bien, voici la maquette pour calculer le stock actuelle mais elle ne marche pas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
select m.MaterielId
         , m.MaterielLibelle
         , coalesce(SR.QTCUMRECU, 0) as CUMRECU
         , coalesce(SS.QTCUMSORTIE, 0) as CUMSOR
         , coalesce(St.QuantiteEnstock, 0)+(coalesce(SR.QTCUMRECU, 0) - coalesce(SS.QTCUMSORTIE,0)) as STOCK
    from materiel as m
    left join 
    (
        select lr.MaterielId
                    , sum(lr.ReceptionQuantite) as QTCUMRECU
               from ligne_reception lr
               inner join reception r ON lr.ReceptionId=r.ReceptionId
        	   inner join stock st on st.MaterielId=lr.MaterielId
        	   where r.ReceptionDate >= st.ModifieLe and r.ReceptionDate='2019-02-27'
               group by lr.MaterielId, r.ReceptionDate
 
    ) SR
    on SR.MaterielId=m.MaterielId        
    left join 
    (
        select ls.MaterielId
                    , sum(ls.SortieQuantite) as QTCUMSORTIE
               from ligne_sortie ls
        	   inner join stock st on st.MaterielId=ls.MaterielId
        	   inner join sortie s on s.SortieId=ls.SortieId
        	   where s.SortieDate >= st.ModifieLe and s.SortieDate='2019-02-27'
               group by ls.MaterielId, s.SortieDate
    ) SS         
    on SS.MaterielId=m.MaterielId

erreur affichée moi je ne vois pas d'erreur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Erreur
Requête SQL :  Documentation
 
MySQL a répondu: Documentation
 
#1054 - Champ 'st.QuantiteEnstock' inconnu dans field list

**Artemus24** · 28/02/2019, 15h49

Salut Zeus_Appolos.

La fonction now() de mysql, vous donne la date du jour quand vous lancez votre batch.
Autrement dit, si vous lancez votre batch le 31 décembre 2018, vous aurez l'évaluation de vos stocks à cette date.
Si vous désirez effectuer la même opération, c'est-à-dire l'évaluation de vos stockes de fin d'année, mais en lançant le batch disons le 15 janvier 2019, vous aurez l'évaluation au 15 janvier 2019 et non au 31 décembre 2018.

De ce fait, vous devez remplacer la fonction now() par une variable qui correspondra à votre date d'arrêté (un paramètre à saisir), même si le batch sera lancé à une autre date.
Et donc, cette variable devra être gérée indépendamment du batch, par exemple, dans une table des paramètres.

@+

**Zeus_Appolos** · 28/02/2019, 20h56

merci Artemus24 je comprend bien maintenant.
Avant tout je dis merci d'abord à escartefigue, oui sans son aide je ne serais pas à ce niveau.

Après des insertions dans mes tables réception et sortie de matériel, les quantités en stock sont dupliquées, je n'y comprend rien alors que les cumules(somme entrée et sorties) sont exacts. voir écrans c-joints.

stock initial
quantités entrées (première entrée)
stock après première entrée
quantités entrées (deuxième entrée)

Je suis bloqué, besoin d'un coup de main

mon code sql

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
	select m.MaterielId MaterielId
		, m.MaterielLibelle MaterielLibelle
		, coalesce(SR.QTCUMRECU, 0) as CUMRECU
		, coalesce(SS.QTCUMSORTIE, 0) as CUMSOR
		, st.QuantiteEnstock STOCK_INITIAL
		, coalesce(st.QuantiteEnstock, 0)+(coalesce(SR.QTCUMRECU, 0) - coalesce(SS.QTCUMSORTIE,0)) as STOCK_ACTUEL
		,SR.FormeId FormeId
		,SR.FormeUnite FormeUnite
	from materiel as m
	left outer  join stock st on st.MaterielId=m.MaterielId
	left join 
	(
		select lr.MaterielId
		, sum(lr.ReceptionQuantite) as QTCUMRECU
		, fm.FormeId
		, fm.FormeUnite
		from ligne_reception lr
		inner join forme fm on fm.FormeId=lr.FormeId
		inner join reception r ON lr.ReceptionId=r.ReceptionId
		inner join stock st on st.MaterielId=lr.MaterielId
		where r.ReceptionDate >= st.ModifieLe and r.ReceptionDate=%1
		group by lr.MaterielId, fm.FormeUnite
	) SR
	on SR.MaterielId=m.MaterielId        
	left join 
	(
		select ls.MaterielId
		, sum(ls.SortieQuantite) as QTCUMSORTIE
		,fm.FormeId
		, fm.FormeUnite
		from ligne_sortie ls
		inner join forme fm on fm.FormeId=ls.FormeId
		inner join stock st on st.MaterielId=ls.MaterielId
		inner join sortie s on s.SortieId=ls.SortieId
		where s.SortieDate >= st.ModifieLe and s.SortieDate=%1
		group by ls.MaterielId, fm.FormeUnite
	) SS         
	on SS.MaterielId=m.MaterielId

**escartefigue** · 01/03/2019, 09h27

Envoyé par Zeus_Appolos

merci Artemus24 je comprend bien maintenant.
Avant tout je dis merci d'abord à escartefigue, oui sans son aide je ne serais pas à ce niveau.

En ce cas n'hésitez pas à approuver

les messages qui ont pu vous aider

**Zeus_Appolos** · 01/03/2019, 10h03

comment l'approuver ? je ne maîtrise pas bien le forum

**escartefigue** · 01/03/2019, 12h58

Il y a deux icones en bas à droite de chaque réponse

- le pouce vert, orienté vers le haut pour appouver une intervention que vous trouvez pertinente
- celui de couleur rouge et orienté vers le bas, pour marquer les interventions avec lesquelles vous êtes en désacord, en ce cas, il est intéressant d'argumenter ce désaccord

Vous ne pouvez évidemment pas voter pour vos propres interventions

Calcul de quantité en stock

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager