Discussion :

[Catab]

Bonjour,

J'ai bien conscience que le problème de sélectionner une ligne dont la date est, au choix, la plus grande, la plus petite, la plus proche par rapport à..., a déjà été évoqué maintes et maintes fois sur ce forum et je m'excuse déjà pour mon incompétence...
Cependant, je n'arrive pas à transposer les solutions vues sur différents sujets à mon propre problème.

Voilà ce qu'il en est.
Je dispose d'une table 'Tarifs' avec les différents tarifs de chacun de mes articles. Ces tarifs sont datés.
J'ai aussi une table 'ConcurrenceLignes' avec des prix provenant de devis concurrents. Eux aussi sont datés.

Voici le code des CREATE :

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
DROP DATABASE IF EXISTS test;
 
CREATE DATABASE test CHARACTER SET 'utf8';
 
USE test;
 
CREATE TABLE IF NOT EXISTS test.Articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(10) NOT NULL,
libelle VARCHAR(100) NOT NULL
)
ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS test.Concurrents (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(100) NOT NULL
)
ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS test.ConcurrenceLignes (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
IDConcurrent INT NOT NULL,
IDArticle INT NOT NULL,
dateDevis DATE NOT NULL,
prix NUMERIC(14,4) NOT NULL,
 
CONSTRAINT fk_IDConcurrent
	FOREIGN KEY (IDConcurrent)
	REFERENCES Concurrents(ID),
 
CONSTRAINT fk_IDArticle
	FOREIGN KEY (IDArticle)
	REFERENCES Articles(ID)
 
)
ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS test.Tarifs (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
IDArticle INT NOT NULL,
dateTarif DATE NOT NULL,
prix NUMERIC(14,4) NOT NULL,
 
CONSTRAINT fk_IDArticle
	FOREIGN KEY (IDArticle)
	REFERENCES Articles(ID)
)
ENGINE = INNODB;

Un échantillon test :

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
INSERT INTO test.Articles (code, libelle)
VALUES
	('A', 'Article A'),
	('B', 'Article B'),
	('C', 'Article C');
 
INSERT INTO test.Concurrents (nom)
VALUES
	('Concurrent 1'),
	('Concurrent 2');
 
INSERT INTO test.ConcurrenceLignes (IDConcurrent, IDArticle, dateDevis, prix)
VALUES
	(1, 1, '20180101', 4.5),
	(1, 1, '20180301', 5.2),
	(1, 2, '20181005', 29.9),
	(2, 1, '20180501', 4.8),
	(2, 2, '20180320', 25.9),
	(2, 2, '20180420', 31.9),
	(2, 3, '20180615', 1510.41),
	(2, 3, '20180920', 1490.71);
 
INSERT INTO test.Tarifs (IDArticle, dateTarif, prix)
VALUES
	(1, '20180201', 4.9),
	(1, '20180504', 5.1),
	(1, '20180910', 5.4),
	(2, '20180112', 23.12),
	(2, '20180314', 22.4),
	(2, '20180725', 27.92),
	(2, '20181210', 31.45),
	(3, '20180411', 1575.45),
	(3, '20180813', 1603.37);

J'aimerais avoir, pour chacun de mes prix concurrents, le prix auquel nous vendions l'article au moment du devis.
En clair, je dois extraire 'Tarif.prix' pour lequel 'ConcurrenceLignes.IDArticle' = 'Tarifs.IDArticle' et où 'Tarif.dateTarif' est la plus grande valeur inférieure à 'ConcurrenceLignes.dateDevis' et ce pour chaque ligne de 'ConcurrenceLignes'.

Voici une ébauche d'un SELECT... je vous passe la partie où j'essaye de joindre la date venant de Tarif. J'ai essayé de manipuler des sous-requêtes avec des MAX(), des HAVING... mais sans succès.

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
A.code,
A.libelle,
C.nom,
CL.dateDevis,
CL.prix,
T.dateTarif,
T.prix
 
FROM
test.Articles AS A
 
INNER JOIN
test.ConcurrenceLignes AS CL
ON A.ID = CL.IDArticle
 
INNER JOIN
test.Concurrents AS C
ON C.ID = CL.IDConcurrent
 
INNER JOIN
test.Tarifs AS T
ON A.ID = T.IDArticle
 
WHERE
-- Tarif dont la date est la plus grande inférieure à la date du devis pour cet article
-- T.ID = ??

Pouvez-vous m'aider ?

Merci beaucoup.

[al1_24]

Quelque chose comme ça ?

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
select  art.code
    ,   art.libelle
    ,   cnc.nom
    ,   clg.datedevis
    ,   clg.prix
    ,   trf.datetarif
    ,   trf.prix
from    test.articles           as  art
    inner join
        test.concurrencelignes  as  clg
        on  art.id  = clg.idarticle
    inner join
        test.concurrents        as  cnc
        on  cnc.id  = clg.idconcurrent
    inner join
        test.tarifs             as  trf
        on  art.id  = trf.idarticle
