Solutions pour le petit jeu des requêtes SQL

IV. Solutions - 4° partie▲

IV-A. Solution au problème n° 30 - Nombre de jours contigus▲

Comme nous l'avons dit, il faut s'aider d'une table des dates, comme ceci :

Sélectionnez

CREATE TABLE T_CALENDRIER_CLD
(CLD_DATE DATE NOT NULL PRIMARY KEY);

Sélectionnez

INSERT INTO T_CALENDRIER_CLD VALUES ('2003-12-30');
INSERT INTO T_CALENDRIER_CLD VALUES ('2003-12-31');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-01');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-02');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-03');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-04');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-05');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-06');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-07');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-08');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-09');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-10');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-11');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-12');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-13');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-14');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-15');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-16');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-17');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-18');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-19');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-20');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-21');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-22');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-23');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-24');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-25');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-26');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-27');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-28');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-29');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-30');
INSERT INTO T_CALENDRIER_CLD VALUES ('2004-01-31');

Voici une solution :

Sélectionnez

SELECT T1.DateInterval AS DateDebut, 
       CAST((SELECT MIN(T2.DateInterval) 
             FROM   (SELECT DC1.PNG_DATE as DateInterval, 'D' as TypeDate
                     FROM   T_CALENDRIER_CLD TD1
                            INNER JOIN T_CALENDRIER_CLD TD2
                                  ON TD1.CLD_DATE -1 = TD2.CLD_DATE 
                            LEFT OUTER JOIN T_PLANNING_PNG DC1
                                  ON DC1.PNG_DATE = TD1.CLD_DATE 
                            LEFT OUTER JOIN T_PLANNING_PNG DC2
                                  ON DC2.PNG_DATE = TD2.CLD_DATE 
                     WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL
                     UNION
                     SELECT DC1.PNG_DATE, 'F' 
                     FROM   T_CALENDRIER_CLD TD1
                            INNER JOIN T_CALENDRIER_CLD TD2
                                  ON TD1.CLD_DATE +1 = TD2.CLD_DATE 
                            LEFT OUTER JOIN T_PLANNING_PNG DC1
                                  ON DC1.PNG_DATE = TD1.CLD_DATE 
                            LEFT OUTER JOIN T_PLANNING_PNG DC2
                                  ON DC2.PNG_DATE = TD2.CLD_DATE
                            WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL) T2
             WHERE  TypeDate = 'F'
               AND  T2.DateInterval >= T1.DateInterval
             GROUP  BY T1.DateInterval ) AS FLOAT) - CAST(T1.DateInterval AS FLOAT) + 1 AS NbJours
FROM   (SELECT DC1.PNG_DATE as DateInterval, 'D' as TypeDate
        FROM   T_CALENDRIER_CLD TD1
               INNER JOIN T_CALENDRIER_CLD TD2
                    ON TD1.CLD_DATE -1 = TD2.CLD_DATE 
               LEFT OUTER JOIN T_PLANNING_PNG DC1
                    ON DC1.PNG_DATE = TD1.CLD_DATE 
               LEFT OUTER JOIN T_PLANNING_PNG DC2
                    ON DC2.PNG_DATE = TD2.CLD_DATE 
        WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL
        UNION
        SELECT DC1.PNG_DATE, 'F' 
        FROM   T_CALENDRIER_CLD TD1
               INNER JOIN T_CALENDRIER_CLD TD2
                    ON TD1.CLD_DATE +1 = TD2.CLD_DATE 
               LEFT OUTER JOIN T_PLANNING_PNG DC1
                    ON DC1.PNG_DATE = TD1.CLD_DATE 
               LEFT OUTER JOIN T_PLANNING_PNG DC2
                    ON DC2.PNG_DATE = TD2.CLD_DATE 
        WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL) T1
WHERE  TypeDate = 'D'

Il va falloir maintenant l'expliquer en la décortiquant...

Tout d'abord, supposons que votre SGBDR soit capable de transformer une date en nombre, par exemple en nombre à virgule flottante (FLOAT) et que la différence entre deux dates ainsi transformées donne le nombre de jour... D'ailleurs si ce n'est pas le cas, il suffit d'utiliser le type INTERVAL de la norme SQL et faire une différence en jour pour que l'on revienne au même !

Ce point étant acquis, voyons comment notre requête est construite...

La première idée a été de calculer tous les débuts de périodes de jours consécutifs et la seconde la fin de ces mêmes périodes. Une fois cela traité il ne suffit plus que de calculer la différence en nombre de jours entre ces deux dates, à condition d'avoir mis en relation les bonnes périodes !

IV-A-1. Étape n°1 : On recherche les débuts de plage ayant des dates contiguës▲

Sélectionnez

SELECT DC1.PNG_DATE as DateInterval, 'D' as TypeDate
FROM   T_CALENDRIER_CLD TD1
       INNER JOIN T_CALENDRIER_CLD TD2
            ON TD1.CLD_DATE -1 = TD2.CLD_DATE 
       LEFT OUTER JOIN T_PLANNING_PNG DC1
            ON DC1.PNG_DATE = TD1.CLD_DATE 
       LEFT OUTER JOIN T_PLANNING_PNG DC2
            ON DC2.PNG_DATE = TD2.CLD_DATE 
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL;

Notez le -1 dans la première jointure qui permet de voir le jour d'avant. Si votre SGBDR ne l'accepte pas, rectifiez la jointure comme suit :

Sélectionnez

ON TD1.CLD_DATE -1 DAY = TD2.CLD_DATE

Notez aussi le filtre pour lequel on recherche une date existante pour le planning avec le fait que dans ce même planning la date précédent n'existe pas !

Voici ce que donne cette requête :

Sélectionnez

DateInterval TypeDate 
------------ -------- 
2004-01-01   D
2004-01-15   D
2004-01-30   D

Dans la colonne TypeDate, nous avons mis "D" pour début.

Notez qu'une autre variante, peut être plus performante consiste à utiliser le EXISTS :

Sélectionnez

SELECT CLD_DATE as DateInterval, 'D' as TypeDate
FROM   T_CALENDRIER_CLD TD
WHERE  NOT EXISTS (SELECT *
                   FROM   T_PLANNING_PNG
                   WHERE  PNG_DATE = TD.CLD_DATE -1 )   
  AND  EXISTS     (SELECT *
                   FROM   T_PLANNING_PNG
                   WHERE  PNG_DATE = TD.CLD_DATE) ;

IV-A-2. Étape n° 2 : la seconde requête n'est guère plus complexe, pour trouver les fins de périodes▲

Sélectionnez

SELECT DC1.PNG_DATE, 'F' 
FROM   T_CALENDRIER_CLD TD1
       INNER JOIN T_CALENDRIER_CLD TD2
            ON TD1.CLD_DATE +1 = TD2.CLD_DATE 
       LEFT OUTER JOIN T_PLANNING_PNG DC1
            ON DC1.PNG_DATE = TD1.CLD_DATE 
       LEFT OUTER JOIN T_PLANNING_PNG DC2
            ON DC2.PNG_DATE = TD2.CLD_DATE 
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL ;

Sélectionnez

PNG_DATE
------------------------------------------------------ ---- 
2004-01-03 00:00:00.000                                F
2004-01-18 00:00:00.000                                F
2004-01-30 00:00:00.000                                F

IV-A-3. Étape n°3 : Voici maintenant le temps venu de joindre ces éléments... Cela semble plus facile à dire qu'à faire !▲

Commençons simple, en unionant ces deux requêtes :

Sélectionnez

SELECT DC1.PNG_DATE as DateInterval, 'D' as TypeDate
FROM   T_CALENDRIER_CLD TD1
       INNER JOIN T_CALENDRIER_CLD TD2
            ON TD1.CLD_DATE -1 = TD2.CLD_DATE 
       LEFT OUTER JOIN T_PLANNING_PNG DC1
            ON DC1.PNG_DATE = TD1.CLD_DATE 
       LEFT OUTER JOIN T_PLANNING_PNG DC2
            ON DC2.PNG_DATE = TD2.CLD_DATE 
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL
UNION
SELECT DC1.PNG_DATE, 'F' 
FROM   T_CALENDRIER_CLD TD1
       INNER JOIN T_CALENDRIER_CLD TD2
            ON TD1.CLD_DATE +1 = TD2.CLD_DATE 
       LEFT OUTER JOIN T_PLANNING_PNG DC1
            ON DC1.PNG_DATE = TD1.CLD_DATE 
       LEFT OUTER JOIN T_PLANNING_PNG DC2
            ON DC2.PNG_DATE = TD2.CLD_DATE 
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL

Sélectionnez

DateInterval    TypeDate 
--------------- -------- 
2004-01-01      D
2004-01-03      F
2004-01-15      D
2004-01-18      F
2004-01-30      D
2004-01-30      F

Nous ne sommes pas très avancés, car il va falloir synthétiser des paires de lignes différentes en une seule ligne.

Pour plus de compréhension, créons une vue à partir de cette requête :

Sélectionnez

CREATE VIEW V_DAT
AS
SELECT DC1.PNG_DATE as DateInterval, 'D' as TypeDate
FROM   T_CALENDRIER_CLD TD1
       INNER JOIN T_CALENDRIER_CLD TD2
            ON TD1.CLD_DATE -1 = TD2.CLD_DATE 
       LEFT OUTER JOIN T_PLANNING_PNG DC1
            ON DC1.PNG_DATE = TD1.CLD_DATE 
       LEFT OUTER JOIN T_PLANNING_PNG DC2
            ON DC2.PNG_DATE = TD2.CLD_DATE 
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL
UNION
SELECT DC1.PNG_DATE, 'F' 
FROM   T_CALENDRIER_CLD TD1
       INNER JOIN T_CALENDRIER_CLD TD2
            ON TD1.CLD_DATE +1 = TD2.CLD_DATE 
       LEFT OUTER JOIN T_PLANNING_PNG DC1
            ON DC1.PNG_DATE = TD1.CLD_DATE 
       LEFT OUTER JOIN T_PLANNING_PNG DC2
            ON DC2.PNG_DATE = TD2.CLD_DATE 
WHERE  DC1.PNG_DATE IS NOT NULL AND DC2.PNG_DATE IS NULL

Il est maintenant assez facile de mettre en correspondance les débuts de plage et les fins :

Sélectionnez

SELECT T1.DateInterval AS DateDebut, 
       (SELECT MIN(T2.DateInterval) 
        FROM   V_DAT T2
        WHERE  TypeDate = 'F'
          AND  T2.DateInterval >= T1.DateInterval
        GROUP  BY T1.DateInterval ) As DateFin
FROM   V_DAT T1
WHERE  TypeDate = 'D' ;

Sélectionnez

DateDebut          DateFin
------------------ -------------------
2004-01-01         2004-01-03 
2004-01-15         2004-01-18 
2004-01-30         2004-01-30

IV-A-4. Étape n°4 : la suite, et donc la touche finale, est on ne peut plus simple puisqu'il s'agit de compter le nombre de jours de chaque période ainsi extraite !▲

Sélectionnez

SELECT T1.DateInterval AS DateDebut, 
       CAST((SELECT MIN(T2.DateInterval) 
             FROM   V_DAT T2
             WHERE  TypeDate = 'F'
               AND  T2.DateInterval >= T1.DateInterval
             GROUP  BY T1.DateInterval ) AS FLOAT) 
       - CAST(T1.DateInterval AS FLoat) +1 As Nb_jours
                                
FROM   V_DAT T1
WHERE  TypeDate = 'D' ;

Sélectionnez

DateDebut         Nb_jours
----------------- ----------------
2004-01-01        3.0
2004-01-15        4.0
2004-01-30        1.0

Bien entendu vous pouvez à nouveau substituer à la soustra id=""ction des dates transtypée en FLOAT, un calcul d'INTERVAL si votre SGBDR le supporte !

IV-B. Solution au problème n° 31 - plus proche valeur ▲

La valeur la plus proche signifie la valeur avec lequel l'écart entre la date souhaitée et l'ensemble des dates possible est minimum. Pour cela il faut calculer une différence en jours et comme cette différence peut se faire en plus ou en moins (avant ou après la date demandée) alors il faut obtenir la valeur absolue de cet écart.

Ceci se traduit par la requête SQL :

Sélectionnez

