none
Jointure sur une date max RRS feed

  • Question

  • Bonjour

    Je cherche à faire une jointure entre 2 tables. Dans l'exemple qui suit, je veux rechercher le Taux de change d'une devise le plus récent par rapport à une date donnée.  Je pourrai passer par une sous-requête mais je ne trouve pas ça élégant.

    Quelqu'un aurait-il une meilleure idée?

    Merci

    Geoffroy

    declare @Devise table 
    		(CodeDevise char(3) not null,
    			Nom varchar(8) not null)
    			
    insert into @Devise (CodeDevise, Nom)
    values
    ('EUR', 'EURO'),
    ('USD', 'DOLLAR')
    
    declare @Taux table
    	(CodeDevise char(3) not null,
    		DateTaux date not null,
    		Taux decimal(9,7) not null)
    		
    insert into @Taux (CodeDevise, DateTaux, Taux)
    values
    ('EUR', '01/01/2001', 1),
    ('USD', '02/01/2010', 1.5),
    ('USD', '03/01/2010', 1.4),
    ('USD', '04/01/2010', 1.35),
    ('USD', '05/01/2010', 1.45),
    ('USD', '08/01/2010', 1.42),
    ('USD', '09/01/2010', 1.25),
    ('USD', '10/01/2010', 1.31)
    
    select d.Nom,
     (Select MAX(t.DateTaux) from @Taux t 
    	where t.DateTaux <= '07/01/2010' and t.CodeDevise = d.CodeDevise) as DateTaux,
     (Select t.Taux from @Taux t 
    	 where t.DateTaux = (Select MAX(t.DateTaux) from @Taux t 
    							where t.DateTaux <= '07/01/2010' and t.CodeDevise = d.CodeDevise)
    			and t.CodeDevise = d.CodeDevise) as Taux
    from @Devise d
    jeudi 7 octobre 2010 15:10

Réponses

  • Bonsoir,

    Si vous possédez une version 2005 ou plus de SQL Server :

     

    WITH CTE
    AS
    (
     SELECT 
     ROW_NUMBER() OVER (PARTITION BY T.CodeDevise ORDER BY T.DateTaux DESC) AS num,
     D.Nom,
     T.Taux,
     T.DateTaux
     FROM @Devise AS D
     INNER JOIN @Taux AS T
     ON D.CodeDevise = T.CodeDevise
     WHERE T.DateTaux <= '07/01/2010'
    )
    SELECT 
     Nom,
     DateTaux,
     Taux
    FROM CTE
    WHERE num = 1;
    
    ++
    MCDBA | MCITP SQL Server 2005 / SQL Server 2008 | LPI Linux 1
    jeudi 7 octobre 2010 17:36
    Modérateur
  • Je vais y aller aussi de ma petite tentative. Côté clarté je pense que ma préférence serait :

    SELECT * FROM @Taux a
    JOIN (SELECT Devise,MAX(Date_Taux) FROM @Taux WHERE DateTaux<='20100701' GROUP BY Devise) b
    ON b.Devise=a.Devise AND b.Date_Taux=a.Date_Taux

     

     

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    jeudi 7 octobre 2010 22:41
  • Autre façon de faire :

    SELECT d.CodeDevise, t.DateTaux, t.Taux
    FROM  @Devise AS d
        INNER JOIN @Taux AS t
           ON d.CodeDevise = t.CodeDevise
    WHERE t.DateTaux >= ALL(SELECT DateTaux
                 FROM  @Taux AS tt
                 WHERE DateTaux <= '20100107'
                  AND tt.CodeDevise = t.CodeDevise )
    AND   t.DateTaux <= '20100107'

     

    A +

     

    Frédéric BROUARD, Spécialiste modélisation, SGBR relationnel, optimisation, langage SQL.
    Le site sur le langage SQL et les SGBD relationnels : http://sqlpro.developpez.com/
    Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
    Le blog sur SQL / MS SQL Server http://blog.developpez.com/sqlpro
    * * * * * Enseignant au CNAM PACA et à l'ISEN à Toulon * * * * *


    Frédéric BROUARD, Spécialiste modélisation, SGBR relationnel, optimisation, langage SQL * * * Le site sur le langage SQL et les SGBD relationnels : http://sqlpro.developpez.com/ * * * Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation * * * Le blog sur SQL / MS SQL Server http://blog.developpez.com/sqlpro * * * Enseignant CNAM PACA, ISEN Toulon, CESI/EXIA Aix En Provence
    dimanche 17 octobre 2010 10:53

