Bonjour les internautes,

Pour commencer, mon environnement de production est une base de données DB2 mais l'environnement sur lequel je fais mes tests et qui concerne ce post est un serveur MySQL 8.0 hébergé en local sur ma machine.

Voici le contenu de ma base de données (version simplifiée de la DB de prod).

Création DB
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
 
DROP DATABASE IF EXISTS DBTEST;
 
CREATE DATABASE DBTEST CHARACTER SET 'utf8';
 
USE DBTEST;
Création 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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
CREATE TABLE IF NOT EXISTS ART -- Articles
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	REF VARCHAR(50) NOT NULL, -- Référence article
	DES VARCHAR(50), -- Description article
	TIERS VARCHAR(50), -- Fournisseur habituel
	PRIMARY KEY (ID)
) ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS SART -- Sous-références articles
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	REF VARCHAR(50) NOT NULL, -- Référence article
	SREF VARCHAR(50), -- Sous-référence article
	CR NUMERIC(20, 4), -- Coût de revient
	PRIMARY KEY (id)
) ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS TFO -- Tarif achat
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	REF VARCHAR(50) NOT NULL, -- Référence article
	SREF VARCHAR(50), -- Sous-référence article
	ETB VARCHAR(50), -- Etablissement
	TIERS VARCHAR(50), -- Fournisseur
	QTE INT, -- Quantité seuil d'application du tarif
	TADT DATE, -- Date d'application du tarif
	PA NUMERIC(20, 4), -- Prix d'achat net
	PRIMARY KEY (ID)
) ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS RFO -- Condition fournisseur
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	REF VARCHAR(50) NOT NULL, -- Référence article
	SREF VARCHAR(50), -- Sous-référence article
	ETB VARCHAR(50), -- Etablissement
	TIERS VARCHAR(50) NOT NULL, -- Fournisseur
	COECOD NUMERIC(20, 4), -- Pourcentage de coûts supplémentaires sur PA
	PRIMARY KEY (id)
) ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS T019 -- Description sous-références articles
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	SREF VARCHAR(50) NOT NULL, -- Sous-référence article
	DES VARCHAR(50), -- Description sous-référence article
	PRIMARY KEY (id)
) ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS ETS -- Etablissements
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	ETB VARCHAR(50) NOT NULL, -- Etablissement
	DES VARCHAR(50), -- Nom établissement
	PRIMARY KEY (ID)
) ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS FOU -- Fournisseurs
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	TIERS VARCHAR(50) NOT NULL, -- Fournisseur
	NOM VARCHAR(50), -- Nom fournisseur
	PRIMARY KEY (ID)
) ENGINE = INNODB;
Population 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
51
52
53
54
55
56
57
58
59
 
INSERT INTO ART
VALUES
(NULL, 'A', 'Article A', 'F1'),
(NULL, 'B', 'Article B', 'F2'),
(NULL, 'C', 'Article C', 'F3'),
(NULL, 'D', 'Article D', 'F4'),
(NULL, 'E', 'Article E', 'F1');
 
INSERT INTO SART
VALUES
(NULL, 'A', '', 1),
(NULL, 'B', 'SR1', 10),
(NULL, 'B', 'SR2', 11),
(NULL, 'B', 'SR3', 12),
(NULL, 'C', '', 20),
(NULL, 'D', '', 30);
(NULL, 'E', 'SR1', 1),
(NULL, 'E', 'SR2', 2),
(NULL, 'E', 'SR3', 3);
 
INSERT INTO RFO
VALUES
(NULL, 'E', '', '', 'F1', 1),
(NULL, 'E', 'SR2', '', 'F1', 2),
(NULL, 'E', 'SR2', 'E1', 'F1', 3),
(NULL, 'E', '', 'E3', 'F1', 4);
 
INSERT INTO TFO
VALUES
(NULL, 'E', '', '', 'F1', 0, '2019-01-01', 1),
(NULL, 'E', '', '', 'F1', 0, '2020-10-01', 1),
(NULL, 'E', 'SR1', '', 'F1', 0, '2020-10-01', 2),
(NULL, 'E', 'SR1', 'E1', 'F1', 0, '2020-10-01', 3),
(NULL, 'E', 'SR1', 'E1', 'F1', 10, '2020-10-01', 4),
(NULL, 'E', '', 'E2', 'F1', 0, '2020-10-01', 5),
(NULL, 'E', '', 'E2', 'F1', 20, '2020-10-01', 6),
(NULL, 'E', '', '', 'F1', 30, '2020-10-01', 7);
 