SELECT *
FROM   T_RENDEZ_VOUS_RDV
WHERE  ABS(RDV_DATE - CAST('2008-01-12' AS DATE) AS DAY) 
       = (SELECT MIN(ABS(RDV_DATE - CAST('2008-01-12' AS DATE) AS DAY))
          FROM T_RENDEZ_VOUS_RDV);

Qui donne bien la bonne réponse dans le cas ou la date est le 12 janvier 2008.

Cependant avec le 23 janvier 2008... tout change. Nous nous retrouvons avec :

Sélectionnez

RDV_DATE
---------------
2008-01-22
2008-01-24

Car il y a deux dates à égale distance de celle demandée.

Qu'à cela ne tienne, il suffit de rajouter que l'on veut celle d'avant ou d'après en appliquant un min ou un max !

La requête devient :

Sélectionnez

SELECT MIN(RDV_DATE) AS RDV_DATE
FROM   T_RENDEZ_VOUS_RDV
WHERE  ABS(RDV_DATE - CAST('2008-01-12' AS DATE) AS DAY) 
       = (SELECT MIN(ABS(RDV_DATE - CAST('2008-01-23' AS DATE) AS DAY))
          FROM T_RENDEZ_VOUS_RDV);

Voici cette même requête traduite pour MS SQL Server :

Sélectionnez

SELECT MIN(RDV_DATE) AS RDV_DATE
FROM   T_RENDEZ_VOUS_RDV
WHERE  ABS(DATEDIFF(day, RDV_DATE, CAST('2008-01-23' id="" AS DATETIME))) 
       = (SELECT MIN(ABS(DATEDIFF(day, RDV_DATE, CAST('2008-01-12' AS DATETIME))))
          FROM T_RENDEZ_VOUS id=""_RDV);

IV-C. Solution au problème n° 32 - primes de salaires▲

Tout d'abord, pour "éclater" la prime sur le nombre d'échéances il nous faut une table des nombres de 1 à n.

Sélectionnez

CREATE TABLE T_NUM
(NUM INT);

Sélectionnez

INSERT INTO T_NUM VALUES (1);
INSERT INTO T_NUM VALUES (2);
INSERT INTO T_NUM VALUES (3);
INSERT INTO T_NUM VALUES (4);
INSERT INTO T_NUM VALUES (5);
INSERT INTO T_NUM VALUES (6);
INSERT INTO T_NUM VALUES (7);
INSERT INTO T_NUM VALUES (8);
INSERT INTO T_NUM VALUES (9);
INSERT INTO T_NUM VALUES (10);
INSERT INTO T_NUM VALUES (11);
INSERT INTO T_NUM VALUES (12);

Une première impression est donnée par la requête suivante :

Sélectionnez

SELECT PRM_EMPLOYE, CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) AS PART_PRIME, NUM
FROM   T_PRIME_PRM
       INNER JOIN T_NUM
             ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES

Sélectionnez

PRM_EMPLOYE      PART_PRIME         NUM
---------------- ------------------ ----------- 
JAK              1000.00            1
POL              333.33             1
POL              333.33             2
POL              333.33             3
LUC              83.33              1
LUC              83.33              2
LUC              83.33              3
LUC              83.33              4
LUC              83.33              5
LUC              83.33              6
LUC              83.33              7
LUC              83.33              8
LUC              83.33              9
LUC              83.33              10
LUC              83.33              11
LUC              83.33              12

Il suffit d'ajouter à cette table l'écart constaté entre la somme des échéances et la prime originelle. Voici comment on peut calculer cet écart :

Sélectionnez

SELECT PRM_EMPLOYE, CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) AS PART_PRIME, NUM,
       PRM_MONTANT - (SELECT MAX(NUM) * ( CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) )
                FROM   T_PRIME_PRM
                       INNER JOIN T_NUM
                             ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES
                WHERE  P.PRM_EMPLOYE = PRM_EMPLOYE
                GROUP  BY PRM_MONTANT, PRN_NB_ECHEANCES) as ECART  
FROM   T_PRIME_PRM P
       INNER JOIN T_NUM
             ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES

Sélectionnez

PRM_EMPLOYE      PART_PRIME         NUM         ECART
---------------- ------------------ ----------- ------------------------------ 
JAK              1000.00            1           .00
POL              333.33             1           .01
POL              333.33             2           .01
POL              333.33             3           .01
LUC              83.33              1           .04
LUC              83.33              2           .04
LUC              83.33              3           .04
LUC              83.33              4           .04
LUC              83.33              5           .04
LUC              83.33              6           .04
LUC              83.33              7           .04
LUC              83.33              8           .04
LUC              83.33              9           .04
LUC              83.33              10          .04
LUC              83.33              11          .04
LUC              83.33              12          .04

Simplifions-nous la vie pour la suite en transformant cette requête en vue :

Sélectionnez

CREATE VIEW V_PRIMES
AS
SELECT PRM_EMPLOYE, CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) AS PART_PRIME, NUM,
       PRM_MONTANT - (SELECT MAX(NUM) * ( CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) )
                FROM   T_PRIME_PRM
                       INNER JOIN T_NUM
                             ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES
                WHERE  P.PRM_EMPLOYE = PRM_EMPLOYE
                GROUP  BY PRM_MONTANT, PRN_NB_ECHEANCES) as ECART  
FROM   T_PRIME_PRM P
       INNER JOIN T_NUM
             ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES ;

Dès lors, on peut maintenant placer la prime au niveau de la dernière échéance, comme suit :

Sélectionnez

SELECT PRM_EMPLOYE, PART_PRIME, NUM,
       (SELECT ECART
        FROM   V_PRIMES
        WHERE  PRM_EMPLOYE = P1.PRM_EMPLOYE
          AND PART_PRIME = P1.PART_PRIME
        GROUP  BY PART_PRIME, PRM_EMPLOYE, ECART
        HAVING MAX(NUM) = P1.NUM) REPORT_ECART
FROM   V_PRIMES P1 ;

Sélectionnez

PRM_EMPLOYE      PART_PRIME         NUM         REPORT_ECART
---------------- ------------------ ----------- ------------------------------ 
JAK              1000.00            1           .00
POL              333.33             1           NULL
POL              333.33             2           NULL
POL              333.33             3           .01
LUC              83.33              1           NULL
LUC              83.33              2           NULL
LUC              83.33              3           NULL
LUC              83.33              4           NULL
LUC              83.33              5           NULL
LUC              83.33              6           NULL
LUC              83.33              7           NULL
LUC              83.33              8           NULL
LUC              83.33              9           NULL
LUC              83.33              10          NULL
LUC              83.33              11          NULL
LUC              83.33              12          .04

Il ne suffit plus maintenant que d'une addition, avec dénullification de l'écart !

Sélectionnez

SELECT PRM_EMPLOYE, NUM, PART_PRIME + 
       COALESCE((SELECT ECART
                 FROM   V_PRIMES
                 WHERE  PRM_EMPLOYE = P1.PRM_EMPLOYE
                   AND PART_PRIME = P1.PART_PRIME
                 GROUP  BY PART_PRIME, PRM_EMPLOYE, ECART
                 HAVING MAX(NUM) = P1.NUM), 0) AS PART_PRIME_EXACTE
FROM   V_PRIMES P1 ;

Sélectionnez

PRM_EMPLOYE      NUM         PART_PRIME_EXACTE
---------------- ----------- ------------------------------- 
JAK              1           1000.00
POL              1           333.33
POL              2           333.33
POL              3           333.34
LUC              1           83.33
LUC              2           83.33
LUC              3           83.33
LUC              4           83.33
LUC              5           83.33
LUC              6           83.33
LUC              7           83.33
LUC              8           83.33
LUC              9           83.33
LUC              10          83.33
LUC              11          83.33
LUC              12          83.37

Sans l'apport de la vue, la requête s'avère un peu indigeste :

Sélectionnez

SELECT PRM_EMPLOYE, NUM, PART_PRIME + 
       COALESCE((SELECT ECART
                 FROM   (SELECT PRM_EMPLOYE, CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) AS PART_PRIME, NUM,
                                PRM_MONTANT - (SELECT MAX(NUM) * ( CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) )
                                               FROM   T_PRIME_PRM
                                                      INNER JOIN T_NUM
                                                            ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES
                                               WHERE  P.PRM_EMPLOYE = PRM_EMPLOYE
                                               GROUP  BY PRM_MONTANT, PRN_NB_ECHEANCES) as ECART  
                         FROM   T_PRIME_PRM P
                                INNER JOIN T_NUM
                                      ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES) AS T
                 WHERE  PRM_EMPLOYE = P1.PRM_EMPLOYE
                   AND PART_PRIME = P1.PART_PRIME
                 GROUP  BY PART_PRIME, PRM_EMPLOYE, ECART
                 HAVING MAX(NUM) = P1.NUM), 0) AS PART_PRIME_EXACTE
FROM   (SELECT PRM_EMPLOYE, CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) AS PART_PRIME, NUM,
                PRM_MONTANT - (SELECT MAX(NUM) * ( CAST(PRM_MONTANT / PRN_NB_ECHEANCES AS DECIMAL (16, 2)) )
                               FROM   T_PRIME_PRM
                                      INNER JOIN T_NUM
                                            ON NUM BETWEEN 1 AND PRN_NB_ECHEANCES
                               WHERE  P.PRM_EMPLOYE = PRM_EMPLOYE
                               GROUP  BY PRM_MONTANT, PRN_NB_ECHEANCE id=""S) as ECART  
        FROM   T_PRIME_PRM P
               INNER JOIN T_NUM
                     ON NUM BETWEEN 1 AND PRN_NB_ECH id=""EANCES) P1 ;

IV-D. Solution au problème n° 33 - affectations comptables▲

Une première idée est de réaliser l'affectation en réalisant un rapprochement entre compte et "masque" de compte à l'aide de l'opérateur LIKE. C'est d'ailleurs pour cela que j'ai remplacé les ? par des _ ...

Sélectionnez

SELECT CPT_COMPTE, CPT_ANA, CPT_MONTANT, AFC_AFFECT, AFC_COMPTE, AFC_ANA
FROM   T_COMPTE_CPT C
       INNER JOIN T_AFFECTATION_AFC A
             ON     CPT_COMPTE LIKE AFC_COMPTE
                AND CPT_ANA    LIKE AFC_ANA
ORDER BY 1, 2 ;

Sélectionnez

CPT_COMPTE CPT_ANA CPT_MONTANT        AFC_AFFECT AFC_COMPTE AFC_ANA 
---------- ------- ------------------ ---------- ---------- ------- 
612000     12345   130.40             T124       6_____     _____
612000     12345   130.40             T125       612___     _____   -->(Regle 612??? ?????)

612000     26045   260.50             T125       612___     _____   -->(Règle 612??? ?????)
612000     26045   260.50             T124       6_____     _____
612000     26045   260.50             T033       6_____     26___

612101     33556   330.50             T028       6_____     __5__
612101     33556   330.50             T124       6_____     _____
612101     33556   330.50             T125       612___     _____
612101     33556   330.50             T126       612___     __5__   -->(Règle 612??? ??5??)

654000     26346   33.55              T124       6_____     _____
654000     26346   33.55              T033       6_____     26___   -->(Règle 6????? 26???)

654102     28333   180.10             T124       6_____     _____   -->(Règle 6????? ?????)

Comme nous le constatons, cette méthode nous donne différentes possibilités d'affectation des lignes comptables aux comptes. Il faut donc trouver une astuce pour faire en sorte que l'affectation soit la plus complète possible.

Demandons nous quelle est la règle sous-jacente au terme "meilleur" ?

Visiblement il s'agit de considérer que les chiffres les plus à gauche sont plus importants que les chiffres les plus à droite.

Dans ce cas, il faut trouver un moyen de valuer la complétude du masque de compte.

C'est généralement par le calcul d'un "poids" que l'on s'en tire. Un poids, c'est un facteur, un coefficient qui indique que telle information est plus sûre que telle autre.

Dans notre cas nous pouvons donner un poids à l'aide de puissances de 2. Si le caractère est présent alors la puissance du rang du caractère est prise en compte. Sinon, c'est un zéro. Finalement on fait la somme de toutes ces puissances afin de donner le poids globale du masque de compte.

Ainsi pour : 612_ _ _ _ _5_ _, le poids sera le suivant :

Sélectionnez

rang   2^rang-1   caractère   prise en compte    total
------ ---------- ----------- ------------------ ----------
11     1024       6           oui                1024
10     512        1           oui                512
9      256        2           oui                256
8      128        _           non                0
7      64         _           non                0
6      32         _           non                0
5      16         _           non                0
4      8          _           non                0
3      4          5           oui                4
2      2          _           non                0
1      1          _           non                0
                                                 ===========
