Discussion :

[Isildur]

bonjour à tous,
je me permet de vous soumettre un cas de jointure qui se révèle être un vrai casse tête.

j'ai une table de fait (des facturations) qui de manière simplifiée se présente ainsi :

sociéte / devise / client / chiffres_d_affaires / date_de_facture

je cherche à faire une jointure sur une table devise :

sociéte/ devise/date_de_debut/taux_de_change

cette table contient les différents taux de change valable pour un intervalle de temps imprévisible (puisqu'il est possible à tous moment d'ajouter un taux de change pour une date donnée, ce taux sera valable à partir de la date en question, jusqu'à la date correspondant à l'enregistrement suivant)

les enregistrements ont donc cet aspect :

sociéte/ devise/date_de_debut/taux_de_change

001/USD/01-05-10/1.28
001/USD/05-06-10/1.27
001/USD/10-08-10/1.31
etc...

mon objectif étant bien entendu de pouvoir rapprocher les factures des taux de change valables pour la date de facturation.

merci de votre aide.

[asmduty]

Dans une sous requête cherches les dates inférieures à la date de la facture.

001/USD/01-05-10/1.28
001/USD/05-06-10/1.27
001/USD/10-08-10/1.31

facture du 08-06-10 tu élimines donc la dernière ligne

Et pour avoir seulement le taux de change qui t'interesse, utilise la fonction MAX() dans ton select, tu élimines donc la première ligne et trouve la ligne qui t'intéresse

[lola06]

Bonjour,

Quel est ton SGBD ?

Code :

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

1
2
3
4
5
6
7
SELECT societe, devise, client, chiffres_d_affaires, date_de_facture, max(date_de_debut), taux_de_change
FROM facturation f
     JOIN devise d
     ON d.societe = f.societe
     AND d.devise = f.devise
WHERE date_debut < date_de_facture
GROUP BY societe, devise, client, chiffres_d_affaires, date_de_facture, taux_de_change

J'ai supposé que tes clés primaires étaient societe et client...

[fsmrel]

Bonjour,

@ Lola

Il y a quelque chose qui cloche : pour la paire {société, devise} votre requête produit nécessairement une ligne pour chaque chaque taux dont la date d'effet est < à la date de facture...

[lola06]

Effectivement, je me suis plantée (fin de semaine, boulot, toussa...)

Je ne peux pas tester par contre, donc...

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
SELECT t.societe, t.devise, f.client, f.chiffres_d_affaires, f.date_de_facture, f.taux_de_change
FROM (
     SELECT societe, devise, max(date_de_debut) date_debut
     FROM facturation f
          JOIN devise d
            ON d.societe = f.societe
            AND d.devise = f.devise
     WHERE date_debut < date_de_facture
     GROUP BY societe, devise) t
JOIN facturation f
  ON f.societe = t.societe
  AND f.devise = t.devise
JOIN devise d
  ON t.societe = d.societe
  AND t.devise = d.devise
  AND d.date_de_debut = t.date_debut

[Waldar]

Évitez les recalculs à la volée.
D'un point de vue modélisation, la table devise est très bien comme cela.

Il suffit de calculer la date de fin pour créer une période qui vous permettront une jointure avec un between.

Si votre SBGD supporte les fonctions de fenêtrage, utilisez la fonction LEAD.
Sinon, il faudra passer par le MIN des dates supérieures avec une autojointure.

[SQLpro]

Le mieux serait de créer une vue. C'est fait pour cela !

A +

[fsmrel]

Bonsoir Isildur,


Sur la base de votre exemple, effectuons un test en tenant compte des préconisations de Waldar. J'utilse ici SQL Server (voyez la fonction DATEADD qui sert ici à calculer J-1), adaptez s'il le faut en fonction de votre SGBD.

Création des tables :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE FACTURATION 
(
        Societe       CHAR(10)   NOT NULL
      , Devise        CHAR(10)   NOT NULL
      , Client        CHAR(10)   NOT NULL
      , DateFacture   DATE       NOT NULL
) ;
 
CREATE TABLE DEVISE 
(
        Societe       CHAR(10)       NOT NULL
      , Devise        CHAR(10)       NOT NULL
      , DateDebutTaux DATE           NOT NULL
      , TauxChange    DECIMAL(3,2)   NOT NULL
) ;