INSERT INTO T019
VALUES
(NULL, 'SR1', 'Sous-Ref 1'),
(NULL, 'SR2', 'Sous-Ref 2'),
(NULL, 'SR3', 'Sous-Ref 3');
 
INSERT INTO ETS
VALUES
(NULL, 'E1', 'LOG'), -- Plateforme Logistique
(NULL, 'E2', 'FLO'), -- Magasin
(NULL, 'E3', 'COG'), -- Magasin
(NULL, 'E4', 'DIN'); -- Magasin
 
INSERT INTO FOU
VALUES
(NULL, 'F1', 'Fournisseur 1'),
(NULL, 'F2', 'Fournisseur 2'),
(NULL, 'F3', 'Fournisseur 3'),
(NULL, 'F4', 'Fournisseur 4'),
(NULL, 'F5', 'LOG'); -- Plateforme Logistique en tant que fournisseur des autres établissements
Quelques explications s'imposent

Un article peut être géré en sous-référence ou non.
Par exemple, un pantalon peut être sous-référencé par taille.
Un article, qu'il soit géré en sous-référence ou non, a toujours une entrée dans la table SART. Car c'est dans cette table qu'est enregistré, entre autres, le CR de l'article (coût de revient calculé à des fins de valorisation de stock)
- S'il n'est pas géré en sous-référence, le champ SREF est une chaine de caractère vide (et pas NULL).
- S'il est géré en sous-référence, il y a une entrée pour chaque sous-référence représentée par le champ SREF et aucune entrée avec le champ SREF vide.

La table RFO contient des conditions d'achat fournisseur, ici COECOD, un coût en pourcentage à appliquer sur le tarif d'achat d'un article, qui dépendent de :
- L'article (ou la sous-référence de l'article)
- Notre établissement (magasin)
- Le fournisseur (un article peut avoir plusieurs fournisseurs)
IMPORTANT :
- Si le champ ETB est vide (et pas NULL encore une fois), alors cette entrée est valable pour tous les établissements.
- De même, si le champ SREF est vide, cette entrée est valable pour toutes les sous-références d'un article, s'il en a.
- Si ces deux champs sont vides, l'entrée est valable aussi bien pour tous les établissements que toutes les sous-références de l'article.
- Les champs REF et TIERS ne peuvent jamais être vides.

La table TFO contient les tarifs d'achat des article, simplifié ici par PA, le prix d'achat net, qui dépendent de :
- L'article (ou une sous-ref)
- Un établissement
- Un fournisseur
- Une quantité seuil d'achat à atteindre pour que ce tarif soit valable
Mêmes remarques que pour RFO :
- Si le champ ETB est vide, alors cette entrée est valable pour tous les établissements.
- De même, si le champ SREF est vide, cette entrée est valable pour toutes les sous-références d'un article, s'il en a.
- Si ces deux champs sont vides, l'entrée est valable aussi bien pour tous les établissements que toutes les sous-références de l'article.
- Le champ TIERS peut être vide puisqu'en théorie un tarif d'achat peut être valable pour tous les fournisseurs mais en pratique il ne l'est jamais.

Ma requête

A partir de là, j'aimerais obtenir :
- Le CR,
- Le COECOD,
- Le PA
pour chaque "cas possible" pour chaque sous-référence. Mais uniquement pour les derniers tarifs en date.
Afin, par après, de calculer la différence entre le CR et le PA majoré du COECOD : CR - (PA * (1 + COECOD))

J'ai volontairement créé l'article "E" avec des valeurs pour certaines sous-ref dans TFO mais uniquement une valeur par défaut dans RFO et vice versa. Et de-même pour les établissements. Il y a également un tarif antérieur expiré. Afin de couvrir un maximum de cas de figure.
Je vous épargne mes autres tests sur les articles A à D.

