none
Creation de table et recuperation de Max et de Min RRS feed

  • Question

  • Bonjour,

    J'ai une premiere table avec des donnees en minutes:

    DateTrade                  Value1   Value2                   

    01/01/2010 09:00:00     2              34

    01/01/2010 09:01:00    10              45

    01/01/2010 09:02:00     8              21

    01/01/2010 09:03:00     28            32

    01/01/2010 09:04:00     36            31

    01/01/2010 09:05:00     56            7

    01/01/2010 09:06:00     14            1

     

    J'ai besoin de creer une seconde table a partir de la premiere qui me restitue les donnees toute les 2 minutes: J'ai besoin de

    - ne selectionner qu'une donnee de DateTrade sur 2:

    - qui en meme temps renvoie le max et les min de de Value1 et Value2

     

    Avec la table a creer ca devrait etre plus clair:

    DateTrade                  Max Value1   Min Value2                   

    01/01/2010 09:01:00    Max (10;2)       Min (45;34)

    01/01/2010 09:03:00     Max (28;8)       Min(32;21)

    01/01/2010 09:05:00     Max (56;36)      Min(7;31)

     

    La je suis vraiment perdu, si quelqu'un avait une idee

    Merci

    samedi 1 mai 2010 09:58

Réponses

  • Bonjour,

    En admettant que les dates de votre table soient toujours décalées d'une minute une solution pourrait être celle-ci :

    DECLARE @t TABLE
    (
     DateTrade DATETIME,
     Value1 INT,
     Value2 INT
    );
     
    INSERT INTO @t VALUES ('01/01/2010 09:00:00', 2, 34);
    INSERT INTO @t VALUES ('01/01/2010 09:01:00', 10, 45);
    INSERT INTO @t VALUES ('01/01/2010 09:02:00', 8, 21);
    INSERT INTO @t VALUES ('01/01/2010 09:03:00', 28, 32);
    INSERT INTO @t VALUES ('01/01/2010 09:04:00', 36, 31);
    INSERT INTO @t VALUES ('01/01/2010 09:05:00', 56, 7);
    INSERT INTO @t VALUES ('01/01/2010 09:06:00', 14, 1);
     
    WITH CTE
    AS
    (
      SELECT 
       ROW_NUMBER() OVER(ORDER BY DateTrade) AS num,
       *
      FROM @t
    )
    SELECT 
      T2.DateTrade,
      CASE T2.num % 2 WHEN 0 THEN CASE 
              WHEN T.Value1 > T2.Value1 THEN T.Value1
              WHEN T.Value1 < T2.Value1 THEN T2.Value1
              ELSE T.Value1
             END
          ELSE NULL
      END AS Value1,
      CASE T2.num % 2 WHEN 0 THEN CASE 
              WHEN T.Value2 > T2.Value2 THEN T2.Value2
              WHEN T.Value2 < T2.Value2 THEN T.Value2
              ELSE T.Value2
             END
          ELSE NULL
      END AS Value2
    FROM CTE AS T 
    INNER JOIN CTE AS T2
    ON T.num = T2.num - 1
    WHERE T2.num % 2 = 0;
    
     
    ++

    MCDBA | MCITP SQL Server 2005 / SQL Server 2008 | LPI Linux 1
    samedi 1 mai 2010 12:17
    Modérateur