Un jeu d’essai :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c10', '2010-05-15') ;
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c25', '2010-07-13') ;
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c26', '2010-07-14') ;
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c27', '2010-07-15') ;
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c30', '2010-09-09') ;
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c31', '2010-09-10') ;
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c32', '2010-09-11') ;
INSERT INTO FACTURATION VALUES ('s1', 'eur', 'c03', '2011-12-16') ;
 
INSERT INTO FACTURATION VALUES ('s2', 'usd', 'c1', '2010-05-20') ;
INSERT INTO FACTURATION VALUES ('s2', 'eur', 'c1', '2010-07-20') ;

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO DEVISE VALUES ('s1', 'usd', '2010-01-01', 1.28) ;
INSERT INTO DEVISE VALUES ('s1', 'usd', '2010-05-02', 1.40) ;
INSERT INTO DEVISE VALUES ('s1', 'usd', '2010-06-05', 1.27) ;
INSERT INTO DEVISE VALUES ('s1', 'usd', '2010-07-14', 1.29) ;
INSERT INTO DEVISE VALUES ('s1', 'usd', '2010-08-01', 1.31) ;
INSERT INTO DEVISE VALUES ('s1', 'usd', '2010-09-10', 1.34) ;
INSERT INTO DEVISE VALUES ('s1', 'eur', '2010-01-01', 1.00) ;
 
INSERT INTO DEVISE VALUES ('s2', 'usd', '2010-02-10', 1.30) ;
INSERT INTO DEVISE VALUES ('s2', 'usd', '2010-07-10', 1.34) ;
INSERT INTO DEVISE VALUES ('s2', 'usd', '2010-10-10', 1.38) ;

Afin que les requêtes ne soient pas trop hermétiques, on peut utiliser une vue pour représenter les dates d’effet des devises sous la forme intervallaire (date de début, date de fin). Comme dit Waldar, on passe par une auto-jointure (1er bloc de l’UNION), avec en plus la mise en œuvre d’un intervalle dont la fin soit suffisamment éloignée dans le temps pour prendre en compte le taux du jour (2e bloc de l’UNION) :

Code SQL :

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
CREATE VIEW INTERVALLE  (Societe, Devise, DateDebutTaux, DateFinTaux, TauxChange) AS
    SELECT  x.societe, x.devise, x.DateDebutTaux, DATEADD (DAY, -1, MIN(y.DateDebutTaux)) AS DateFinTaux, x.TauxChange
    FROM   DEVISE AS x JOIN DEVISE AS y 
              ON x.Societe = y.Societe, AND x.Devise = y.Devise AND x.DateDebutTaux < y.DateDebutTaux
    GROUP BY x.societe, x.devise, x.DateDebutTaux, x.TauxChange
 
    UNION ALL
 
    SELECT x.Societe, x.Devise, y.DateDebutTaux, y.DateFinTaux, x.TauxChange 
    FROM   DEVISE AS x JOIN  
                           (
                            SELECT  societe, devise, MAX(DateDebutTaux)AS DateDebutTaux, '9999-12-31' AS DateFinTaux
                            FROM    DEVISE
                            GROUP BY societe, devise
                           ) AS y
                        ON  x.Societe = y.Societe AND x.Devise = y.Devise AND x.DateDebutTaux = y.DateDebutTaux
;

Recherche des taux pour les lignes de facturation, au moyen du BETWEEN préconisé par Waldar :

Code SQL :

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

1
2
3
4
5
6
SELECT x.Societe, x.Devise, x.Client, x.DateFacture, y.TauxChange
FROM   FACTURATION AS x JOIN INTERVALLE AS y
                ON  x.Societe = y.Societe
                AND x.Devise = y.Devise 
                AND x.DateFacture BETWEEN y.DateDebutTaux AND y.DateFinTaux 
;

Au résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Societe    Devise    Client    DateFacture    TauxChange
-------    ------    ------    -----------    ----------
s1         eur       c03       2011-12-16     1.00
s1         usd       c10       2010-05-15     1.40
s1         usd       c25       2010-07-13     1.27
s1         usd       c26       2010-07-14     1.29
s1         usd       c27       2010-07-15     1.29
s1         usd       c30       2010-09-09     1.31
s1         usd       c31       2010-09-10     1.34
s1         usd       c32       2010-09-11     1.34
s2         usd       c1        2010-05-20     1.30

[fsmrel]

Hello Fred,

Je n'avais pas vu votre message, mais je constate que nous avons eu le même réflexe