Dans cet exemple :
La sous référence SR1 dispose de son propre tarif d'achat.
Cette même sous-référence dispose d'un tarif spécial pour l'établissement 1 et dispose d'un tarif quantitatif sur cet établissement pour une quantité seuil de 10 unités.
L'établissement 2 dispose de son propre tarif d'achat pour toutes les sous-références de l'article E et il y a un tarif quantitatif à partir de 20 unités sur cet établissement.
Toutes les sous-référence de cet article ont un tarif quantitatif spécial à partir de 30 unités sur tous les établissements.
La SR2 a ses propres conditions fournisseurs, dont une spéciale valable pour l'établissement 1.
L'établissement 3 a ses propres conditions fournisseur pour toutes les sous-références de l'article E.

Puisque j'ai plus de mal de vous expliquer le résultat que je souhaite que vous le montrer, voici le résultat attendu, si mes calculs sont bons :

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
+-----+------+-----+-------+-----+------------+--------+--------+--------+----------+
| REF | SREF | ETB | TIERS | QTE | TADT       | CR     | COECOD | PA     | POSITION |
+-----+------+-----+-------+-----+------------+--------+--------+--------+----------+
| E   | SR1  |     | F1    |   0 | 2020-10-01 | 1.0000 | 1.0000 | 2.0000 |        1 |
| E   | SR1  | E1  | F1    |   0 | 2020-10-01 | 1.0000 | 1.0000 | 3.0000 |        1 |
| E   | SR1  | E1  | F1    |  10 | 2020-10-01 | 1.0000 | 1.0000 | 4.0000 |        1 |
| E   | SR1  | E2  | F1    |   0 | 2020-10-01 | 1.0000 | 1.0000 | 5.0000 |        1 |
| E   | SR1  | E2  | F1    |  20 | 2020-10-01 | 1.0000 | 1.0000 | 6.0000 |        1 |
| E   | SR1  |     | F1    |  30 | 2020-10-01 | 1.0000 | 1.0000 | 7.0000 |        1 |
| E   | SR1  | E3  | F1    |   0 | 2020-10-01 | 1.0000 | 4.0000 | 2.0000 |        1 |
| E   | SR1  | E3  | F1    |  30 | 2020-10-01 | 1.0000 | 4.0000 | 2.0000 |        1 |
| E   | SR2  |     | F1    |   0 | 2020-10-01 | 2.0000 | 2.0000 | 1.0000 |        1 |
| E   | SR2  |     | F1    |  30 | 2020-10-01 | 2.0000 | 2.0000 | 7.0000 |        1 |
| E   | SR2  | E2  | F1    |   0 | 2020-10-01 | 2.0000 | 2.0000 | 5.0000 |        1 |
| E   | SR2  | E2  | F1    |  20 | 2020-10-01 | 2.0000 | 2.0000 | 6.0000 |        1 |
| E   | SR2  | E3  | F1    |   0 | 2020-10-01 | 2.0000 | 4.0000 | 1.0000 |        1 |
| E   | SR2  | E3  | F1    |  30 | 2020-10-01 | 2.0000 | 4.0000 | 7.0000 |        1 |
| E   | SR2  | E1  | F1    |   0 | 2020-10-01 | 2.0000 | 3.0000 | 1.0000 |        1 |
| E   | SR2  | E1  | F1    |  30 | 2020-10-01 | 2.0000 | 3.0000 | 7.0000 |        1 |
| E   | SR3  |     | F1    |   0 | 2020-10-01 | 3.0000 | 1.0000 | 1.0000 |        1 |
| E   | SR3  |     | F1    |  30 | 2020-10-01 | 3.0000 | 1.0000 | 7.0000 |        1 |
| E   | SR3  | E2  | F1    |   0 | 2020-10-01 | 3.0000 | 1.0000 | 5.0000 |        1 |
| E   | SR3  | E2  | F1    |  20 | 2020-10-01 | 3.0000 | 1.0000 | 6.0000 |        1 |
| E   | SR3  | E3  | F1    |   0 | 2020-10-01 | 3.0000 | 4.0000 | 1.0000 |        1 |
| E   | SR3  | E3  | F1    |  30 | 2020-10-01 | 3.0000 | 4.0000 | 7.0000 |        1 |
+-----+------+-----+-------+-----+------------+--------+--------+--------+----------+
Mon SELECT
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
 