Toutes les réponses

  • Bonsoir,

    Si vous possédez une version 2005 ou plus de SQL Server :

     

    WITH CTE
    AS
    (
     SELECT 
     ROW_NUMBER() OVER (PARTITION BY T.CodeDevise ORDER BY T.DateTaux DESC) AS num,
     D.Nom,
     T.Taux,
     T.DateTaux
     FROM @Devise AS D
     INNER JOIN @Taux AS T
     ON D.CodeDevise = T.CodeDevise
     WHERE T.DateTaux <= '07/01/2010'
    )
    SELECT 
     Nom,
     DateTaux,
     Taux
    FROM CTE
    WHERE num = 1;
    
    ++
    MCDBA | MCITP SQL Server 2005 / SQL Server 2008 | LPI Linux 1
    jeudi 7 octobre 2010 17:36
    Modérateur
  • Je vais y aller aussi de ma petite tentative. Côté clarté je pense que ma préférence serait :

    SELECT * FROM @Taux a
    JOIN (SELECT Devise,MAX(Date_Taux) FROM @Taux WHERE DateTaux<='20100701' GROUP BY Devise) b
    ON b.Devise=a.Devise AND b.Date_Taux=a.Date_Taux

     

     

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    jeudi 7 octobre 2010 22:41
  • Bonjour et merci à vous 2 pour vos réponses.

    Les 2 solutions fonctionnent mais ma préférence va quand même pour les CTE.

    Pour info, je viens de tester en complexifiant mon exemple pour qu'il soit au plus près de mon problème réel, et ça fonctionne très bien. Reste à voir le comportement sur quelques centaines de milliers de lignes!

    Merci encore

    Geoffroy

    declare @Devise table 
    		(CodeDevise char(3) not null,
    			Nom varchar(8) not null)
    			
    insert into @Devise (CodeDevise, Nom)
    values
    ('EUR', 'EURO'),
    ('USD', 'DOLLAR')
    
    declare @Taux table
    	(CodeDevise char(3) not null,
    		DateTaux date not null,
    		Taux decimal(9,7) not null)
    		
    insert into @Taux (CodeDevise, DateTaux, Taux)
    values
    ('EUR', '01/01/2001', 1),
    ('USD', '02/01/2010', 1.5),
    ('USD', '03/01/2010', 1.4),
    ('USD', '04/01/2010', 1.35),
    ('USD', '05/01/2010', 1.45),
    ('USD', '08/01/2010', 1.42),
    ('USD', '09/01/2010', 1.25),
    ('USD', '10/01/2010', 1.31),
    ('USD', '11/01/2010', 1.25),
    ('USD', '12/01/2010', 1.27),
    ('USD', '13/01/2010', 1.28),
    ('USD', '14/01/2010', 1.22),
    ('USD', '17/01/2010', 1.19)
    
    declare @Dates table 
    	(DateRef date not null)
    insert into @Dates values
    	('04/01/2010'), ('07/01/2010'), ('12/01/2010'), ('15/01/2010');
    
    WITH CTE
    AS
    (
     SELECT 
     ROW_NUMBER() OVER (PARTITION BY T.CodeDevise, DT.DateRef ORDER BY T.DateTaux DESC) AS num,
     D.Nom,
     T.Taux,
     T.DateTaux,
     DT.DateRef
     FROM @Devise AS D
     INNER JOIN @Taux AS T ON D.CodeDevise = T.CodeDevise
     INNER JOIN @Dates AS DT ON T.DateTaux <= DT.DateRef	
    )
    SELECT
     DateRef, 
     Nom,
     DateTaux,
     Taux
    FROM CTE
    WHERE num = 1;
    
    
    vendredi 8 octobre 2010 08:11
  • Autre façon de faire :

    SELECT d.CodeDevise, t.DateTaux, t.Taux
    FROM  @Devise AS d
        INNER JOIN @Taux AS t
           ON d.CodeDevise = t.CodeDevise
    WHERE t.DateTaux >= ALL(SELECT DateTaux
                 FROM  @Taux AS tt
                 WHERE DateTaux <= '20100107'
                  AND tt.CodeDevise = t.CodeDevise )
    AND   t.DateTaux <= '20100107'

     

    A +

     

    Frédéric BROUARD, Spécialiste modélisation, SGBR relationnel, optimisation, langage SQL.
    Le site sur le langage SQL et les SGBD relationnels : http://sqlpro.developpez.com/
    Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
    Le blog sur SQL / MS SQL Server http://blog.developpez.com/sqlpro
    * * * * * Enseignant au CNAM PACA et à l'ISEN à Toulon * * * * *


    Frédéric BROUARD, Spécialiste modélisation, SGBR relationnel, optimisation, langage SQL * * * Le site sur le langage SQL et les SGBD relationnels : http://sqlpro.developpez.com/ * * * Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation * * * Le blog sur SQL / MS SQL Server http://blog.developpez.com/sqlpro * * * Enseignant CNAM PACA, ISEN Toulon, CESI/EXIA Aix En Provence
    dimanche 17 octobre 2010 10:53
  • Bonjour Fred

    Merci, je ne connaissais pas cette syntaxe et ça fonctionne très bien.

    Geoffroy

    lundi 18 octobre 2010 11:34
  • Salut Geoffroy

    regarde quand même quelle est la moins couteuse en essayant les différentes requêtes sur ta base avec un peu de volume !

    Après cela peut être un exercice d'indexation.

     

    A +

    Frédéric BROUARD, Spécialiste modélisation, SGBR relationnel, optimisation, langage SQL.
    Le site sur le langage SQL et les SGBD relationnels : http://sqlpro.developpez.com/
    Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
    Le blog sur SQL / MS SQL Server http://blog.developpez.com/sqlpro
    * * * * * Enseignant au CNAM PACA et à l'ISEN à Toulon * * * * *


    Frédéric BROUARD, Spécialiste modélisation, SGBR relationnel, optimisation, langage SQL * * * Le site sur le langage SQL et les SGBD relationnels : http://sqlpro.developpez.com/ * * * Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation * * * Le blog sur SQL / MS SQL Server http://blog.developpez.com/sqlpro * * * Enseignant CNAM PACA, ISEN Toulon, CESI/EXIA Aix En Provence
    lundi 18 octobre 2010 14:03
  • Bonjour Fred

    Pour l'instant, c'est ta solution la moins coûteuse. J'ai de très bons résultats! Merci encore

    Geoffroy

    mardi 19 octobre 2010 08:39