where   exists
        (   select  null
            from    tarif   sel
            where   trf.idarticle   = sel.idarticle
                and trf.datetarif   <= clg.datedevis
            having  trf.datetarif   = max(sel.datetarif)
        )

[Catab]

Bonjour al1_24,

Cela ne fonctionne pas

Voici le résultat que je souhaite obtenir (sur base des données citées dans mon 1er post) :

code	libelle	nom	dateDevis	prix	dateTarif	prix
A	Article A	Concurrent 1	2018-03-01	5.2	2018-02-01	4.9
B	Article B	Concurrent 1	2018-10-05	29.9	2018-07-25	27.92
A	Article A	Concurrent 2	2018-05-01	4.8	2018-02-01	4.9
B	Article B	Concurrent 2	2018-03-20	25.9	2018-03-14	22.4
B	Article B	Concurrent 2	2018-04-20	31.9	2018-03-14	22.4
C	Article C	Concurrent 2	2018-06-15	1510.41	2018-04-11	1575.45
C	Article C	Concurrent 2	2018-09-20	1490.71	2018-08-13	1603.37

Et Voilà ce que j'obtiens via ta requête :

code	libelle	nom	dateDevis	prix	dateTarif	prix
C	Article C	Concurrent 2	2018-09-20	1490.71	2018-08-13	1603.37

Y a du bon puisqu'il s'agit de la dernière ligne de mon tableau voulu.

Je cherche également une solution de mon côté...

Bàv,

[al1_24]

Essaye en remplaçant trf.datetarif par sel.datetarif à la ligne 22.
J'ai fait ça un peu vite et sans vérifier

[Catab]

Tu es génial !
C'est exactement le résultat que je souhaitais.

Y a plus qu'à maitriser la syntaxe pour l'appliquer grandeur nature maintenant.

Merci beaucoup

[Catab]

Je vais pousser le problème un peu plus loin.

Mais j'aimerais obtenir toutes les lignes de 'ConcurrenceLignes' quoi qu'il arrive, qu'il y aie un article lié ou pas, un tarif lié ou pas.

Dans l'exemple, j'aimerais que la 1ère ligne (ID = 1) de 'ConcurrenceLignes' s'affiche malgré qu'il n'y a pas de tarif antérieur.
J'aimerais aussi que, si un article inexistant est présent dans 'ConcurrenceLignes', ma requête me renvoie quand même la ligne de la concurrence, sans les infos liés à l'article du coup.

Code :

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

1
2
3
4
-- Pas d'article avec l'ID 4 !
INSERT INTO test.ConcurrenceLignes (IDConcurrent, IDArticle, dateDevis, prix)
VALUES
	(1, 4, '20180101', 4.5);

Est-ce possible ?

Merci

[aieeeuuuuu]

J'aimerais aussi que, si un article inexistant est présent dans 'ConcurrenceLignes'

Vous avez une contrainte d’intégrité de ConcurrenceLignes vers Article, cette situation est donc impossible logiquement, a moins que la contrainte ne soit pas validée.

qu'en est-il ?


Vous pouvez toutefois partir sur une solution de ce type, différente de celle d'al1_24

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
 
WITH CTE AS 
(
    SELECT 
	   a.code,
	   a.libelle,
	   c.nom,
	   cl.dateDevis,
	   cl.prix AS PrixConcurrent,
	   t.dateTarif,
	   t.prix,
	   ROW_NUMBER() OVER(PARTITION BY cl.id ORDER BY t.dateTarif DESC) AS RN
    FROM	   test.ConcurrenceLignes AS cl
    INNER JOIN test.Concurrents AS c
	   ON cl.IDConcurrent = c.id
    LEFT JOIN test.Articles AS a
	   ON a.id = cl.IDArticle
    LEFT JOIN test.Tarifs AS t
	   ON cl.IDArticle = t.IDArticle
	   AND t.dateTarif <= cl.dateDevis
)
SELECT 
    code,
    libelle,
    nom,
    dateDevis,
    PrixConcurrent,
    dateTarif,
    prix
FROM CTE
WHERE RN = 1

[Catab]

En effet, mon exemple est mauvais à cause de la contrainte d'intégrité qui ne représente en fait pas la réalité... J'ai voulu faire simple car je pensais être en mesure de gérer le surplus de complexité à base de OUTER JOIN etc.

Si vous vous souvenez de mon dernier post sur ce forum, j'expliquais à quelle point la base de données que j'utilise est vétuste et à quelle point elle ignorait les fondements de la gestion de base de données relationnelles.

Soit, il faut jongler avec...

En tout cas ta requête fonctionne à merveille. Je vais prendre le temps de l'étudier pour essayer de la mettre en pratique sur la DB de production.

Merci aieeeuuuuu !

Je marque le sujet comme résolu. J'espère ne pas avoir à revenir vers vous si je cale encore sur cette requête.

Bien à vous.