SUM avec plusieurs variables

Version imprimable

Bonjour,

Je pêche sur quelque chose qui me semblait simple... (je suis plutôt novice sur SQL)

Je cherche à avoir la dernière date d'inventaire d'un arcticle A (il y a plusieurs dates d'inventaire sur l'année). J'utilise donc MAX(date).
Je souhaite aussi savoir si un article Y, il n'y a pas eu d'inventaire, donc que la requête me retour 'vide', mais ca je n'y arrive pas.

La table se décompose ainsi:

Ci dessous ma requête qui fonctionne pour les articles avec inventaire, mais je ne voit pas comment la modifier pour avoir les articles avec et sans inventaires.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT A.partner, A.article, A.movement_date as "Date dernier inventaire", A.quantity as "Quantité Inventaire" FROM "storage_history" AS A INNER JOIN( SELECT partner, article, MAX(movement_date) as MaxDate FROM "storage_history" WHERE "is_inventory" like 'true' GROUP BY partner, article ) tm ON A.partner = tm.partner AND A.article = tm.article AND A.movement_date = tm.MaxDate
Merci pour votre aide ... :-)

23/12/2021, 10h55
escartefigue

Bonjour

Une jointer interne (INNER JOIN) n'est pas utilisable dans ce cas.
Pour récupérer tous les articles, même ceux n'ayant jamais fait l'objet d'inventaire, il faut utiliser une jointure externe (OUTER JOIN).
La syntaxe est la même .
Mais il faut que cette jointure se fasse entre la table des article et celle des inventaires.

J'ai essayé avec ca, mais ca ne fonctionne pas .. ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
		 A.partner,
		B."id",
		 A.movement_date as "Date dernier inventaire",
		 A.quantity as "Quantité Inventaire"
FROM  "storage_history" AS  A
OUTER JOIN(	SELECT
			 partner,
			 id,
			 MAX(movement_date) as MaxDate
	FROM  "storage_history" 
	WHERE	 "is_inventory"  like 'true'
	GROUP BY partner,
		  id 
) tm ON A.partner  = tm.partner
	 AND	B.id  = tm.id
	 AND	A.movement_date  = tm.MaxDate  
JOIN "product_product" AS  B ON B."id" = A.article

-------------------------
Détails de l’erreur :

Citation:

L’analyse syntaxique de la requête a échoué, veuillez vérifier la syntaxe

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
		 B.partner,
		 A."id",
		 B.movement_date as "Date dernier inventaire",
		 B.quantity as "Quantité Inventaire"
FROM  "product_product" AS  A
JOIN "storage_history" AS  B ON A."id"  = B."article" 
JOIN(	SELECT
			 partner,
			 id,
			 MAX(movement_date) as MaxDate
	FROM  "storage_history" 
	WHERE	 "is_inventory"  like 'true'
	GROUP BY partner,
		  id 
) tm ON B.partner  = tm.partner
	 AND	A.id  = tm.id
	 AND	B.movement_date  = tm.MaxDate

23/12/2021, 13h00
tomaTourcoing

Citation:

Envoyé par escartefigue

Bonjour

Une jointer interne (INNER JOIN) n'est pas utilisable dans ce cas.
Pour récupérer tous les articles, même ceux n'ayant jamais fait l'objet d'inventaire, il faut utiliser une jointure externe (OUTER JOIN).
La syntaxe est la même .
Mais il faut que cette jointure se fasse entre la table des article et celle des inventaires.

La requête ne me retourne rien..

Comme indiqué plus haut, il manque une table "ARTICLE" et une table "INVENTAIRE"
en toute logique, le modèle conceptuel devrait être du genre :

[ARTICLE] 0,n --- (inventorier) --- 1,n [INVENTAIRE]

L'article étant caractérisé par sa référence, sa désignation etc.
L'inventaire par une date de début et de fin d'inventaire, un exercice comptable de rattachement, etc.
Et la table issue de l'association "inventorier" sera porteuse de la quantité