TOTAL                                            1796

On peut donc calculer chaque poids de chaque masque et ajouter cela à la table T_AFFECTATION_AFC en lui adjoignant une nouvelle colonne :

Sélectionnez

ALTER TABLE T_AFFECTATION_AFC 
  ADD AFC_PRIORITE SMALLINT;

Il faut ensuite mettre à jour ligne à ligne la table des affectations, comme ceci :

Sélectionnez

UPDATE T_AFFECTATION_AFC SET AFC_PRIORITE = 1024 WHERE AFC_AFFECT = 'T124';
UPDATE T_AFFECTATION_AFC SET AFC_PRIORITE = 1028 WHERE AFC_AFFECT = 'T028';
UPDATE T_AFFECTATION_AFC SET AFC_PRIORITE = 1048 WHERE AFC_AFFECT = 'T033';
UPDATE T_AFFECTATION_AFC SET AFC_PRIORITE = 1792 WHERE AFC_AFFECT = 'T125';
UPDATE T_AFFECTATION_AFC SET AFC_PRIORITE = 1796 WHERE AFC_AFFECT = 'T126';

Bien entendu, certaines personnes vont dire que je triche puisque j'ajoute à la table d'affectation une colonne... Mais tous d'abord disons que si nous violons la règle, nous n'en violons pas l'esprit qui consiste à préserver l'intégrité de la base de données. Souvenez vous de la règle de Codd n°9, intitulée " Indépendance logique des données " : les applications et les programmes terminaux sont logiquement inaffectés, quand des changements de tous ordres, préservant les informations et qui ne leur portent théoriquement aucune atteinte, sont apportés aux tables de base (restructuration). En particulier l'ajout d'une table, d'une vue ou d'une colonne à une table existante ne modifie en rien le comportement des applications qui utilisent cette base de données (ou tout du moins, cela devrait être vrai !).

Cependant et pour les puriste nous verrons en final comment respecter notre serment et montrerons une solution sans restructuration de l'existant ni ajout d'une fonction utilisateur.

Parce que tant qu'à violer nos propres règles, allons-y franchement en utilisant une UDF afin de faciliter le calculs des poids. Je vous la livre écrite pour MS SQL Server :

Sélectionnez

CREATE FUNCTION F_POIDS_COMPTE (@DATA VARCHAR(32))
RETURNS INTEGER
AS
BEGIN
   IF @DATA IS NULL RETURN NULL;
   DECLARE @OUT INT, @I INT;
   SELECT @OUT = 0, @I = LEN(@DATA);
   WHILE @I >= 1
   BEGIN
      IF SUBSTRING(@DATA, @I, 1) <> '_'
         SET @OUT = @OUT + POWER(2, LEN(@DATA) - @I);
      SET @I = @I - 1;
   END;
   RETURN @OUT;
END;

Dès lors et afin de départager nos différentes possibilité d'affectation, il ne s'agit plus que de retenir chaque ligne ayant le poids le plus fort, ce qui peut se traduire par :

Sélectionnez

SELECT CPT_COMPTE, CPT_ANA, CPT_MONTANT, AFC_AFFECT
FROM   T_COMPTE_CPT C
       INNER JOIN T_AFFECTATION_AFC A
             ON     CPT_COMPTE LIKE AFC_COMPTE
                AND CPT_ANA    LIKE AFC_ANA
WHERE  AFC_PRIORITE = (SELECT MAX(AFC_PRIORITE)
                       FROM   T_COMPTE_CPT C2
                              INNER JOIN T_AFFECTATION_AFC A2
                                    ON     CPT_COMPTE LIKE AFC_COMPTE
                                       AND CPT_ANA    LIKE AFC_ANA
                       WHERE  C2.CPT_COMPTE = C.CPT_COMPTE
                         AND  C2.CPT_ANA = C.CPT_ANA
                         AND  C2.CPT_MONTANT = C.CPT_MONTANT);

Et qui donne le bon résultat :

Sélectionnez

CPT_COMPTE CPT_ANA CPT_MONTANT        AFC_AFFECT 
---------- ------- ------------------ ---------- 
654102     28333   180.10             T124
654000     26346   33.55              T033
612101     33556   330.50             T126
612000     26045   260.50             T125
612000     12345   130.40             T125

Comment nous débarrasser de la colonne en sus dans la table des affectations ?

Simple, il suffit de rajouter une table avec toutes les données nécessaires... Cela ne nous est pas interdit !

Sélectionnez

CREATE TABLE T_AFFECTATION2_AFC
(AFC_COMPTE          CHAR(6),
 AFC_ANA             CHAR(5),
 AFC_AFFECT          CHAR(4),
 AFC_PRIORITE        SMALLINT);

Sélectionnez

INSERT INTO T_AFFECTATION2_AFC VALUES ('6_____', '_____', 'T124', 1024);
INSERT INTO T_AFFECTATION2_AFC VALUES ('6_____', '__5__', 'T028', 1028);
INSERT INTO T_AFFECTATION2_AFC VALUES ('6_____', '26___', 'T033', 1048);
INSERT INTO T_AFFECTATION2_AFC VALUES ('612___', '_____', 'T125', 1792);
INSERT INTO T_AFFECTATION2_AFC VALUES ('612___', '__5__', 'T126', 1796);

Et au fait... comment calculer les poids sans utiliser une UDF ?

Avec une requête... tout simplement !

Sélectionnez

SELECT AFC_COMPTE, AFC_ANA, SUM(POID) AS POIDS
FROM   (SELECT AFC_COMPTE, AFC_ANA, 
               CASE
                  WHEN SUBSTRING(AFC_COMPTE+AFC_ANA, NUM, 1) = '_' THEN 0
                  ELSE POWER(2, LEN(AFC_COMPTE+AFC_ANA)- NUM)
               END AS POID 
        FROM   T_AFFECTATION_AFC
               INNER JOIN T_NUM
                     ON NUM BETWEEN 1 AND LEN(AFC_COMPTE+AFC_ANA)) T
GROUP BY AFC_COMPTE, AFC_ANA

Pour la mystérieuse table T_NUM qui apparait bizarrement ici, souvenez vous qu'elle apparait dans de nombreux exercices et de trouve définit comme suit :

Sélectionnez

CREATE TABLE T_NUM (NUM INT);

Sélectionnez

INSERT INTO T_NUM VALUES (1);
INSERT INTO T_NUM VALUES (2);
INSERT INTO T_NUM VALUES (3);
INSERT INTO T_NUM VALUES (4);
INSERT INTO T_NUM VALUES (5);
INSERT INTO T_NUM VALUES (6);
INSERT INTO T_NUM VALUES (7);
INSERT INTO T id=""_NUM VALUES (8);
INSERT INTO T_NUM VALUES (9);
INSERT INTO T_NUM VALUES (10);
INSERT INTO T_NUM VALUES (11);
INSERT INTO T_NUM id="" VALUES (12);
...

IV-E. Solution au problème n° 33 - précédents (ou suivants)▲

Commençons par numéroter les lignes de cette table en utilisant une inéqui auto jointure avec comptage :

Sélectionnez

SELECT T1.PSV, COUNT(*) AS N
FROM   T_PRECSUIV_PSV T1
       INNER JOIN T_PRECSUIV_PSV T2
             ON T1.PSV >= T2.PSV
GROUP  BY  T1.PSV

Sélectionnez

PSV  N
---- ----------- 
AAA  1
BBB  2
CCC  3
DDD  4

Dès lors nous savons que la précédente est celle ayant un comptage de -1 par rapport à N, la solution devient triviale :

Sélectionnez

SELECT T1.PSV, COUNT(*) AS N,  COUNT(*) - 1 AS PRECEDENT
FROM   T_PRECSUIV_PSV T1
       INNER JOIN T_PRECSUIV_PSV T2
             ON T1.PSV >= T2.PSV
GROUP  BY  T1.PSV

Sélectionnez

PSV  N           PRECEDENT
---- ----------- ----------- 
AAA  1           0
BBB  2           1
CCC  3           2
DDD  4           3

Sauf que la ligne n° 0 n'existant pas, il nous faut faire :

Sélectionnez

SELECT T1.PSV, COUNT(*) AS N,  NULLIF(COUNT(*) - 1, 0) AS PRECEDENT
FROM   T_PRECSUIV_PSV T1
       INNER JOIN T_PRECSUIV_PSV T2
             ON T1.PSV >= T2.PSV
GROUP  BY  T1.PSV

Sélectionnez

PSV  N           PRECEDENT
---- ----------- ----------- 
AAA  1           NULL
BBB  2           1
CCC  3           2
DDD  4           3

Ce qui est une bonne solution.

En définitive, la solution globale est fournie par la requête suivante :

Sélectionnez

SELECT PSV1 AS VALEUR_ACTUELLE, PSV2 AS VALEUR PRECEDENTE
FROM   (SELECT T1.PSV AS PSV1, COUNT(T1.PSV) AS N1
        FROM   T_PRECSUIV_PSV T1
               LEFT OUTER  JOIN T_PRECSUIV_PSV T2
                    ON T1.PSV >= T2.PSV
        GROUP  BY  T1.PSV) AS T1
       LEFT OUTER JOIN (SELECT T1.PSV AS PSV2, COUNT(T1.PSV) + 1 AS N2
                        FROM   T_PRECSUIV_PSV T1
                               LEFT OUTER  JOIN T_PRECSUIV_PSV T2
                                    ON T1.PSV >= T2.PSV
                        GROUP  BY  T1.PSV) T2
            ON T1. N1 = T2.N2

Sélectionnez

VALEUR_ACTUELLE VALEUR PRECEDENTE 
--------------- -----------------
AAA             NULL
BBB             AAA
CCC             BBB
DDD             CCC

Mais cette solution est-elle suffisante ?

Par exemple, rajoutons à notre table les données suivantes :

Sélectionnez

INSERT INTO T_PRECSUIV_PSV VALUES ('BBB');
INSERT INTO T_PRECSUIV_PSV VALUES ('CCC');
INSERT INTO T_PRECSUIV_PSV VALUES ('CCC');
INSERT INTO T_PRECSUIV_PSV VALUES ('CCC');

Notre requête précédente donne :

Sélectionnez

VALEUR_ACTUELLE VALEUR_PRECEDENT 
--------------- ---------------- 
AAA             NULL
BBB             NULL
CCC             NULL
DDD             NULL

Tentons une autre approche et essayons de chercher pour chaque ligne la valeur immédiatement inférieure.

Cela peut se traduire par :

Sélectionnez

SELECT DISTINCT PSV AS VALEUR_ACTUELLE,
       (SELECT MAX(PSV)
        FROM   T_PRECSUIV_PSV
        WHERE  PSV < T.PSV) AS VALEUR_PRECEDENTE
FROM   T_PRECSUIV_PSV AS T ;

Sélectionnez

VALEUR_ACTUELLE VALEUR_PRECEDENTE 
--------------- --------------- 
AAA             NULL
BBB             AAA
CCC             BBB
DDD             CCC

Cette solution, apparemment simpliste gagne en force et en souplesse !

Bien entendu le chaînage avant/arrière (précédent/suivant) est facile à réaliser :

Sélectionnez

SELECT DISTINCT PSV AS VALEUR_ACTUELLE,
       (SELECT MAX(PSV)
        FROM   T_PRECSUIV_PSV
        WHERE  PSV < T.PSV) AS VALEUR_PRECEDENTE,
       (SELECT MIN(PSV)
        FROM   T_PRECSUIV_PSV
        WHERE  PSV > T.PSV) AS VALEUR_SUIVANTE
FROM   T_PRECSUIV_PSV AS T ;

Sélectionnez

VALEUR_ACTUELLE VALEUR_PRECEDENTE VALEUR_SUIVANTE 
--------------- ----------------- --------------- 
A id=""AA             NULL              BBB
BBB             AAA               CCC
CCC             BBB               DDD
DDD            id=""  CCC               NULL

IV-F. Solution au problème n° 35 - matchs et victoires▲

Obtenir le nombre de matches des joueurs relève d'une simple astuce de jointure pour le comptage. Une jointure conditionnelle avec un OR dans la condition de jointure. C'est ce que l'on appelle une non équi jointure :

Sélectionnez