Toutes les réponses

  • Bonjour,

    En admettant que les dates de votre table soient toujours décalées d'une minute une solution pourrait être celle-ci :

    DECLARE @t TABLE
    (
     DateTrade DATETIME,
     Value1 INT,
     Value2 INT
    );
     
    INSERT INTO @t VALUES ('01/01/2010 09:00:00', 2, 34);
    INSERT INTO @t VALUES ('01/01/2010 09:01:00', 10, 45);
    INSERT INTO @t VALUES ('01/01/2010 09:02:00', 8, 21);
    INSERT INTO @t VALUES ('01/01/2010 09:03:00', 28, 32);
    INSERT INTO @t VALUES ('01/01/2010 09:04:00', 36, 31);
    INSERT INTO @t VALUES ('01/01/2010 09:05:00', 56, 7);
    INSERT INTO @t VALUES ('01/01/2010 09:06:00', 14, 1);
     
    WITH CTE
    AS
    (
      SELECT 
       ROW_NUMBER() OVER(ORDER BY DateTrade) AS num,
       *
      FROM @t
    )
    SELECT 
      T2.DateTrade,
      CASE T2.num % 2 WHEN 0 THEN CASE 
              WHEN T.Value1 > T2.Value1 THEN T.Value1
              WHEN T.Value1 < T2.Value1 THEN T2.Value1
              ELSE T.Value1
             END
          ELSE NULL
      END AS Value1,
      CASE T2.num % 2 WHEN 0 THEN CASE 
              WHEN T.Value2 > T2.Value2 THEN T2.Value2
              WHEN T.Value2 < T2.Value2 THEN T.Value2
              ELSE T.Value2
             END
          ELSE NULL
      END AS Value2
    FROM CTE AS T 
    INNER JOIN CTE AS T2
    ON T.num = T2.num - 1
    WHERE T2.num % 2 = 0;
    
     
    ++

    MCDBA | MCITP SQL Server 2005 / SQL Server 2008 | LPI Linux 1
    samedi 1 mai 2010 12:17
    Modérateur
  • Bonjour,

    C'est exactement ca que je veux faire.

    J'ai juste fait quelques changements de noms dans le code pour que ca corresponde a mes donnees de la base initiale

    Si maintenant j'ai besoin d'ajouter d'autres donnees qui repondent a des logiques un peu differentes:

    J'ai besoin de rapatrier pour la premiere ligne:

    -EurostoxxBase1Opening float =1

    -EurostoxxBase1Closing float = 11

    -EurostoxxBase1Volume float = 50+60

     

    J'ai besoin de rapatrier pour la deuxieme ligne:

     -EurostoxxBase1Opening float =3

     -EurostoxxBase1Closing float = 13

     -EurostoxxBase1Volume float = 70+80

     

    Je ne sais pas comment ajouter ces donnees au code initial.

    Je n'arrive pas a ajouter ces nouvelles donnees.

    Voici le code que j'ai modifie, celui la marche, mais quand j'essaie d'ajouter les nouvelles donnees ca ne marche pas.

    Pourriez vous m'indiquer comment modifier mo code

     

    Merci

     

    DECLARE @t TABLE
    (
     DateTrade             smalldatetime,
     EurostoxxBase1Opening float,
     EurostoxxBase1Maximum float,
     EurostoxxBase1Minimum float,
     EurostoxxBase1Closing float,
     EurostoxxBase1Volume float,
    );
     
    INSERT INTO @t VALUES ('01/01/2010 09:00:00', 1, 2, 34, 10, 50);
    INSERT INTO @t VALUES ('01/01/2010 09:01:00', 2, 10, 45, 11, 60);
    INSERT INTO @t VALUES ('01/01/2010 09:02:00', 3, 8, 21, 12, 70);
    INSERT INTO @t VALUES ('01/01/2010 09:03:00', 4, 28, 32, 13, 80);
    INSERT INTO @t VALUES ('01/01/2010 09:04:00', 5, 36, 31, 14, 90);
    INSERT INTO @t VALUES ('01/01/2010 09:05:00', 6, 56, 7, 15, 100);
    INSERT INTO @t VALUES ('01/01/2010 09:06:00', 7, 14, 1, 16, 110);
     
    WITH CTE
    AS
    (
      SELECT
       ROW_NUMBER() OVER(ORDER BY DateTrade) AS num,
       *
      FROM @t
    )
    SELECT
      T2.DateTrade,
      CASE T2.num % 2 WHEN 0 THEN CASE
              WHEN T.EurostoxxBase1Maximum > T2.EurostoxxBase1Maximum THEN T.EurostoxxBase1Maximum
              WHEN T.EurostoxxBase1Maximum < T2.EurostoxxBase1Maximum THEN T2.EurostoxxBase1Maximum
              ELSE T.EurostoxxBase1Maximum
             END
          ELSE NULL
      END AS MaxB2,
      CASE T2.num % 2 WHEN 0 THEN CASE
              WHEN T.EurostoxxBase1Minimum > T2.EurostoxxBase1Minimum THEN T2.EurostoxxBase1Minimum
              WHEN T.EurostoxxBase1Minimum < T2.EurostoxxBase1Minimum THEN T.EurostoxxBase1Minimum
              ELSE T.EurostoxxBase1Minimum
             END
          ELSE NULL
      END AS MinB2
    FROM CTE AS T
    INNER JOIN CTE AS T2
    ON T.num = T2.num - 1
    WHERE T2.num % 2 = 0;

    DECLARE @t TABLE
    (
     DateTrade             smalldatetime,
     EurostoxxBase1Opening float,
     EurostoxxBase1Maximum float,
     EurostoxxBase1Minimum float,
     EurostoxxBase1Closing float,
     EurostoxxBase1Volume float,
    );
     
    INSERT INTO @t VALUES ('01/01/2010 09:00:00', 1, 2, 34, 10, 50);
    INSERT INTO @t VALUES ('01/01/2010 09:01:00', 2, 10, 45, 11, 60);
    INSERT INTO @t VALUES ('01/01/2010 09:02:00', 3, 8, 21, 12, 70);
    INSERT INTO @t VALUES ('01/01/2010 09:03:00', 4, 28, 32, 13, 80);
    INSERT INTO @t VALUES ('01/01/2010 09:04:00', 5, 36, 31, 14, 90);
    INSERT INTO @t VALUES ('01/01/2010 09:05:00', 6, 56, 7, 15, 100);
    INSERT INTO @t VALUES ('01/01/2010 09:06:00', 7, 14, 1, 16, 110);
     
    WITH CTE
    AS
    (
      SELECT
       ROW_NUMBER() OVER(ORDER BY DateTrade) AS num,
       *
      FROM @t
    )
    SELECT
      T2.DateTrade,
      CASE T2.num % 2 WHEN 0 THEN CASE
              WHEN T.EurostoxxBase1Maximum > T2.EurostoxxBase1Maximum THEN T.EurostoxxBase1Maximum
              WHEN T.EurostoxxBase1Maximum < T2.EurostoxxBase1Maximum THEN T2.EurostoxxBase1Maximum
              ELSE T.EurostoxxBase1Maximum
             END
          ELSE NULL
      END AS MaxB2,
      CASE T2.num % 2 WHEN 0 THEN CASE
              WHEN T.EurostoxxBase1Minimum > T2.EurostoxxBase1Minimum THEN T2.EurostoxxBase1Minimum
              WHEN T.EurostoxxBase1Minimum < T2.EurostoxxBase1Minimum THEN T.EurostoxxBase1Minimum
              ELSE T.EurostoxxBase1Minimum
             END
          ELSE NULL
      END AS MinB2
    FROM CTE AS T
    INNER JOIN CTE AS T2
    ON T.num = T2.num - 1
    WHERE T2.num % 2 = 0;

     

     

    samedi 1 mai 2010 13:18
  • Bonsoir,

    DECLARE @t TABLE
    (
     DateTrade       smalldatetime,
     EurostoxxBase1Opening float,
     EurostoxxBase1Maximum float,
     EurostoxxBase1Minimum float,
     EurostoxxBase1Closing float,
     EurostoxxBase1Volume float
    );
     
    INSERT INTO @t VALUES ('01/01/2010 09:00:00', 1, 2, 34, 10, 50);
    INSERT INTO @t VALUES ('01/01/2010 09:01:00', 2, 10, 45, 11, 60);
    INSERT INTO @t VALUES ('01/01/2010 09:02:00', 3, 8, 21, 12, 70);
    INSERT INTO @t VALUES ('01/01/2010 09:03:00', 4, 28, 32, 13, 80);
    INSERT INTO @t VALUES ('01/01/2010 09:04:00', 5, 36, 31, 14, 90);
    INSERT INTO @t VALUES ('01/01/2010 09:05:00', 6, 56, 7, 15, 100);
    INSERT INTO @t VALUES ('01/01/2010 09:06:00', 7, 14, 1, 16, 110);
     
    WITH CTE
    AS
    (
     SELECT 
      ROW_NUMBER() OVER(ORDER BY DateTrade) AS num,
      *
     FROM @t
    )
    SELECT 
     T2.DateTrade,
     CASE T2.num % 2 WHEN 0 THEN CASE 
         WHEN T.EurostoxxBase1Maximum > T2.EurostoxxBase1Maximum THEN T.EurostoxxBase1Maximum
         WHEN T.EurostoxxBase1Maximum < T2.EurostoxxBase1Maximum THEN T2.EurostoxxBase1Maximum
         ELSE T.EurostoxxBase1Maximum
         END
       ELSE NULL
     END AS MaxB2,
     CASE T2.num % 2 WHEN 0 THEN CASE 
         WHEN T.EurostoxxBase1Minimum > T2.EurostoxxBase1Minimum THEN T2.EurostoxxBase1Minimum
         WHEN T.EurostoxxBase1Minimum < T2.EurostoxxBase1Minimum THEN T.EurostoxxBase1Minimum
         ELSE T.EurostoxxBase1Minimum
         END
       ELSE NULL
     END AS MinB2,
     CASE T2.num % 2 WHEN 0 THEN T.EurostoxxBase1Opening END AS EurostoxxBase1Opening,
     CASE T2.num % 2 WHEN 0 THEN T2.EurostoxxBase1Closing END AS EurostoxxBase1Closing,
     CASE T2.num % 2 WHEN 0 THEN T.EurostoxxBase1Volume + T2.EurostoxxBase1Volume END AS EurostoxxBase1Volume
    FROM CTE AS T 
    INNER JOIN CTE AS T2
    ON T.num = T2.num - 1
    WHERE T2.num % 2 = 0;

     ++


    MCDBA | MCITP SQL Server 2005 / SQL Server 2008 | LPI Linux 1
    dimanche 2 mai 2010 18:53
    Modérateur
  • Bonsoir Mike,

     

    Merci c'est exactement ce que je voulais faire, je vais le faire tourner sur la base table initiale.

     

     

    lundi 3 mai 2010 17:26
  • Bonjour Mike,

    Ca marche parfaitement, mais il y a encore 1 truc que je ne sais pas faire ( eh oui je debute!)

     

    J'ai modifie un peu le code pour pour me servir des donnees de la table initiale et ca me donne les bons resultats, mais je ne sais pas comment afficher les colonnes dans l'ordre que je veux a savoir:

    EuroStoxxBase2TradeDate / EurostoxxBase2Opening / EurostoxxBase2Maximum / EurostoxxBase2Minimum / EurostoxxBase2Closing / EurostoxxBase2Volume

     

    Voici le code modifier modifie qui fonctionne:

    Use Pat1
    CREATE TABLE EuroStoxxBase2
    (

    EuroStoxxBase2TradeDate      Smalldatetime,
    EurostoxxBase2Opening        FLOAT,
    EurostoxxBase2Maximum        FLOAT,
    EurostoxxBase2Minimum        FLOAT,
    EurostoxxBase2Closing        FLOAT,
    EurostoxxBase2Volume         FLOAT
    );

    WITH CTE
    AS
    (
     SELECT EurostoxxBase1TradeDate,EuroStoxxBase1Opening, EurostoxxBase1Maximum, EurostoxxBase1Minimum,EurostoxxBase1Closing, EurostoxxBase1Volume,
      ROW_NUMBER() OVER(ORDER BY EurostoxxBase1TradeDate) AS num FROM EuroStoxxBase1 
    )
    INSERT INTO EurostoxxBase2
    (EuroStoxxBase2TradeDate, EurostoxxBase2Opening, EurostoxxBase2Maximum, EurostoxxBase2Minimum, EurostoxxBase2Closing, EurostoxxBase2Volume
    )

    SELECT
     T2.EurostoxxBase1TradeDate,
     CASE T2.num % 2 WHEN 0 THEN CASE
         WHEN T.EurostoxxBase1Maximum > T2.EurostoxxBase1Maximum THEN T.EurostoxxBase1Maximum
         WHEN T.EurostoxxBase1Maximum < T2.EurostoxxBase1Maximum THEN T2.EurostoxxBase1Maximum
         ELSE T.EurostoxxBase1Maximum
         END
       ELSE NULL
     END AS EurostoxxBase2Maximum,
     CASE T2.num % 2 WHEN 0 THEN CASE
         WHEN T.EurostoxxBase1Minimum > T2.EurostoxxBase1Minimum THEN T2.EurostoxxBase1Minimum
         WHEN T.EurostoxxBase1Minimum < T2.EurostoxxBase1Minimum THEN T.EurostoxxBase1Minimum
         ELSE T.EurostoxxBase1Minimum
         END
       ELSE NULL
     END AS EurostoxxBase2Minimum,

     CASE T2.num % 2 WHEN 0 THEN T.EurostoxxBase1Opening END AS EurostoxxBase2Opening,
     CASE T2.num % 2 WHEN 0 THEN T2.EurostoxxBase1Closing END AS EurostoxxBase2Closing,
     CASE T2.num % 2 WHEN 0 THEN T.EurostoxxBase1Volume + T2.EurostoxxBase1Volume END AS EurostoxxBase2Volume
    FROM CTE AS T
    INNER JOIN CTE AS T2
    ON T.num = T2.num - 1
    WHERE T2.num % 2 = 0;

    Pourrais tu m'expliquer comment je dois modifier mon code pour pouvoir gerer les colonnes dans l'ordre que je veux

     

    lundi 3 mai 2010 19:12
  • Voila le code qui marche si jamais ca peut aider quelqu'un

    CREATE TABLE EurostoxxBase2
    (
     EurostoxxBase2TradeDate    smalldatetime,
     EurostoxxBase2Opening     float,
     EurostoxxBase2Maximum     float,
     EurostoxxBase2Minimum     float,
     EurostoxxBase2Closing     float,
     EurostoxxBase2Volume     float
    );
     
     
    WITH CTE
    AS
    (
     SELECT cte.EurostoxxBase1ContractType, cte.EurostoxxBase1TradeDate, 
     cte.EurostoxxBase1Opening, cte.EurostoxxBase1Maximum,
     cte.EurostoxxBase1Minimum, cte.EurostoxxBase1Closing, cte.EurostoxxBase1Volume, 
     ROW_NUMBER() OVER(ORDER BY EurostoxxBase1TradeDate) AS num
     FROM EurostoxxBase1 cte
    )
    
    INSERT INTO EurostoxxBase2
    (
     EurostoxxBase2TradeDate,
     EurostoxxBase2Opening,
     EurostoxxBase2Maximum,
     EurostoxxBase2Minimum,
     EurostoxxBase2Closing,
     EurostoxxBase2Volume 
    )
    
    SELECT 
    T2.EurostoxxBase1TradeDate,
     CASE T2.num % 2 WHEN 0 THEN T.EurostoxxBase1Opening END AS EurostoxxBase1Opening,
     CASE T2.num % 2 WHEN 0 THEN CASE
      WHEN T.EurostoxxBase1Maximum > T2.EurostoxxBase1Maximum THEN T.EurostoxxBase1Maximum
      
      WHEN T.EurostoxxBase1Maximum < T2.EurostoxxBase1Maximum THEN T2.EurostoxxBase1Maximum
      
      ELSE T.EurostoxxBase1Maximum
      END
      ELSE NULL
     END AS MaxB2,
     
     CASE T2.num % 2 WHEN 0 THEN CASE
      WHEN T.EurostoxxBase1Minimum > T2.EurostoxxBase1Minimum THEN T2.EurostoxxBase1Minimum
      
      WHEN T.EurostoxxBase1Minimum < T2.EurostoxxBase1Minimum THEN T.EurostoxxBase1Minimum ELSE T.EurostoxxBase1Minimum
      END
      ELSE NULL
     END AS MinB2,
     
     CASE T2.num % 2 WHEN 0 THEN T2.EurostoxxBase1Closing END AS EurostoxxBase1Closing,
     
     CASE T2.num % 2 WHEN 0 THEN T.EurostoxxBase1Volume + T2.EurostoxxBase1Volume END AS EurostoxxBase1Volume
    
    FROM CTE AS T
    INNER JOIN CTE AS T2
    ON T.num = T2.num - 1
    WHERE T2.num % 2 = 0;

    dimanche 9 mai 2010 10:50
  • Apres avoir creer une Table 2 minutes a partir d'une table ou les donnees sont fourniees en base 1 minute,

    Je cherche a creer la base 3 minutes: le code ne marche pas pour le moment, si jamais quelqu'un pouvait m'aider ce serait super

    Merci

    CREATE TABLE EurostoxxBase3
    (
     EurostoxxBase3TradeDate    smalldatetime,
     EurostoxxBase3Opening     float,
     EurostoxxBase3Maximum     float,
     EurostoxxBase3Minimum     float,
     EurostoxxBase3Closing     float,
     EurostoxxBase3Volume     float
    );
     
     
    WITH CTE
    AS
    (
     SELECT cte.EurostoxxBase1ContractType, cte.EurostoxxBase1TradeDate, 
        cte.EurostoxxBase1Opening, cte.EurostoxxBase1Maximum,
        cte.EurostoxxBase1Minimum, cte.EurostoxxBase1Closing, cte.EurostoxxBase1Volume, 
        ROW_NUMBER() OVER(ORDER BY EurostoxxBase1TradeDate) AS num
     FROM EurostoxxBase1 cte
    )
    
    INSERT INTO EurostoxxBase3
    (
     EurostoxxBase3TradeDate,
     EurostoxxBase3Opening,
     EurostoxxBase3Maximum,
     EurostoxxBase3Minimum,
     EurostoxxBase3Closing,
     EurostoxxBase3Volume 
    )
    
    SELECT 
    T3.EurostoxxBase1TradeDate,
     CASE T2.num % 2 WHEN 0 THEN T.EurostoxxBase1Opening END AS EurostoxxBase1Opening,
     
     CASE T2.num % 2 WHEN 0 THEN CASE
     
      WHEN T.EurostoxxBase1Maximum > T2.EurostoxxBase1Maximum and
        T.EurostoxxBase1Maximum > T3.EurostoxxBase1Maximum and
        T2.EurostoxxBase1Maximum < T3.EurostoxxBase1Maximum
      THEN T.EurostoxxBase1Maximum
      
      WHEN T.EurostoxxBase1Maximum > T2.EurostoxxBase1Maximum and
        T.EurostoxxBase1Maximum > T3.EurostoxxBase1Maximum and
        T2.EurostoxxBase1Maximum > T3.EurostoxxBase1Maximum
      THEN T.EurostoxxBase1Maximum
      
      WHEN T.EurostoxxBase1Maximum < T2.EurostoxxBase1Maximum and
        T.EurostoxxBase1Maximum < T3.EurostoxxBase1Maximum and
        T2.EurostoxxBase1Maximum < T3.EurostoxxBase1Maximum
      THEN T2.EurostoxxBase1Maximum
      
      WHEN T.EurostoxxBase1Maximum < T2.EurostoxxBase1Maximum and
        T.EurostoxxBase1Maximum > T3.EurostoxxBase1Maximum and
        T2.EurostoxxBase1Maximum > T3.EurostoxxBase1Maximum
      THEN T2.EurostoxxBase1Maximum
      
      WHEN T.EurostoxxBase1Maximum < T2.EurostoxxBase1Maximum and
        T.EurostoxxBase1Maximum < T3.EurostoxxBase1Maximum and
        T2.EurostoxxBase1Maximum < T3.EurostoxxBase1Maximum
      THEN T3.EurostoxxBase1Maximum
      
      WHEN T.EurostoxxBase1Maximum < T2.EurostoxxBase1Maximum and
        T.EurostoxxBase1Maximum < T3.EurostoxxBase1Maximum and
        T2.EurostoxxBase1Maximum > T3.EurostoxxBase1Maximum
      THEN T3.EurostoxxBase1Maximum
      
      END
      ELSE NULL
     END AS MaxB2,
     
     CASE T2.num % 3 WHEN 0 THEN CASE
      WHEN T.EurostoxxBase1Minimum > T2.EurostoxxBase1Minimum and
        T.EurostoxxBase1Minimum > T3.EurostoxxBase1Minimum and
        T2.EurostoxxBase1Minimum < T3.EurostoxxBase1Minimum
      THEN T2.EurostoxxBase1Minimum
      
      WHEN T.EurostoxxBase1Minimum > T2.EurostoxxBase1Minimum and
        T.EurostoxxBase1Minimum > T3.EurostoxxBase1Minimum and
        T2.EurostoxxBase1Minimum > T3.EurostoxxBase1Minimum
      THEN T3.EurostoxxBase1Minimum
      
      WHEN T.EurostoxxBase1Minimum < T2.EurostoxxBase1Minimum and
        T.EurostoxxBase1Minimum < T3.EurostoxxBase1Minimum and
        T2.EurostoxxBase1Minimum < T3.EurostoxxBase1Minimum
      THEN T.EurostoxxBase1Minimum
      
      WHEN T.EurostoxxBase1Minimum < T2.EurostoxxBase1Minimum and
        T.EurostoxxBase1Minimum > T3.EurostoxxBase1Minimum and
        T2.EurostoxxBase1Minimum > T3.EurostoxxBase1Minimum
      THEN T3.EurostoxxBase1Minimum
      
      WHEN T.EurostoxxBase1Minimum < T2.EurostoxxBase1Minimum and
        T.EurostoxxBase1Minimum < T3.EurostoxxBase1Minimum and
        T2.EurostoxxBase1Minimum < T3.EurostoxxBase1Minimum
      THEN T2.EurostoxxBase1Minimum
      
      WHEN T.EurostoxxBase1Minimum < T2.EurostoxxBase1Minimum and
        T.EurostoxxBase1Minimum < T3.EurostoxxBase1Minimum and
        T2.EurostoxxBase1Minimum > T3.EurostoxxBase1Minimum
      THEN T.EurostoxxBase1Minimum
    
     
      END
      ELSE NULL
     END AS MinB2,
     
     CASE T2.num % 2 WHEN 0 THEN T3.EurostoxxBase1Closing END AS EurostoxxBase1Closing,
     
     CASE T2.num % 2 WHEN 0 THEN T.EurostoxxBase1Volume + T2.EurostoxxBase1Volume + T3.EurostoxxBase1Volume END AS EurostoxxBase1Volume
    
    FROM CTE AS T
    INNER JOIN CTE AS T2 ON T.num = T2.num - 1
    INNER JOIN CTE AS T3 ON T2.num = T3.num - 1
    WHERE T2.num % 2 = 0;

    dimanche 9 mai 2010 10:53