Si je me crée le jeu de données suivant :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 with AR_article (AR_ident, AR_ref, AR_lib) as (select 1, 'R65P12E', 'fer à souder 40W' union all select 2, 'AC21002', 'clef plate 12mm' union all select 3, '44-C-20', 'joint plat diam 30' union all select 4, 'A50B01F', 'scie circulaire diam 260' union all select 5, 'R65P14E', 'fer à souder 60W' ) , IV_inventaire (IV_ident, IV_date) as (select 1, '2019-06-30' union all select 2, '2020-06-30' union all select 3, '2021-06-30' ) , AI_art_inv (IV_ident, AR_ident, AI_qte) as (select 1, 1, 040 union all select 1, 2, 060 union all select 1, 3, 440 union all select 1, 4, 012 union all select 2, 1, 022 union all select 2, 2, 081 union all select 2, 4, 010 union all select 3, 2, 077 union all select 3, 4, 015 union all select 3, 5, 040 )
Alors la requête qui suit donne bien la quantité au dernier inventaire, même pour les articles non inventoriés :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 select AR.AR_ref , AR.AR_lib , coalesce(S1.QtInv, 0)as QteInv , (select max(IV_date) from IV_inventaire) as DteInv from AR_article as AR left join (select AR_ident , coalesce(AI_QTE, 0) as Qtinv , IV_date from AI_art_inv as AI inner join IV_inventaire as IV on IV.IV_ident=AI.IV_ident where IV.IV_date= (select max(IV_date) from IV_inventaire ) ) as S1 on S1.AR_ident=AR.AR_ident order by AR.AR_ref
Résultat :

AR_ref AR_lib QteInv DteInv

44-C-20 joint plat diam 30 0 2021-06-30

A50B01F scie circulaire diam 260 15 2021-06-30

AC21002 clef plate 12mm 77 2021-06-30

R65P12E fer à souder 40W 0 2021-06-30

R65P14E fer à souder 60W 40 2021-06-30

Note : pensez à utiliser les balises [CODE] pour encadrer les requêtes, en utilisant l'icone # de la barre d'outils ;)

23/12/2021, 14h19
tomaTourcoing

Je n'ai que 2 tables,

1 avec le la référence de tous les articles,
l'autre avec l'entrepôt, la référence de l'article inventorié (true = inventaire ou false= réception, expédition)
la quantité de chaque mouvement
la date du mouvement (inventaire ou mouvement)

Aussi, je travaille sur une sorte de SQL sur Zoho, je n'ai pas la possibilité de faire 2 select à la suite comme tu l'as fais ... :calim2:
23/12/2021, 14h45
tomaTourcoing

Sinon, il y a moyen de prendre ma première requete qui fait le boulot pour les articles inventorié et ajouter à la suite autre chose pour avoir les articles "non inventorié" ?
23/12/2021, 14h58
escartefigue

Citation:

Envoyé par tomaTourcoing

je n'ai pas la possibilité de faire 2 select à la suite comme tu l'as fais ... :calim2:

Si cette remarque porte sur l'utilisation des CTE (WITH...) dans ma réponse qui précède, alors ça n'a pas d'importance, vous n'avez pas besoin de ces requêtes puisque vous avez les tables chargées. Ces requêtes me permettent de créer un petit jeu d'essai sans créer de vraies tables ;)

Citation:

Envoyé par tomaTourcoing

Sinon, il y a moyen de prendre ma première requete qui fait le boulot pour les articles inventorié et ajouter à la suite autre chose pour avoir les articles "non inventorié" ?

Tout à fait, je suis parti sur 3 tables, mais avec les deux telles que décrites c'est à peu près pareil.
Pour éviter les allers-retours multiples, il est préférable de communiquer la description exacte des deux tables
idéalement le script contenant les ordres CREATE TABLE.
23/12/2021, 15h31
tomaTourcoing

1 pièce(s) jointe(s)

Ci joint la table story.
Il s'agit de faire une somme de toutes les quantity
Groupé par partner (entrepôt) et article (les types de produits)

Si il y a un is_inventory = True, on commence à faire la somme des quantity (pour l'article / entrepôt) à partir de cette date (+ la donnée compté au moment de l'inventaire)
Avec la date et quantité du dernier inventaire à côté de l'article
Si il y a pas d'inventaire, alors date d'inventaire et quantité inventaire = null