SELECT JOR_NOM, COUNT(*) AS NB_MATCHS
FROM   T_MATCH_MCH M
       INNER JOIN T_JOUEUR_JOR J
              ON J.JOR_ID = M.JOR_ID1 OR J.JOR_ID = M.JOR_ID2
GROUP  BY JOR_NOM ;

Sélectionnez

JOR_NOM                          NB_MATCHS
-------------------------------- ----------- 
Camus                            2
Dupont                           3
Mercier                          3

Obtenir le nombre de victoire des joueurs relève d'une autre astuce de jointure presque aussi simple :

Sélectionnez

SELECT JOR_NOM, COUNT(*) AS NB_VICTOIRE
FROM   T_MATCH_MCH M
       INNER JOIN T_JOUEUR_JOR J
              ON J.JOR_ID = CASE 
                               WHEN MCH_SCORE_JOUEUR1 > MCH_SCORE_JOUEUR2 THEN M.JOR_ID1
                               WHEN MCH_SCORE_JOUEUR2 > MCH_SCORE_JOUEUR1 THEN M.JOR_ID2
                            END
GROUP  BY JOR_NOM ;

Sélectionnez

JOR_NOM                          NB_VICTOIRE 
-------------------------------- ----------- 
Camus                            2
Dupont                           1
Mercier                          1

En fait la touche finale est bien plus simple qu'il n'y parait, car il suffit de mettre ces deux précédentes requêtes en tant que table dérivées dans la clause FROM d'une super requête et de les joindre sur le nom du joueur.

Mais comme il peut se trouver des homonymes, nous allons ajouter à nos deux requête l'identifiant du joueur et réaliser la jointure dessus, comme ceci :

Sélectionnez

SELECT T1.JOR_ID, T1.JOR_NOM, NB_MATCHS, NB_VICTOIRE
FROM   (SELECT JOR_ID, JOR_NOM, COUNT(*) AS NB_MATCHS
        FROM   T_MATCH_MCH M
               INNER JOIN T_JOUEUR_JOR J
                      ON J.JOR_ID = M.JOR_ID1 OR J.JOR_ID = M.JOR_ID2
        GROUP  BY JOR_ID, JOR_NOM) AS T1
       LEFT OUTER JOIN (SELECT JOR_ID, JOR_NOM, COUNT(*) AS NB_VICTOIRE
                        FROM   T_MATCH_MCH M
                               INNER JOIN T_JOUEUR_JOR J
                                      ON J.JOR_ID = CASE 
                                                       WHEN MCH_SCORE_JOUEUR1 > MCH_SCORE_JOUEUR2 THEN M.JOR_ID1
                                                       WHEN MCH_SCORE_JOUEUR2 > MCH_SCORE_JOUEUR1 THEN M.JOR_ID2
                                                    END
                        GROUP  BY JOR_ID, JOR_NOM) AS T2
            ON T1.JOR_ID = T2.JOR_ID

Sélectionnez

JOR_ID      JOR_NOM                          NB_MATCHS   NB_VICTOIRE 
----------- -------------------------------- ----------- ----------- 
2           Camus                            2           2
1           Dupont                           3           1
3           Mercier                          3           1

id="" Cependant, je vous laisse deviner pourquoi j'ai préféré une jointure externe gauche à une jointure id="" interne !

IV-G. Solution au problème n° 36 - tri bâtard▲

Une idée serait de faire un transtypage en entier et de forcer la clause de tri ORDER BY à opérer sur cette transformation, comme ceci :

Sélectionnez

SELECT *
FROM   T_PARCELLE_PCL
ORDER  BY CAST(PCL_NUM AS INTEGER) ;

Cependant, cette requête part en erreur du fait de la présence de caractères non numériques...

En fait, l'astuce consiste à effectuer un double tri :

premièrement sur la longueur de la donnée ;
deuxièmement sur la donnée elle même.

Sélectionnez

SELECT *
FROM   T_PARCELLE_PCL
ORDER  BY CHARACTER_LENGTH(PCL_NUM), PCL_NUM ;

Sélectionnez

PCL_NUM
 id=""----------------
1
2
3
11
21
22
111
221
28A
28B

Et le id="" tout est joué !

IV-H. Solution au problème n° 37 - vote contraint ▲

Un savant calcul combinant la multiplication et la dénullification est, semble-t-il, la chose la plus courte possible.

Sélectionnez

ALTER TABLE T_SCHNORKELZIG_SKZ ADD CONSTRAINT CK 
CHECK((COALESCE(SKZ_MARC+1,1)*COALESCE(SKZ_PAUL+1,1)*COALESCE(SKZ_JEAN+1,1)*COALESCE(SKZ_LEON+1,1))IN(2,6,24,120))

Remarquez que dans le IN figurent des nombres issus de la fonction factorielle... Pour y parvenir et comme la multiplication par 1 ne change pas les données de départ, on ajoute 1 à la valeur du vote et on met 1 en cas de NULL. Ainsi, les combinaisons possibles sont :

Sélectionnez

2  * 3 * 4 * 5 = 120
2  * 3 * 4 = 24
2  * 3 =  6
2 = 2

Notez que la longueur de la clause à partir du mot CHECK fait 114 caractères... id="" Avez-vous fait moins ?

IV-I. Solution au problème n° 38 - propriété bien gardée▲

La solution à ce problème fait partie des requêtes les plus complexes à élaborer. En général les requêtes de manipulation du temps et en particulier celle portant sur des intervalles (durées); sont très difficile à mettre au point.

Voici cependant la décomposition de cette requête. Nous allons d'abord voire une solution globale sous forme de CTE puis nous allons la décortiquer petit à petit pour en comprendre son mécanisme.

IV-I-1. Étape préliminaire▲

Tout d'abord il nous faut rajouter quelque information à notre ensemble de données : en effet nous devons borner l'intervalle de traitement de nos données entre le 1er septembre 2008 à 0h et le 7 septembre 2008 à 23h59'59". Pour cela nous devons soit couper les périodes chevauchant ces limites, soit si aucune période ne les chevauche, les créer de toute pièce avec une durée nulle.

Rajouter à l'ensemble de départ deux périodes fictives permettant de borner l'intervalle souhaité :

Sélectionnez

CREATE TABLE T_BORNE_BRN (DEBUT DATETIME, FIN DATETIME)
INSERT INTO  T_BORNE_BRN VALUES ('20080901 00:00:00', '20080901 00:00:00')
INSERT INTO  T_BORNE_BRN VALUES ('20080907 23:59:59', '20080907 23:59:59')

La solution sous forme de CTE :

Sélectionnez

WITH
V_GDN
-- Les périodes nouvelles de gardiennage avec les périodes bornes recoupées sur la durée de l'intervalle à scruter
AS
(
SELECT CASE 
          WHEN GDN_DATEHEURE_DEBUT < (SELECT MIN(DEBUT) FROM T_BORNE_BRN) THEN (SELECT MIN(DEBUT) FROM T_BORNE_BRN)
          ELSE GDN_DATEHEURE_DEBUT
       END AS GDN_DATEHEURE_DEBUT,
       CASE 
          WHEN GDN_DATEHEURE_FIN > (SELECT MAX(DEBUT) FROM T_BORNE_BRN) THEN (SELECT MAX(DEBUT) FROM T_BORNE_BRN)
          ELSE GDN_DATEHEURE_FIN
       END AS GDN_DATEHEURE_FIN
FROM   dbo.T_GARDIENNAGE_GDN
UNION  ALL
SELECT DEBUT, FIN
FROM   T_BORNE_BRN
),
-- calcul des périodes agrégées de gardiennage, c'est à dire quelles sont les périodes pendant lesquelles il y a continuellement quelqu'un ?
GDT
AS
-- on combine toutes les heures de début avec toutes les heures de fin qui sont supérieures à celle de début 
-- on réalise en fait une matrice "triangulaire"
(
SELECT DISTINCT GD1.GDN_DATEHEURE_DEBUT, GD2.GDN_DATEHEURE_FIN
FROM   V_GDN GD1
       INNER JOIN V_GDN GD2
             ON GD1.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN 
-- élimination des trous entre GD1.GDN_DATEHEURE_DEBUT et GD2.GDN_DATEHEURE_FIN
  AND NOT EXISTS 
      (SELECT *
       FROM   V_GDN GD3
       WHERE  GD3.GDN_DATEHEURE_DEBUT  > GD1.GDN_DATEHEURE_FIN
         AND  GD3.GDN_DATEHEURE_DEBUT  <= GD2.GDN_DATEHEURE_DEBUT
         AND  NOT EXISTS 
              (SELECT *
               FROM   V_GDN GD4
               WHERE  GD4.GDN_DATEHEURE_DEBUT  <  GD3.GDN_DATEHEURE_DEBUT
                 AND  GD4.GDN_DATEHEURE_FIN    >= GD3.GDN_DATEHEURE_DEBUT
              )
      )
-- élimination des sous périodes incluse dans les périodes de base
  AND NOT EXISTS 
      (SELECT *
       FROM   V_GDN GD5
       WHERE (    GD5.GDN_DATEHEURE_DEBUT <  GD1.GDN_DATEHEURE_DEBUT 
              AND GD5.GDN_DATEHEURE_FIN   >= GD1.GDN_DATEHEURE_DEBUT )
          OR (    GD5.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN  
              AND GD5.GDN_DATEHEURE_FIN   >  GD2.GDN_DATEHEURE_FIN   )
       )
)
-- il faut maintenant inverser le problème en trouvant les périodes ou il n'y a personne !
SELECT T1.GDN_DATEHEURE_FIN AS GDN_DATEHEURE_DEBUT,
       MIN(T2.GDN_DATEHEURE_DEBUT) AS GDN_DATEHEURE_FIN
FROM   GDT T1
       INNER JOIN GDT T2
             ON  T2.GDN_DATEHEURE_DEBUT > T1.GDN_DATEHEURE_FIN
GROUP BY T1.GDN_DATEHEURE_FIN

Comment cette requête fonctionne-t-elle ? Nous allons la décomposer étape par étape et comparer à chaque étape les lignes originelles avec celles calculées...

IV-I-2. Étape 1 : bornage des tranches sur la période à scruter▲

Sélectionnez

CREATE VIEW V_GDN
AS
SELECT CASE 
          WHEN GDN_DATEHEURE_DEBUT < (SELECT MIN(DEBUT) FROM T_BORNE_BRN) THEN (SELECT MIN(DEBUT) FROM T_BORNE_BRN)
          ELSE GDN_DATEHEURE_DEBUT
       END AS GDN_DATEHEURE_DEBUT,
       CASE 
          WHEN GDN_DATEHEURE_FIN > (SELECT MAX(DEBUT) FROM T_BORNE_BRN) THEN (SELECT MAX(DEBUT) FROM T_BORNE_BRN)
          ELSE GDN_DATEHEURE_FIN
       END AS GDN_DATEHEURE_FIN
FROM   dbo.T_GARDIENNAGE_GDN
UNION  ALL
SELECT DEBUT, FIN
FROM   T_BORNE_BRN

-- requête de comparaison des tranches existantes avec les nouvelles tranches "recadrées" suite au bornage sur période à scruter
SELECT *
FROM   
(
SELECT DISTINCT
       T1.GDN_DATEHEURE_DEBUT AS DEBUT_GARDIENNAGE,
       T1.GDN_DATEHEURE_FIN AS FIN_GARDIENNAGE,
       T2.GDN_DATEHEURE_DEBUT AS DEBUT_NOUVEAU,
       T2.GDN_DATEHEURE_FIN AS FIN_NOUVEAU
FROM   T_GARDIENNAGE_GDN T1
       FULL OUTER JOIN V_GDN T2
            ON T1.GDN_DATEHEURE_DEBUT = T2.GDN_DATEHEURE_DEBUT
               AND T1.GDN_DATEHEURE_FIN = T2.GDN_DATEHEURE_FIN
) AS T
ORDER  BY COALESCE(DEBUT_GARDIENNAGE,  DEBUT_NOUVEAU), 
          COALESCE(FIN_GARDIENNAGE, FIN_NOUVEAU)

Sélectionnez