SELECT
T1.REF AS REF,
T1.SREF AS SREF,
T1.ETB AS ETB,
T1.TIERS AS TIERS,
T1.QTE AS QTE,
T1.TADT AS TADT,
COALESCE(RFO.COECOD, RFO2.COECOD, RFO3.COECOD) AS COECOD,
SART.CR AS COUTREVIENT,
T1.PA AS PA,
T1.POSITION AS POSITION
 
FROM
(
	SELECT
	ROW_NUMBER() OVER(PARTITION BY TFO.REF, TFO.SREF, TFO.ETB, TFO.TIERS, TFO.QTE ORDER BY TFO.TADT DESC) AS POSITION,
 	TFO.ID,
 	TFO.TIERS,
 	TFO.ETB,
 	TFO.REF,
 	TFO.SREF,
 	TFO.TADT,
 	TFO.QTE,
 	TFO.PA
 
	FROM
	TFO
) AS T1
 
LEFT JOIN
RFO
ON 
(
	T1.REF = RFO.REF
	AND T1.SREF = RFO.SREF
	AND T1.TIERS = RFO.TIERS
	AND T1.ETB = RFO.ETB
)
 
LEFT JOIN
ART
ON (T1.REF = ART.REF)
 
LEFT JOIN
SART
ON (T1.REF = SART.REF
	AND T1.SREF = SART.SREF)
 
LEFT JOIN
RFO AS RFO2
ON
(
	T1.REF = RFO2.REF
	AND T1.SREF = RFO2.SREF
	AND T1.TIERS = RFO2.TIERS
	AND RFO2.ETB = ''
)
 
LEFT JOIN
RFO AS RFO3
ON
(
	T1.REF = RFO3.REF
	AND T1.SREF = ''
	AND T1.TIERS = RFO3.TIERS
	AND RFO2.ETB = RFO3.ETB
)
 
LEFT JOIN
RFO AS RFO4
ON
(
	T1.REF = RFO4.REF
	AND T1.SREF = ''
	AND T1.TIERS = RFO4.TIERS
	AND RFO2.ETB = ''
)
 
WHERE
T1.POSITION = 1
AND T1.REF = 'E'
 
UNION
 
SELECT
RFO.REF AS REF,
RFO.SREF AS SREF,
RFO.ETB AS ETB,
RFO.TIERS AS TIERS,
COALESCE(T1.QTE, T2.QTE, T3.QTE, T4.QTE) AS QTE,
COALESCE(T1.TADT, T2.TADT, T3.TADT, T4.TADT) AS TADT,
SART.CR AS CR,
RFO.COECOD AS COECOD,
COALESCE(T1.PA, T2.PA, T3.PA, T4.PA) AS PA,
COALESCE(T1.POSITION, T2.POSITION, T3.POSITION, T4.POSITION) AS POSITION
 
FROM
RFO
 
LEFT JOIN
(
	SELECT
	ROW_NUMBER() OVER(PARTITION BY TFO.REF, TFO.SREF, TFO.ETB, TFO.TIERS, TFO.QTE ORDER BY TFO.TADT DESC) AS POSITION,
 	TFO.ID,
 	TFO.TIERS,
 	TFO.ETB,
 	TFO.REF,
 	TFO.SREF,
 	TFO.TADT,
 	TFO.QTE,
 	TFO.PA
 
	FROM
	TFO
) AS T1
ON (T1.REF = RFO.REF
	AND T1.SREF = RFO.SREF
	AND T1.TIERS = RFO.TIERS
	AND T1.ETB = RFO.ETB)
 
LEFT JOIN
(
	SELECT
	ROW_NUMBER() OVER(PARTITION BY TFO.REF, TFO.SREF, TFO.ETB, TFO.TIERS, TFO.QTE ORDER BY TFO.TADT DESC) AS POSITION,
 	TFO.ID,
 	TFO.TIERS,
 	TFO.ETB,
 	TFO.REF,
 	TFO.SREF,
 	TFO.TADT,
 	TFO.QTE,
 	TFO.PA
 
	FROM
	TFO
) AS T2
ON (T2.REF = RFO.REF
	AND T2.SREF = RFO.SREF
	AND T2.TIERS = RFO.TIERS
	AND T2.ETB = '')
 