Normalement je pense qu'il y a tout ...
Merci beaucoup pour l'aide
23/12/2021, 16h28
tomaTourcoing

Niveau information, c'est + clair ?

AR_ref	AR_lib	QteInv	DteInv
44-C-20	joint plat diam 30	0	2021-06-30
A50B01F	scie circulaire diam 260	15	2021-06-30
AC21002	clef plate 12mm	77	2021-06-30
R65P12E	fer à souder 40W	0	2021-06-30
R65P14E	fer à souder 60W	40	2021-06-30

Si je comprends bien, on veut par entrepot ("partner") et par article, la somme Qté inventaire + Qté mouvements.

Comme la même table contient à la fois des lignes inventaire (lignes dont la colonne is_inventory est vraie) et des lignes mouvements (celles dont is_inventory est faux), il faut faire une autojointure (jointure de la table avec elle même), c'est la solution proposée ci-dessous
Autre solution : utiliser un CASEpour faire la somme des mouvements, mais cette solution ne serait applicable que s'il n'existe qu'une seule date d'inventaire pour un même couple article/entrepôt.

Voici la création du jeu d'essai
je n'ai pris que les première lignes du doc excel, auxquelles j'ai ajouté 2 cas d'inventaires pour un même entrepôt et même article que le mouvement.
et je n'ai conservé que les colonnes utiles au traitement

Code:

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
create table storage_history
            (  partner         integer      not null
             , article         integer      not null
             , movement_date   date         not null
             , quantity        decimal(9,2) not null
             , is_inventory    char(1)      not null
            )
;
insert into storage_history
values (376, 013, '2020-10-05', 001, 'F')
     , (376, 002, '2020-10-05', 014, 'F')
     , (376, 003, '2020-10-05', 002, 'F')
     , (376, 004, '2020-10-05', 002, 'F')
     , (341, 004, '2020-10-05', 001, 'F')
     , (341, 002, '2020-10-05', 014, 'F')
     , (341, 007, '2020-10-05', 001, 'F')
     , (341, 010, '2020-10-05', 001, 'F')
     , (148, 002, '2020-12-17', 003, 'F')
     , (259, 002, '2020-12-07', 000, 'V')
     , (259, 007, '2020-12-07', 000, 'V')
     , (259, 006, '2020-12-07', 000, 'V')
     , (259, 005, '2020-12-07', 000, 'V')
     , (259, 013, '2020-12-07', 000, 'V')
     , (259, 003, '2020-12-07', 000, 'V')
     , (259, 012, '2020-12-07', 000, 'V')
     , (259, 004, '2020-12-07', 000, 'V')
     , (279, 002, '2020-12-09', 003, 'V')
     , (279, 003, '2020-12-09', 001, 'V')
     , (279, 013, '2020-12-09', 000, 'V')
     , (279, 011, '2020-12-09', 000, 'V')
     , (376, 013, '2020-08-06', 005, 'V') -- ligne ajoutée 
     , (376, 002, '2020-08-06', 004, 'V') -- ligne ajoutée
;

Et la requête

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select MVT.partner       as Entrepot
     , MVT.article       as Article
     , sum(MVT.quantity) as Qte
     , coalesce(max(INV.quantity), 0) as Inv
     , sum(MVT.quantity)+coalesce(max(INV.quantity), 0) as Dispo
from storage_history as MVT
left join storage_history as INV
   on INV.partner=MVT.partner
  and INV.article=MVT.article
  and INV.is_inventory='V'
  and not exists
     (select 1
      from storage_history IN2
      where IN2.partner=INV.partner
        and IN2.article=INV.article
        and IN2.movement_date>INV.movement_date
        and IN2.is_inventory='V'
     )
where MVT.is_inventory='F'
group by MVT.partner
       , MVT.article
order by MVT.partner
       , MVT.article

Qui donne :