DEBUT_GARDIENNAGE FIN_GARDIENNAGE  DEBUT_NOUVEAU    FIN_NOUVEAU
----------------- ---------------- ---------------- ----------------
NULL              NULL             2008-09-01 00:00 2008-09-01 00:00  --> tranche ajoutée en début
2008-09-01 07:30  2008-09-01 14:30 2008-09-01 07:30 2008-09-01 14:30
2008-09-01 12:30  2008-09-02 00:00 2008-09-01 12:30 2008-09-02 00:00
2008-09-01 22:30  2008-09-02 08:00 2008-09-01 22:30 2008-09-02 08:00
2008-09-01 23:30  2008-09-02 11:00 2008-09-01 23:30 2008-09-02 11:00
2008-09-02 06:00  2008-09-02 17:30 2008-09-02 06:00 2008-09-02 17:30
2008-09-02 08:30  2008-09-02 16:00 2008-09-02 08:30 2008-09-02 16:00
2008-09-03 06:00  2008-09-03 17:30 2008-09-03 06:00 2008-09-03 17:30
2008-09-03 08:30  2008-09-03 16:00 2008-09-03 08:30 2008-09-03 16:00
2008-09-03 18:00  2008-09-04 04:30 2008-09-03 18:00 2008-09-04 04:30
2008-09-03 22:30  2008-09-04 10:00 2008-09-03 22:30 2008-09-04 10:00
2008-09-04 06:00  2008-09-04 17:30 2008-09-04 06:00 2008-09-04 17:30
2008-09-04 14:30  2008-09-05 08:30 2008-09-04 14:30 2008-09-05 08:30
2008-09-05 08:30  2008-09-05 21:30 2008-09-05 08:30 2008-09-05 21:30
2008-09-05 09:30  2008-09-05 12:00 2008-09-05 09:30 2008-09-05 12:00
2008-09-06 08:30  2008-09-06 13:30 2008-09-06 08:30 2008-09-06 13:30
2008-09-06 10:30  2008-09-06 21:00 2008-09-06 10:30 2008-09-06 21:00
2008-09-06 12:00  2008-09-06 22:30 2008-09-06 12:00 2008-09-06 22:30
2008-09-06 16:30  2008-09-07 10:30 2008-09-06 16:30 2008-09-07 10:30
2008-09-06 21:00  2008-09-07 10:30 2008-09-06 21:00 2008-09-07 10:30
2008-09-07 06:00  2008-09-07 12:30 2008-09-07 06:00 2008-09-07 12:30
2008-09-07 18:30  2008-09-08 06:30 2008-09-07 18:30 2008-09-07 23:59  --> tranche modifiée pour sa borne fin
NULL              NULL             2008-09-07 23:59 2008-09-07 23:59  --> tranche ajoutée en fin

IV-I-3. Étape 2 : calcul de toutes les périodes possibles partant des périodes existantes▲

On met en relation toutes les fins avec tous les début à condition de respecter l'ordre chronologie que le début soit avant (ou égal : tranche de durée nulle) à la fin

Sélectionnez

CREATE VIEW GDT1
AS
-- on combine toutes les heures de début avec toutes les heures de fin qui sont supérieures à celle de début 
-- on réalise en fait une matrice "triangulaire" (éclatement de périodes...)
SELECT DISTINCT GD1.GDN_DATEHEURE_DEBUT, GD2.GDN_DATEHEURE_FIN
FROM   V_GDN GD1
       INNER JOIN V_GDN GD2
             ON GD1.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN

Comparaison des tranches existantes avec les nouvelles tranches éclatées

Sélectionnez

SELECT *
FROM   
(
SELECT DISTINCT
       T1.GDN_DATEHEURE_DEBUT AS DEBUT_GARDIENNAGE,
       T1.GDN_DATEHEURE_FIN AS FIN_GARDIENNAGE,
       T2.GDN_DATEHEURE_DEBUT AS DEBUT_NOUVEAU,
       T2.GDN_DATEHEURE_FIN AS FIN_NOUVEAU
FROM   T_GARDIENNAGE_GDN T1
       FULL OUTER JOIN GDT1 T2
            ON T1.GDN_DATEHEURE_DEBUT = T2.GDN_DATEHEURE_DEBUT
               AND T1.GDN_DATEHEURE_FIN = T2.GDN_DATEHEURE_FIN
) AS T
ORDER  BY COALESCE(DEBUT_GARDIENNAGE,  DEBUT_NOUVEAU), 
          COALESCE(FIN_GARDIENNAGE, FIN_NOUVEAU) ;

Sélectionnez