LEFT JOIN
(
	SELECT
	ROW_NUMBER() OVER(PARTITION BY TFO.REF, TFO.SREF, TFO.ETB, TFO.TIERS, TFO.QTE ORDER BY TFO.TADT DESC) AS POSITION,
 	TFO.ID,
 	TFO.TIERS,
 	TFO.ETB,
 	TFO.REF,
 	TFO.SREF,
 	TFO.TADT,
 	TFO.QTE,
 	TFO.PA
 
	FROM
	TFO
) AS T3
ON (T3.REF = RFO.REF
	AND T3.SREF = ''
	AND T3.TIERS = RFO.TIERS
	AND T3.ETB = RFO.ETB)
 
LEFT JOIN
(
	SELECT
	ROW_NUMBER() OVER(PARTITION BY TFO.REF, TFO.SREF, TFO.ETB, TFO.TIERS, TFO.QTE ORDER BY TFO.TADT DESC) AS POSITION,
 	TFO.ID,
 	TFO.TIERS,
 	TFO.ETB,
 	TFO.REF,
 	TFO.SREF,
 	TFO.TADT,
 	TFO.QTE,
 	TFO.PA
 
	FROM
	TFO
) AS T4
ON (T4.REF = RFO.REF
	AND T4.SREF = ''
	AND T4.TIERS = RFO.TIERS
	AND T4.ETB = '')
 
LEFT JOIN
ART
ON (RFO.REF = ART.REF)
 
LEFT JOIN
SART
ON (RFO.REF = SART.REF
	AND RFO.SREF = SART.SREF)
 
WHERE
COALESCE(T1.POSITION, T2.POSITION, T3.POSITION, T4.POSITION) = 1
AND RFO.REF = 'E'
Voilà le bébé auquel j'ai donné naissance...
Tout d'abord, j'ai fait en sorte de pouvoir isoler le dernier tarif en date en partitionnant sur TADT ma table TFO par REF, SREF, ETB, TIERS et QTE et en filtrant sur la POSITION.
Ensuite, parce qu'il me fallait tous les cas de figure possibles aussi bien avec les SREF et ETB de TFO que de RFO, j'ai fait un UNION entre deux requêtes. (En gros j'ai émulé un FULL JOIN)
- La première utilisant TFO comme point de départ.
- l'autre RFO.
Enfin, parcequ'il me faut aussi les valeurs par défaut lorsqu'une jointure n'existe pas entre TFO.ETB et RFO.ETB ou TFO.SREF et RFO.SREF, j'ai créé respectivement 4 sous-requêtes RFO lorsque TFO est ma source, et 4 sous-requêtes TFO lorsque RFO est ma source, dans lesquelles mes requêtes vont puiser les infos.
- La première joint RFO et TFO lorsque ETB et SREF sont égaux. (cas de correspondance exacte)
- La seconde joint RFO et TFO lorsque les SREF sont égaux mais que ETB est vide (correspondance uniquement sur la SREF)
- La troisième joint RFO et TFO lorsque les ETB sont égaux mais que SREF est vide (correspondance uniquement sur le ETB)
- La quatrième joint RFO et TFO uniquement sur base de la REF article et TIERS. Les valeurs de ETB et SREF sont vides. (Jointure par défaut ultime).
Je teste succèssivement ces jointures grâce à l'expression COALESCE.

Cette requête aboutissait à un résultat assez concluant dans mes autres tests, mais ici, il me manque des résultats, notamment tous les résultats sur le SR3 qui ne sont présents ni dans RFO ni dans TFO alors que cette sous-référence a elle-aussi des "cas possibles".

Et... je sèche un peu. Etes-vous plus inspiré que moi ?

Merci d'avance pour votre aide.

Bien à vous,

EDIT : Correction dans le résultat attendu :
REF = A | SREF = SR1 |ETB = E3 | QTE = 0
-> TFO.PA = 2 et pas 1

REF = A | SREF = SR1 |ETB = E3 | QTE = 30
-> TFO.PA = 2 et pas 7
La sous-référence a priorité sur la quantité commandée.