Entrepot	Article	Qte	Inv	Dispo
148	2	3.00	0.00	3.00
341	2	14.00	0.00	14.00
341	4	1.00	0.00	1.00
341	7	1.00	0.00	1.00
341	10	1.00	0.00	1.00
376	2	14.00	4.00	18.00
376	3	2.00	0.00	2.00
376	4	2.00	0.00	2.00
376	13	1.00	5.00	6.00

Est-ce bien le résultat attendu ?

28/12/2021, 09h51
tomaTourcoing

Bonjour,

Merci pour ta réponse 😊
On veut par entrepot ("partner") et par article, la somme Qté inventaire + Qté mouvements.
-->
On veut par entrepot ("partner") et par article, la somme Qté inventaire + Qté mouvements à partir de la date du dernier inventaire OU si pas de date d'inventaire; la somme Qté mouvements (il est possible qu'il n'y ait pas d'inventaire sur un article).

Quand j'utilise ta requête j'ai un message d'erreur :
"Table ou alias inconnu « INV » utilisé dans la requête Select"

(J'utilise Zoho pour faire mes requêtes, je pense que cet outils a ses limites peut être ... )
28/12/2021, 11h15
escartefigue

Je ne connais pas "Zoho", il faut publier la requête générée par cet outil pour voir ce qui cloche

La requête que j'ai proposée plus haut fonctionne, qu'il y ait ou pas eu d'inventaire pour un article / entrepôt
Et également, s'il y a eu plusieurs inventaires, elle prend en ce cas la valeur de l'inventaire le plus récent pour l'article / entrepôt.
28/12/2021, 13h56
tomaTourcoing

J'ai demandé à leur assistance, j'ai fait une table intermédiaire pour régler le problème; ca fonctionne bien 😊

Merci beaucoup pour le coup de main
28/12/2021, 14h21
escartefigue

Pensez à communiquer la solution qui fonctionne, car elle peut intéresser d'autres personnes.
Pensez aussi à passer le sujet à "résolu" avec le bouton en bas de l'écran si c'est bien le cas ;)

J'ai fait une table intermédiaire "Date derniers inventaires palettes (inter)"

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
		 A.partner,
		 A.article,
		 A.movement_date as "Date dernier inventaire",
		 A.quantity as "Quantité Inventaire"
FROM  "storage_history" AS  A
INNER JOIN(	SELECT
			 partner,
			 article,
			 MAX(movement_date) as MaxDate
	FROM  "storage_history" 
	WHERE	 "is_inventory"  like 'true'
	GROUP BY partner,
		  article 
) tm ON A.partner  = tm.partner
	 AND	A.article  = tm.article
	 AND	A.movement_date  = tm.MaxDate

Ensuite:

Une table qui permet d'avoir les palettes inventoriées ou pas (pour le suivi des inventaires):

Code:

1
2
3
4
5
6
7
8
SELECT DISTINCT
		 A."partner",
		 A."article",
		 B."Date dernier inventaire" as "Date dernier inventaire",
		 B."Quantité Inventaire" as "Quantité Inventaire"
FROM  "storage_history" AS  A
LEFT JOIN "Date derniers inventaires palettes (inter)" AS  B ON B."A.article"  = A."article"
	 AND	B."A.partner"  = A."partner"

Puis enfin une table qui permet d'avoir le stock total depuis le dernier inventaire, la date du dernier inventaire (ou pas):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
		 D.code_relay as "AK",
		 E."name" as "Type palette",
		 B."Date dernier inventaire" as "Date dernier inventaire",
		 B."Quantité Inventaire" as "Quantité Inventaire",
		 coalesce(SUM(A."quantity") + B."Quantité Inventaire", SUM(A."quantity")) as "Stock"
FROM  "storage_history" AS  A
INNER JOIN "partner_partner" AS  D ON A."partner"  = "D"."id" 
INNER JOIN "product_product" AS  E ON A.article  = E."id" 
LEFT JOIN "Liste inventaire palettes" AS  B ON B."A.partner"  = A."partner"
	 AND	B."A.article"  = A.article  
WHERE	 A."movement_date"  > B."Date dernier inventaire"
 OR	B."Date dernier inventaire"  is null
GROUP BY D.code_relay,
	 E."name",
	 B."Date dernier inventaire",
	  B."Quantité Inventaire"