DEBUT_GARDIENNAGE FIN_GARDIENNAGE  DEBUT_NOUVEAU    FIN_NOUVEAU
----------------- ---------------- ---------------- ----------------
NULL              NULL             2008-09-01 00:00 2008-09-01 00:00
NULL              NULL             2008-09-01 00:00 2008-09-01 14:30
NULL              NULL             2008-09-01 00:00 2008-09-02 00:00
NULL              NULL             2008-09-01 00:00 2008-09-02 08:00
NULL              NULL             2008-09-01 00:00 2008-09-02 11:00
NULL              NULL             2008-09-01 00:00 2008-09-02 16:00
NULL              NULL             2008-09-01 00:00 2008-09-02 17:30
NULL              NULL             2008-09-01 00:00 2008-09-03 16:00
NULL              NULL             2008-09-01 00:00 2008-09-03 17:30
NULL              NULL             2008-09-01 00:00 2008-09-04 04:30
NULL              NULL             2008-09-01 00:00 2008-09-04 10:00
NULL              NULL             2008-09-01 00:00 2008-09-04 17:30
NULL              NULL             2008-09-01 00:00 2008-09-05 08:30
NULL              NULL             2008-09-01 00:00 2008-09-05 12:00
NULL              NULL             2008-09-01 00:00 2008-09-05 21:30
NULL              NULL             2008-09-01 00:00 2008-09-06 13:30
NULL              NULL             2008-09-01 00:00 2008-09-06 21:00
NULL              NULL             2008-09-01 00:00 2008-09-06 22:30
NULL              NULL             2008-09-01 00:00 2008-09-07 10:30
NULL              NULL             2008-09-01 00:00 2008-09-07 12:30
NULL              NULL             2008-09-01 00:00 2008-09-07 23:59
2008-09-01 07:30  2008-09-01 14:30 2008-09-01 07:30 2008-09-01 14:30
NULL              NULL             2008-09-01 07:30 2008-09-02 00:00
NULL              NULL             2008-09-01 07:30 2008-09-02 08:00
NULL              NULL             2008-09-01 07:30 2008-09-02 11:00
NULL              NULL             2008-09-01 07:30 2008-09-02 16:00
NULL              NULL             2008-09-01 07:30 2008-09-02 17:30
NULL              NULL             2008-09-01 07:30 2008-09-03 16:00
NULL              NULL             2008-09-01 07:30 2008-09-03 17:30
NULL              NULL             2008-09-01 07:30 2008-09-04 04:30
NULL              NULL             2008-09-01 07:30 2008-09-04 10:00
NULL              NULL             2008-09-01 07:30 2008-09-04 17:30
NULL              NULL             2008-09-01 07:30 2008-09-05 08:30
NULL              NULL             2008-09-01 07:30 2008-09-05 12:00
NULL              NULL             2008-09-01 07:30 2008-09-05 21:30
NULL              NULL             2008-09-01 07:30 2008-09-06 13:30
NULL              NULL             2008-09-01 07:30 2008-09-06 21:00
NULL              NULL             2008-09-01 07:30 2008-09-06 22:30
NULL              NULL             2008-09-01 07:30 2008-09-07 10:30
NULL              NULL             2008-09-01 07:30 2008-09-07 12:30
NULL              NULL             2008-09-01 07:30 2008-09-07 23:59
NULL              NULL             2008-09-01 12:30 2008-09-01 14:30
2008-09-01 12:30  2008-09-02 00:00 2008-09-01 12:30 2008-09-02 00:00
NULL              NULL             2008-09-01 12:30 2008-09-02 08:00
NULL              NULL             2008-09-01 12:30 2008-09-02 11:00
NULL              NULL             2008-09-01 12:30 2008-09-02 16:00
NULL              NULL             2008-09-01 12:30 2008-09-02 17:30
NULL              NULL             2008-09-01 12:30 2008-09-03 16:00
NULL              NULL             2008-09-01 12:30 2008-09-03 17:30
NULL              NULL             2008-09-01 12:30 2008-09-04 04:30
NULL              NULL             2008-09-01 12:30 2008-09-04 10:00
NULL              NULL             2008-09-01 12:30 2008-09-04 17:30
NULL              NULL             2008-09-01 12:30 2008-09-05 08:30
NULL              NULL             2008-09-01 12:30 2008-09-05 12:00
NULL              NULL             2008-09-01 12:30 2008-09-05 21:30
NULL              NULL             2008-09-01 12:30 2008-09-06 13:30
NULL              NULL             2008-09-01 12:30 2008-09-06 21:00
NULL              NULL             2008-09-01 12:30 2008-09-06 22:30
NULL              NULL             2008-09-01 12:30 2008-09-07 10:30
NULL              NULL             2008-09-01 12:30 2008-09-07 12:30
NULL              NULL             2008-09-01 12:30 2008-09-07 23:59
NULL              NULL             2008-09-01 22:30 2008-09-02 00:00
2008-09-01 22:30  2008-09-02 08:00 2008-09-01 22:30 2008-09-02 08:00
NULL              NULL             2008-09-01 22:30 2008-09-02 11:00
NULL              NULL             2008-09-01 22:30 2008-09-02 16:00
NULL              NULL             2008-09-01 22:30 2008-09-02 17:30
NULL              NULL             2008-09-01 22:30 2008-09-03 16:00
NULL              NULL             2008-09-01 22:30 2008-09-03 17:30
NULL              NULL             2008-09-01 22:30 2008-09-04 04:30
NULL              NULL             2008-09-01 22:30 2008-09-04 10:00
NULL              NULL             2008-09-01 22:30 2008-09-04 17:30
NULL              NULL             2008-09-01 22:30 2008-09-05 08:30
NULL              NULL             2008-09-01 22:30 2008-09-05 12:00
NULL              NULL             2008-09-01 22:30 2008-09-05 21:30
NULL              NULL             2008-09-01 22:30 2008-09-06 13:30
NULL              NULL             2008-09-01 22:30 2008-09-06 21:00
NULL              NULL             2008-09-01 22:30 2008-09-06 22:30
NULL              NULL             2008-09-01 22:30 2008-09-07 10:30
NULL              NULL             2008-09-01 22:30 2008-09-07 12:30
NULL              NULL             2008-09-01 22:30 2008-09-07 23:59
NULL              NULL             2008-09-01 23:30 2008-09-02 00:00
NULL              NULL             2008-09-01 23:30 2008-09-02 08:00
2008-09-01 23:30  2008-09-02 11:00 2008-09-01 23:30 2008-09-02 11:00
NULL              NULL             2008-09-01 23:30 2008-09-02 16:00
NULL              NULL             2008-09-01 23:30 2008-09-02 17:30
NULL              NULL             2008-09-01 23:30 2008-09-03 16:00
NULL              NULL             2008-09-01 23:30 2008-09-03 17:30
NULL              NULL             2008-09-01 23:30 2008-09-04 04:30
NULL              NULL             2008-09-01 23:30 2008-09-04 10:00
NULL              NULL             2008-09-01 23:30 2008-09-04 17:30
NULL              NULL             2008-09-01 23:30 2008-09-05 08:30
NULL              NULL             2008-09-01 23:30 2008-09-05 12:00
NULL              NULL             2008-09-01 23:30 2008-09-05 21:30
NULL              NULL             2008-09-01 23:30 2008-09-06 13:30
NULL              NULL             2008-09-01 23:30 2008-09-06 21:00
NULL              NULL             2008-09-01 23:30 2008-09-06 22:30
NULL              NULL             2008-09-01 23:30 2008-09-07 10:30
NULL              NULL             2008-09-01 23:30 2008-09-07 12:30
NULL              NULL             2008-09-01 23:30 2008-09-07 23:59
NULL              NULL             2008-09-02 06:00 2008-09-02 08:00
NULL              NULL             2008-09-02 06:00 2008-09-02 11:00
NULL              NULL             2008-09-02 06:00 2008-09-02 16:00
2008-09-02 06:00  2008-09-02 17:30 2008-09-02 06:00 2008-09-02 17:30
NULL              NULL             2008-09-02 06:00 2008-09-03 16:00
NULL              NULL             2008-09-02 06:00 2008-09-03 17:30
NULL              NULL             2008-09-02 06:00 2008-09-04 04:30
NULL              NULL             2008-09-02 06:00 2008-09-04 10:00
NULL              NULL             2008-09-02 06:00 2008-09-04 17:30
NULL              NULL             2008-09-02 06:00 2008-09-05 08:30
NULL              NULL             2008-09-02 06:00 2008-09-05 12:00
NULL              NULL             2008-09-02 06:00 2008-09-05 21:30
NULL              NULL             2008-09-02 06:00 2008-09-06 13:30
NULL              NULL             2008-09-02 06:00 2008-09-06 21:00
NULL              NULL             2008-09-02 06:00 2008-09-06 22:30
NULL              NULL             2008-09-02 06:00 2008-09-07 10:30
NULL              NULL             2008-09-02 06:00 2008-09-07 12:30
NULL              NULL             2008-09-02 06:00 2008-09-07 23:59
NULL              NULL             2008-09-02 08:30 2008-09-02 11:00
2008-09-02 08:30  2008-09-02 16:00 2008-09-02 08:30 2008-09-02 16:00
NULL              NULL             2008-09-02 08:30 2008-09-02 17:30
NULL              NULL             2008-09-02 08:30 2008-09-03 16:00
NULL              NULL             2008-09-02 08:30 2008-09-03 17:30
NULL              NULL             2008-09-02 08:30 2008-09-04 04:30
NULL              NULL             2008-09-02 08:30 2008-09-04 10:00
NULL              NULL             2008-09-02 08:30 2008-09-04 17:30
NULL              NULL             2008-09-02 08:30 2008-09-05 08:30
NULL              NULL             2008-09-02 08:30 2008-09-05 12:00
NULL              NULL             2008-09-02 08:30 2008-09-05 21:30
NULL              NULL             2008-09-02 08:30 2008-09-06 13:30
NULL              NULL             2008-09-02 08:30 2008-09-06 21:00
NULL              NULL             2008-09-02 08:30 2008-09-06 22:30
NULL              NULL             2008-09-02 08:30 2008-09-07 10:30
NULL              NULL             2008-09-02 08:30 2008-09-07 12:30
NULL              NULL             2008-09-02 08:30 2008-09-07 23:59
NULL              NULL             2008-09-03 06:00 2008-09-03 16:00
2008-09-03 06:00  2008-09-03 17:30 2008-09-03 06:00 2008-09-03 17:30
NULL              NULL             2008-09-03 06:00 2008-09-04 04:30
NULL              NULL             2008-09-03 06:00 2008-09-04 10:00
NULL              NULL             2008-09-03 06:00 2008-09-04 17:30
NULL              NULL             2008-09-03 06:00 2008-09-05 08:30
NULL              NULL             2008-09-03 06:00 2008-09-05 12:00
NULL              NULL             2008-09-03 06:00 2008-09-05 21:30
NULL              NULL             2008-09-03 06:00 2008-09-06 13:30
NULL              NULL             2008-09-03 06:00 2008-09-06 21:00
NULL              NULL             2008-09-03 06:00 2008-09-06 22:30
NULL              NULL             2008-09-03 06:00 2008-09-07 10:30
NULL              NULL             2008-09-03 06:00 2008-09-07 12:30
NULL              NULL             2008-09-03 06:00 2008-09-07 23:59
2008-09-03 08:30  2008-09-03 16:00 2008-09-03 08:30 2008-09-03 16:00
NULL              NULL             2008-09-03 08:30 2008-09-03 17:30
NULL              NULL             2008-09-03 08:30 2008-09-04 04:30
NULL              NULL             2008-09-03 08:30 2008-09-04 10:00
NULL              NULL             2008-09-03 08:30 2008-09-04 17:30
NULL              NULL             2008-09-03 08:30 2008-09-05 08:30
NULL              NULL             2008-09-03 08:30 2008-09-05 12:00
NULL              NULL             2008-09-03 08:30 2008-09-05 21:30
NULL              NULL             2008-09-03 08:30 2008-09-06 13:30
NULL              NULL             2008-09-03 08:30 2008-09-06 21:00
NULL              NULL             2008-09-03 08:30 2008-09-06 22:30
NULL              NULL             2008-09-03 08:30 2008-09-07 10:30
NULL              NULL             2008-09-03 08:30 2008-09-07 12:30
NULL              NULL             2008-09-03 08:30 2008-09-07 23:59
2008-09-03 18:00  2008-09-04 04:30 2008-09-03 18:00 2008-09-04 04:30
NULL              NULL             2008-09-03 18:00 2008-09-04 10:00
NULL              NULL             2008-09-03 18:00 2008-09-04 17:30
NULL              NULL             2008-09-03 18:00 2008-09-05 08:30
NULL              NULL             2008-09-03 18:00 2008-09-05 12:00
NULL              NULL             2008-09-03 18:00 2008-09-05 21:30
NULL              NULL             2008-09-03 18:00 2008-09-06 13:30
NULL              NULL             2008-09-03 18:00 2008-09-06 21:00
NULL              NULL             2008-09-03 18:00 2008-09-06 22:30
NULL              NULL             2008-09-03 18:00 2008-09-07 10:30
NULL              NULL             2008-09-03 18:00 2008-09-07 12:30
NULL              NULL             2008-09-03 18:00 2008-09-07 23:59
NULL              NULL             2008-09-03 22:30 2008-09-04 04:30
2008-09-03 22:30  2008-09-04 10:00 2008-09-03 22:30 2008-09-04 10:00
NULL              NULL             2008-09-03 22:30 2008-09-04 17:30
NULL              NULL             2008-09-03 22:30 2008-09-05 08:30
NULL              NULL             2008-09-03 22:30 2008-09-05 12:00
NULL              NULL             2008-09-03 22:30 2008-09-05 21:30
NULL              NULL             2008-09-03 22:30 2008-09-06 13:30
NULL              NULL             2008-09-03 22:30 2008-09-06 21:00
NULL              NULL             2008-09-03 22:30 2008-09-06 22:30
NULL              NULL             2008-09-03 22:30 2008-09-07 10:30
NULL              NULL             2008-09-03 22:30 2008-09-07 12:30
NULL              NULL             2008-09-03 22:30 2008-09-07 23:59
NULL              NULL             2008-09-04 06:00 2008-09-04 10:00
2008-09-04 06:00  2008-09-04 17:30 2008-09-04 06:00 2008-09-04 17:30
NULL              NULL             2008-09-04 06:00 2008-09-05 08:30
NULL              NULL             2008-09-04 06:00 2008-09-05 12:00
NULL              NULL             2008-09-04 06:00 2008-09-05 21:30
NULL              NULL             2008-09-04 06:00 2008-09-06 13:30
NULL              NULL             2008-09-04 06:00 2008-09-06 21:00
NULL              NULL             2008-09-04 06:00 2008-09-06 22:30
NULL              NULL             2008-09-04 06:00 2008-09-07 10:30
NULL              NULL             2008-09-04 06:00 2008-09-07 12:30
NULL              NULL             2008-09-04 06:00 2008-09-07 23:59
NULL              NULL             2008-09-04 14:30 2008-09-04 17:30
2008-09-04 14:30  2008-09-05 08:30 2008-09-04 14:30 2008-09-05 08:30
NULL              NULL             2008-09-04 14:30 2008-09-05 12:00
NULL              NULL             2008-09-04 14:30 2008-09-05 21:30
NULL              NULL             2008-09-04 14:30 2008-09-06 13:30
NULL              NULL             2008-09-04 14:30 2008-09-06 21:00
NULL              NULL             2008-09-04 14:30 2008-09-06 22:30
NULL              NULL             2008-09-04 14:30 2008-09-07 10:30
NULL              NULL             2008-09-04 14:30 2008-09-07 12:30
NULL              NULL             2008-09-04 14:30 2008-09-07 23:59
NULL              NULL             2008-09-05 08:30 2008-09-05 08:30
NULL              NULL             2008-09-05 08:30 2008-09-05 12:00
2008-09-05 08:30  2008-09-05 21:30 2008-09-05 08:30 2008-09-05 21:30
NULL              NULL             2008-09-05 08:30 2008-09-06 13:30
NULL              NULL             2008-09-05 08:30 2008-09-06 21:00
NULL              NULL             2008-09-05 08:30 2008-09-06 22:30
NULL              NULL             2008-09-05 08:30 2008-09-07 10:30
NULL              NULL             2008-09-05 08:30 2008-09-07 12:30
NULL              NULL             2008-09-05 08:30 2008-09-07 23:59
2008-09-05 09:30  2008-09-05 12:00 2008-09-05 09:30 2008-09-05 12:00
NULL              NULL             2008-09-05 09:30 2008-09-05 21:30
NULL              NULL             2008-09-05 09:30 2008-09-06 13:30
NULL              NULL             2008-09-05 09:30 2008-09-06 21:00
NULL              NULL             2008-09-05 09:30 2008-09-06 22:30
NULL              NULL             2008-09-05 09:30 2008-09-07 10:30
NULL              NULL             2008-09-05 09:30 2008-09-07 12:30
NULL              NULL             2008-09-05 09:30 2008-09-07 23:59
2008-09-06 08:30  2008-09-06 13:30 2008-09-06 08:30 2008-09-06 13:30
NULL              NULL             2008-09-06 08:30 2008-09-06 21:00
NULL              NULL             2008-09-06 08:30 2008-09-06 22:30
NULL              NULL             2008-09-06 08:30 2008-09-07 10:30
NULL              NULL             2008-09-06 08:30 2008-09-07 12:30
NULL              NULL             2008-09-06 08:30 2008-09-07 23:59
NULL              NULL             2008-09-06 10:30 2008-09-06 13:30
2008-09-06 10:30  2008-09-06 21:00 2008-09-06 10:30 2008-09-06 21:00
NULL              NULL             2008-09-06 10:30 2008-09-06 22:30
NULL              NULL             2008-09-06 10:30 2008-09-07 10:30
NULL              NULL             2008-09-06 10:30 2008-09-07 12:30
NULL              NULL             2008-09-06 10:30 2008-09-07 23:59
NULL              NULL             2008-09-06 12:00 2008-09-06 13:30
NULL              NULL             2008-09-06 12:00 2008-09-06 21:00
2008-09-06 12:00  2008-09-06 22:30 2008-09-06 12:00 2008-09-06 22:30
NULL              NULL             2008-09-06 12:00 2008-09-07 10:30
NULL              NULL             2008-09-06 12:00 2008-09-07 12:30
NULL              NULL             2008-09-06 12:00 2008-09-07 23:59
NULL              NULL             2008-09-06 16:30 2008-09-06 21:00
NULL              NULL             2008-09-06 16:30 2008-09-06 22:30
2008-09-06 16:30  2008-09-07 10:30 2008-09-06 16:30 2008-09-07 10:30
NULL              NULL             2008-09-06 16:30 2008-09-07 12:30
NULL              NULL             2008-09-06 16:30 2008-09-07 23:59
NULL              NULL             2008-09-06 21:00 2008-09-06 21:00
NULL              NULL             2008-09-06 21:00 2008-09-06 22:30
2008-09-06 21:00  2008-09-07 10:30 2008-09-06 21:00 2008-09-07 10:30
NULL              NULL             2008-09-06 21:00 2008-09-07 12:30
NULL              NULL             2008-09-06 21:00 2008-09-07 23:59
NULL              NULL             2008-09-07 06:00 2008-09-07 10:30
2008-09-07 06:00  2008-09-07 12:30 2008-09-07 06:00 2008-09-07 12:30
NULL              NULL             2008-09-07 06:00 2008-09-07 23:59
NULL              NULL             2008-09-07 18:30 2008-09-07 23:59
2008-09-07 18:30  2008-09-08 06:30 NULL             NULL
NULL              NULL             2008-09-07 23:59 2008-09-07 23:59

38-D. Étape 3 : on supprime maintenant les périodes incluses dans les autres afin de ne laisser que des périodes non "redondantes"▲

Sélectionnez

CREATE VIEW GDT2
AS
-- on combine toutes les heures de début avec toutes les heures de fin qui sont supérieures à celle de début 
-- on réalise en fait une matrice "triangulaire"
SELECT DISTINCT GD1.GDN_DATEHEURE_DEBUT, GD2.GDN_DATEHEURE_FIN
FROM   V_GDN GD1
       INNER JOIN V_GDN GD2
             ON GD1.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN 

-- élimination des sous périodes incluse dans les périodes de base
  AND NOT EXISTS 
      (SELECT *
       FROM   V_GDN GD5
       WHERE (    GD5.GDN_DATEHEURE_DEBUT <  GD1.GDN_DATEHEURE_DEBUT 
              AND GD5.GDN_DATEHEURE_FIN   >= GD1.GDN_DATEHEURE_DEBUT )
          OR (    GD5.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN  
              AND GD5.GDN_DATEHEURE_FIN   >  GD2.GDN_DATEHEURE_FIN   )
       );

Comparaison des tranches existantes avec les nouvelles tranches éclatées débarrassées de la redondance

Sélectionnez

SELECT *
FROM   
(
SELECT DISTINCT
       T1.GDN_DATEHEURE_DEBUT AS DEBUT_GARDIENNAGE,
       T1.GDN_DATEHEURE_FIN AS FIN_GARDIENNAGE,
       T2.GDN_DATEHEURE_DEBUT AS DEBUT_NOUVEAU,
       T2.GDN_DATEHEURE_FIN AS FIN_NOUVEAU
FROM   T_GARDIENNAGE_GDN T1
       FULL OUTER JOIN GDT2 T2
            ON T1.GDN_DATEHEURE_DEBUT = T2.GDN_DATEHEURE_DEBUT
               AND T1.GDN_DATEHEURE_FIN = T2.GDN_DATEHEURE_FIN
) AS T
ORDER  BY COALESCE(DEBUT_GARDIENNAGE,  DEBUT_NOUVEAU), 
          COALESCE(FIN_GARDIENNAGE, FIN_NOUVEAU)

Sélectionnez

DEBUT_GARDIENNAGE FIN_GARDIENNAGE  DEBUT_NOUVEAU    FIN_NOUVEAU
----------------- ---------------- ---------------- ----------------
NULL              NULL             2008-09-01 00:00 2008-09-01 00:00
NULL              NULL             2008-09-01 00:00 2008-09-02 17:30
NULL              NULL             2008-09-01 00:00 2008-09-03 17:30
NULL              NULL             2008-09-01 00:00 2008-09-05 21:30
NULL              NULL             2008-09-01 00:00 2008-09-07 12:30
NULL              NULL             2008-09-01 00:00 2008-09-07 23:59
2008-09-01 07:30  2008-09-01 14:30 NULL             NULL
NULL              NULL             2008-09-01 07:30 2008-09-02 17:30
NULL              NULL             2008-09-01 07:30 2008-09-03 17:30
NULL              NULL             2008-09-01 07:30 2008-09-05 21:30
NULL              NULL             2008-09-01 07:30 2008-09-07 12:30
NULL              NULL             2008-09-01 07:30 2008-09-07 23:59
2008-09-01 12:30  2008-09-02 00:00 NULL             NULL
2008-09-01 22:30  2008-09-02 08:00 NULL             NULL
2008-09-01 23:30  2008-09-02 11:00 NULL             NULL
2008-09-02 06:00  2008-09-02 17:30 NULL             NULL
2008-09-02 08:30  2008-09-02 16:00 NULL             NULL
2008-09-03 06:00  2008-09-03 17:30 2008-09-03 06:00 2008-09-03 17:30
NULL              NULL             2008-09-03 06:00 2008-09-05 21:30
NULL              NULL             2008-09-03 06:00 2008-09-07 12:30
NULL              NULL             2008-09-03 06:00 2008-09-07 23:59
2008-09-03 08:30  2008-09-03 16:00 NULL             NULL
2008-09-03 18:00  2008-09-04 04:30 NULL             NULL
NULL              NULL             2008-09-03 18:00 2008-09-05 21:30
NULL              NULL             2008-09-03 18:00 2008-09-07 12:30
NULL              NULL             2008-09-03 18:00 2008-09-07 23:59
2008-09-03 22:30  2008-09-04 10:00 NULL             NULL
2008-09-04 06:00  2008-09-04 17:30 NULL             NULL
2008-09-04 14:30  2008-09-05 08:30 NULL             NULL
2008-09-05 08:30  2008-09-05 21:30 NULL             NULL
2008-09-05 09:30  2008-09-05 12:00 NULL             NULL
2008-09-06 08:30  2008-09-06 13:30 NULL             NULL
NULL              NULL             2008-09-06 08:30 2008-09-07 12:30
NULL              NULL             2008-09-06 08:30 2008-09-07 23:59
2008-09-06 10:30  2008-09-06 21:00 NULL             NULL
2008-09-06 12:00  2008-09-06 22:30 NULL             NULL
2008-09-06 16:30  2008-09-07 10:30 NULL             NULL
2008-09-06 21:00  2008-09-07 10:30 NULL             NULL
2008-09-07 06:00  2008-09-07 12:30 NULL             NULL
NULL              NULL             2008-09-07 18:30 2008-09-07 23:59
2008-09-07 18:30  2008-09-08 06:30 NULL             NULL

38-E. Étape 4 : en sus de de débarrasser de la redondance, on se débarrase des tranches faisant des "trous" au sein d'une même période▲

Sélectionnez

CREATE VIEW GDT3
AS
-- on combine toutes les heures de début avec toutes les heures de fin qui sont supérieures à celle de début 
-- on réalise en fait une matrice "triangulaire"
SELECT DISTINCT GD1.GDN_DATEHEURE_DEBUT, GD2.GDN_DATEHEURE_FIN
FROM   V_GDN GD1
       INNER JOIN V_GDN GD2
             ON GD1.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN 

-- élimination des sous périodes incluse dans les périodes de base
  AND NOT EXISTS 
      (SELECT *
       FROM   V_GDN GD5
       WHERE (    GD5.GDN_DATEHEURE_DEBUT <  GD1.GDN_DATEHEURE_DEBUT 
              AND GD5.GDN_DATEHEURE_FIN   >= GD1.GDN_DATEHEURE_DEBUT )
          OR (    GD5.GDN_DATEHEURE_DEBUT <= GD2.GDN_DATEHEURE_FIN  
              AND GD5.GDN_DATEHEURE_FIN   >  GD2.GDN_DATEHEURE_FIN   )
       )
-- élimination des trous entre GD1.GDN_DATEHEURE_DEBUT et GD2.GDN_DATEHEURE_FIN
  AND NOT EXISTS 
      (SELECT *
       FROM   V_GDN GD3
       WHERE  GD3.GDN_DATEHEURE_DEBUT  > GD1.GDN_DATEHEURE_FIN
         AND  GD3.GDN_DATEHEURE_DEBUT  <= GD2.GDN_DATEHEURE_DEBUT
         AND  NOT EXISTS 
              (SELECT *
               FROM   V_GDN GD4
               WHERE  GD4.GDN_DATEHEURE_DEBUT  <  GD3.GDN_DATEHEURE_DEBUT
                 AND  GD4.GDN_DATEHEURE_FIN    >= GD3.GDN_DATEHEURE_DEBUT
              )
      )

Comparaison des tranches existantes avec les nouvelles tranches débarrassées de toutes les scories

Sélectionnez

SELECT *
FROM   
(
SELECT DISTINCT
       T1.GDN_DATEHEURE_DEBUT AS DEBUT_GARDIENNAGE,
       T1.GDN_DATEHEURE_FIN AS FIN_GARDIENNAGE,
       T2.GDN_DATEHEURE_DEBUT AS DEBUT_NOUVEAU,
       T2.GDN_DATEHEURE_FIN AS FIN_NOUVEAU
FROM   T_GARDIENNAGE_GDN T1
       FULL OUTER JOIN GDT3 T2
            ON T1.GDN_DATEHEURE_DEBUT = T2.GDN_DATEHEURE_DEBUT
               AND T1.GDN_DATEHEURE_FIN = T2.GDN_DATEHEURE_FIN
) AS T
ORDER  BY COALESCE(DEBUT_GARDIENNAGE,  DEBUT_NOUVEAU), 
          COALESCE(FIN_GARDIENNAGE, FIN_NOUVEAU)

Sélectionnez

DEBUT_GARDIENNAGE FIN_GARDIENNAGE  DEBUT_NOUVEAU    FIN_NOUVEAU
----------------- ---------------- ---------------- ----------------
NULL              NULL             2008-09-01 00:00 2008-09-01 00:00
2008-09-01 07:30  2008-09-01 14:30 NULL             NULL
NULL              NULL             2008-09-01 07:30 2008-09-02 17:30
2008-09-01 12:30  2008-09-02 00:00 NULL             NULL
2008-09-01 22:30  2008-09-02 08:00 NULL             NULL
2008-09-01 23:30  2008-09-02 11:00 NULL             NULL
2008-09-02 06:00  2008-09-02 17:30 NULL             NULL
2008-09-02 08:30  2008-09-02 16:00 NULL             NULL
2008-09-03 06:00  2008-09-03 17:30 2008-09-03 06:00 2008-09-03 17:30
2008-09-03 08:30  2008-09-03 16:00 NULL             NULL
2008-09-03 18:00  2008-09-04 04:30 NULL             NULL
NULL              NULL             2008-09-03 18:00 2008-09-05 21:30
2008-09-03 22:30  2008-09-04 10:00 NULL             NULL
2008-09-04 06:00  2008-09-04 17:30 NULL             NULL
2008-09-04 14:30  2008-09-05 08:30 NULL             NULL
2008-09-05 08:30  2008-09-05 21:30 NULL             NULL
2008-09-05 09:30  2008-09-05 12:00 NULL             NULL
2008-09-06 08:30  2008-09-06 13:30 NULL             NULL
NULL              NULL             2008-09-06 08:30 2008-09-07 12:30
2008-09-06 10:30  2008-09-06 21:00 NULL             NULL
2008-09-06 12:00  2008-09-06 22:30 NULL             NULL
2008-09-06 16:30  2008-09-07 10:30 NULL             NULL
2008-09-06 21:00  2008-09-07 10:30 NULL             NULL
2008-09-07 06:00  2008-09-07 12:30 NULL             NULL
NULL              NULL             2008-09-07 18:30 2008-09-07 23:59
2008-09-07 18:30  2008-09-08 06:30 NULL             NULL

Notez que les étapes 3 et 4 sont réalisées dans la même CTE.

38-F. Étape 5 (finale): recherche des intervalles inverses▲

Il faut maintenant inverser le problème en trouvant les périodes où il n'y a personne !

Sélectionnez

CREATE VIEW GDT4
AS
SELECT T1.GDN_DATEHEURE_FIN AS GDN_DATEHEURE_DEBUT,
       MIN(T2.GDN_DATEHEURE_DEBUT) AS GDN_DATEHEURE_FIN
FROM   GDT3 T1
       INNER JOIN GDT3 T2
             ON  T2.GDN_DATEHEURE_DEBUT > T1.GDN_DATEHEURE_FIN
GROUP BY T1.GDN_DATEHEURE_FIN;

Comparaison des tranches existantes avec les tranches vides enfin calculées :

Sélectionnez

SELECT *
FROM   
(
SELECT DISTINCT
       T1.GDN_DATEHEURE_DEBUT AS DEBUT_GARDIENNAGE,
       T1.GDN_DATEHEURE_FIN AS FIN_GARDIENNAGE,
       T2.GDN_DATEHEURE_DEBUT AS DEBUT_NOUVEAU,
       T2.GDN_DATEHEURE_FIN AS FIN_NOUVEAU
FROM   T_GARDIENNAGE_GDN T1
       FULL OUTER JOIN GDT4 T2
            ON T1.GDN_DATEHEURE_DEBUT = T2.GDN_DATEHEURE_DEBUT
               AND T1.GDN_DATEHEURE_FIN = T2.GDN_DATEHEURE_FIN
) AS T
ORDER  BY COALESCE(DEBUT_GARDIENNAGE,  DEBUT_NOUVEAU), 
          COALESCE(FIN_GARDIENNAGE, FIN_NOUVEAU);

Sélectionnez

DEBUT_GARDIENNAGE FIN_GARDIENNAGE  DEBUT_NOUVEAU    FIN_NOUVEAU
----------------- ---------------- ---------------- ----------------
NULL              NULL             2008-09-01 00:00 2008-09-01 07:30
2008-09-01 07:30  2008-09-01 14:30 NULL             NULL
2008-09-01 12:30  2008-09-02 00:00 NULL             NULL
2008-09-01 22:30  2008-09-02 08:00 NULL             NULL
2008-09-01 23:30  2008-09-02 11:00 NULL             NULL
2008-09-02 06:00  2008-09-02 17:30 NULL             NULL
2008-09-02 08:30  2008-09-02 16:00 NULL             NULL
NULL              NULL             2008-09-02 17:30 2008-09-03 06:00
2008-09-03 06:00  2008-09-03 17:30 NULL             NULL
2008-09-03 08:30  2008-09-03 16:00 NULL             NULL
NULL              NULL             2008-09-03 17:30 2008-09-03 18:00
2008-09-03 18:00  2008-09-04 04:30 NULL             NULL
2008-09-03 22:30  2008-09-04 10:00 NULL             NULL
2008-09-04 06:00  2008-09-04 17:30 NULL             NULL
2008-09-04 14:30  2008-09-05 08:30 NULL             NULL
2008-09-05 08:30  2008-09-05 21:30 NULL             NULL
2008-09-05 09:30  2008-09-05 12:00 NULL             NULL
NULL              NULL             2008-09-05 21:30 2008-09-06 08:30
2008-09-06 08:30  2008-09-06 13:30 NULL             NULL
2008-09-06 10:30  2008-09-06 21:00 NULL             NULL
2008-09-06 12:00  2008-09-06 22:30 NULL             NULL
2008-09-06 16:30  2008-09-07 10:30 NULL             NULL
2008-09-06 21:00  2008-09-07 10:30 NULL             NULL
2008-09-07 06:00  2008-09-07 12:30 NULL             NULL
NULL              NULL             2008-09-07 12:30 2008-09-07 18:30
2008-09-07 18:30  2008-09-08 06:30 NULL             NULL

39. Solution au problème n° 39 - abstract et mots clefs▲

On comprend bien que pour réaliser le comptage il faut que l'on arrive à une table telle que chaque mot ait été découpé et mis dans une colonne avec la référence du titre du papier.

La difficulté de ce découpage réside en deux problèmes :

39-A. Le découpage n'est pas itératif▲

En effet, si les données étaient telles que l'on aurait autant de caractère dièse (#) que de mot, la découpe serait simplifiée par le fait que trouver le début ou la fin du mot clef, consisterait à trouver la position de chaque caractères #. Or ce n'est pas le cas. Il convient donc d'agir en conséquence et faire deux traitements distincts.

Par exemple, trouver le premier mot clef de l'abstract revient à faire la requête suivante :

Sélectionnez

SELECT *, CASE 
          WHEN POSITION('#', PPR_ABSTRACT) > 0 
             THEN SUBSTRING(PPR_ABSTRACT FROM 1 FOR COALESCE(POSITION('#', PPR_ABSTRACT) - 1, 0)) 
             ELSE PPR_ABSTRACT
          END AS MOT_CLEF
FROM   T_PAPIER_PPR T ;

Sélectionnez

PPR_TITRE                            PPR_ABSTRACT                         MOT_CLEF1
------------------------------------ ------------------------------------ -------------
Les derniers roi de France           Histoire#Politique                   Histoire
De Gaulle, un héro de l'histoire     Histoire#Politique#Guerre            Histoire
Les deux guerres mondiales           Histoire#Guerre                      Histoire
Les années 50 en Europe              Histoire#Union européenne            Histoire
Les présidents de la république      Histoire#Politique                   Histoire
Histoire de France                   Histoire                             Histoire

39-B. Le découpage n'est pas "fini"▲

En effet, le nombre de mots clef dans l'abstract n'est pas limité. Ici dans notre exemple nous avons fait en sorte de ne pas avoir une colonne PPR_ABSTRACT dont la longueur dépasse 120 caractères, mais dans la réalité, les choses ne sont pas bornées. Il faut donc prévoir un découpage de l'abstract sur un nombre indéterminé de mots clefs.

La solution à ce problème consiste à passer par une table de nombre et de faire un produit cartésien pour assurer le découpage des mots clefs...

Créons comme à notre habitude une table des nombres pour ce faire :

Sélectionnez

CREATE TABLE T_NUM (NUM INTEGER);

Sélectionnez

INSERT INTO T_NUM VALUES (0);
INSERT INTO T_NUM VALUES (1);
INSERT INTO T_NUM VALUES (2);
INSERT INTO T_NUM VALUES (3);
INSERT INTO T_NUM VALUES (4);
INSERT INTO T_NUM VALUES (5);
INSERT INTO T_NUM VALUES (6);
INSERT INTO T_NUM VALUES (7);
INSERT INTO T_NUM VALUES (8);
INSERT INTO T_NUM VALUES (9);

Sélectionnez

INSERT INTO T_NUM
SELECT T1.NUM + 10 * T2.NUM + 100 * T3.NUM
FROM   T_NUM AS T1
       CROSS JOIN T_NUM AS T2
       CROSS JOIN T_NUM AS T3
WHERE  T1.NUM + 10 * T2.NUM + 100 * T3.NUM > 9 ;

Une voie pour parvenir à ce résultat est la requête suivante :

Sélectionnez

SELECT PPR_TITRE, PPR_ABSTRACT,
       SUBSTRING(PPR_ABSTRACT, N.NUM + 1, LEN(PPR_ABSTRACT) - N.NUM + 1) AS DECOUPE
FROM   T_PAPIER_PPR T
       CROSS JOIN T_NUM N
WHERE  SUBSTRING(PPR_ABSTRACT, N.NUM, 1) = '#' ;

Sélectionnez

PPR_TITRE                            PPR_ABSTRACT                 DECOUPE
------------------------------------ ---------------------------- --------------------------------------------
Les derniers roi de France           Histoire#Politique           Politique
De Gaulle, un héro de l'histoire     Histoire#Politique#Guerre    Politique#Guerre
De Gaulle, un héro de l'histoire     Histoire#Politique#Guerre    Guerre
Les deux guerres mondiales           Histoire#Guerre              Guerre
Les années 50 en Europe              Histoire#Union européenne    Union européenne
Les présidents de la république      Histoire#Politique           Politique

Il ne suffit plus que de nettoyer cette table en ôtant tout ce qui est situé après le caractères # dans la colonne DECOUPE de faire une union avec la précédente requête et enfin de compter les occurrences.

Voici donc la requête finale :

Sélectionnez

SELECT CAST(MOT_CLEF AS VARCHAR(16)) AS MOT_CLEF, COUNT(*) AS NBR_REPONSES
FROM   (SELECT PPR_TITRE, PPR_ABSTRACT, SUBSTRING(MOT_CLEF, 1, 
               COALESCE(NULLIF(CHARINDEX('#', MOT_CLEF), 0) - 1, LEN(MOT_CLEF))) AS MOT_CLEF
        FROM   (SELECT *, CASE 
                             WHEN CHARINDEX('#', PPR_ABSTRACT) > 0 
                                THEN SUBSTRING(PPR_ABSTRACT, 1, COALESCE(CHARINDEX('#', PPR_ABSTRACT) - 1, 0)) 
                                ELSE PPR_ABSTRACT
                          END AS MOT_CLEF
                FROM   T_PAPIER_PPR T
                UNION
                SELECT PPR_TITRE, PPR_ABSTRACT, 
                       SUBSTRING(PPR_ABSTRACT, N.NUM + 1, LEN(PPR_ABSTRACT) - N.NUM + 1) AS DECOUPE
                FROM   T_PAPIER_PPR T
                       CROSS JOIN T_NUM N
                WHERE  SUBSTRING(PPR_ABSTRACT, N.NUM, 1) = '#') T) TT
GROUP BY MOT_CLEF ;

Une autre façon de faire est de rajouter un # en tête de l'abstract dès le départ :

Ajout du caractère # en tête :

Sélectionnez

SELECT PPR_TITRE, '#' + PPR_ABSTRACT AS PPR_ABSTRACT
FROM   T_PAPIER_PPR

Sélectionnez

PPR_TITRE                            PPR_ABSTRACT
------------------------------------ -------------------------------
Les derniers roi de France           #Histoire#Politique
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre
Les deux guerres mondiales           #Histoire#Guerre
Les années 50 en Europe              #Histoire#Union européenne
Les présidents de la république      #Histoire#Politique
Histoire de France                   #Histoire

Découpe sur n caractères # :

Sélectionnez

SELECT PPR_TITRE, PPR_ABSTRACT, 
       SUBSTRING(PPR_ABSTRACT, N.NUM + 1, LEN(PPR_ABSTRACT) - N.NUM + 1) AS DECOUPE
FROM   (SELECT PPR_TITRE, '#' + PPR_ABSTRACT AS PPR_ABSTRACT
        FROM   T_PAPIER_PPR) T
       CROSS JOIN T_NUM N
WHERE  SUBSTRING(PPR_ABSTRACT, N.NUM, 1) = '#' ;

Sélectionnez

PPR_TITRE                            PPR_ABSTRACT                     DECOUPE
------------------------------------ ------------------------------- -----------------------------
Les derniers roi de France           #Histoire#Politique             Histoire#Politique
Les derniers roi de France           #Histoire#Politique             Politique
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre      Histoire#Politique#Guerre
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre      Politique#Guerre
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre      Guerre
Les deux guerres mondiales           #Histoire#Guerre                Histoire#Guerre
Les deux guerres mondiales           #Histoire#Guerre                Guerre
Les années 50 en Europe              #Histoire#Union européenne      Histoire#Union européenne
Les années 50 en Europe              #Histoire#Union européenne      Union européenne
Les présidents de la république      #Histoire#Politique             Histoire#Politique
Les présidents de la république      #Histoire#Politique             Politique
Histoire de France                   #Histoire                       Histoire

Suppression de tout ce qui est après et avec le caractère # :

Sélectionnez

SELECT PPR_TITRE, PPR_ABSTRACT, 
       CASE 
          WHEN CHARINDEX('#', DECOUPE) > 0 
             THEN SUBSTRING(DECOUPE, 1, CHARINDEX('#', DECOUPE) - 1) 
             ELSE DECOUPE
          END AS MOT_CLEF
FROM   (SELECT PPR_TITRE, PPR_ABSTRACT, 
               SUBSTRING(PPR_ABSTRACT, N.NUM + 1, LEN(PPR_ABSTRACT) - N.NUM + 1) AS DECOUPE
        FROM   (SELECT PPR_TITRE, '#' + PPR_ABSTRACT AS PPR_ABSTRACT
                FROM   T_PAPIER_PPR) T
               CROSS JOIN T_NUM N
        WHERE  SUBSTRING(PPR_ABSTRACT, N.NUM, 1) = '#') TT

Sélectionnez

PPR_TITRE                            PPR_ABSTRACT                   MOT_CLEF
------------------------------------ ------------------------------ -------------------- 
Les derniers roi de France           #Histoire#Politique            Histoire
Les derniers roi de France           #Histoire#Politique            Politique
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre     Histoire
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre     Politique
De Gaulle, un héro de l'histoire     #Histoire#Politique#Guerre     Guerre
Les deux guerres mondiales           #Histoire#Guerre               Histoire
Les deux guerres mondiales           #Histoire#Guerre               Guerre
Les années 50 en Europe              #Histoire#Union européenne     Histoire
Les années 50 en Europe              #Histoire#Union européenne     Union européenne
Les présidents de la république      #Histoire#Politique            Histoire
Les présidents de la république      #Histoire#Politique            Politique
Histoire de France                   #Histoire                      Histoire

Comptage final :

Sélectionnez

SELECT MOT_CLEF, COUNT(*)
FROM   (SELECT PPR_TITRE, PPR_ABSTRACT, 
               CASE 
                 WHEN CHARINDEX('#', DECOUPE) > 0 
                    THEN SUBSTRING(DECOUPE, 1, CHARINDEX('#', DECOUPE) - 1) 
                    ELSE DECOUPE
                 END AS MOT_CLEF
       FROM   (SELECT PPR_TITRE, PPR_ABSTRACT, 
                      SUBSTRING(PPR_ABSTRACT, N.NUM + 1, LEN(PPR_ABSTRACT) - N.NUM + 1) AS DECOUPE
               FROM   (SELECT PPR_TITRE, '#' + PPR_ABSTRACT AS PPR_ABSTRACT
                       FROM   T_PAPIER_PPR) T
                      CROSS JOIN T_NUM N
               WHERE  SUBSTRING(PPR_ABSTRACT, N.NUM, 1) = '#') TT ) TTT
GROUP  BY MOT_CLEF

Sélectionnez

MOT_CLEF                   NOMBRE
-------------------------- ----------- 
Guerre                     2
Histoire                   6
Politique                  3
Union européenne           1

Copyright © SQLPro. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.