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
IV-A-1. Étape n°1 : On recherche les débuts de plage ayant des dates contiguës
IV-A-2. Étape n° 2 : la seconde requête n'est guère plus complexe, pour trouver les fins de périodes
IV-A-3. Étape n°3 : Voici maintenant le temps venu de joindre ces éléments... Cela semble plus facile à dire qu'à faire !
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 !
IV-B. Solution au problème n° 31 - plus proche valeur
IV-C. Solution au problème n° 32 - primes de salaires
IV-D. Solution au problème n° 33 - affectations comptables
IV-E. Solution au problème n° 33 - précédents (ou suivants)
IV-F. Solution au problème n° 35 - matchs et victoires
IV-G. Solution au problème n° 36 - tri bâtard
IV-H. Solution au problème n° 37 - vote contraint
IV-I. Solution au problème n° 38 - propriété bien gardée
IV-I-1. Étape préliminaire
IV-I-2. Étape 1 : bornage des tranches sur la période à scruter
IV-I-3. Étape 2 : calcul de toutes les périodes possibles partant des périodes existantes
38-D. Étape 3 : on supprime maintenant les périodes incluses dans les autres afin de ne laisser que des périodes non "redondantes"
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
38-F. Étape 5 (finale): recherche des intervalles inverses
39. Solution au problème n° 39 - abstract et mots clefs
39-A. Le découpage n'est pas itératif
39-B. Le découpage n'est pas "fini"
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 :
CREATE TABLE T_CALENDRIER_CLD
(CLD_DATE DATE NOT NULL PRIMARY KEY);
|
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 :
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
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 :
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 :
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 :
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
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 ;
|
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 :
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
|
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 :
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 :
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' ;
|
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 !
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' ;
|
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 :
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 :
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 :
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 :
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.
CREATE TABLE T_NUM
(NUM INT);
|
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 :
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
|
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 :
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
|
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 :
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 :
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 ;
|
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 !
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 ;
|
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 :
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 _ ...
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 ;
|
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 :
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 :
ALTER TABLE T_AFFECTATION_AFC
ADD AFC_PRIORITE SMALLINT;
|
Il faut ensuite mettre à jour ligne à ligne la table des affectations, comme ceci :
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 :
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 :
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 :
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 !
CREATE TABLE T_AFFECTATION2_AFC
(AFC_COMPTE CHAR(6),
AFC_ANA CHAR(5),
AFC_AFFECT CHAR(4),
AFC_PRIORITE SMALLINT);
|
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 !
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 :
CREATE TABLE T_NUM (NUM INT);
|
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 :
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
|
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 :
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
|
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 :
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
|
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 :
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
|
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 :
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 :
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 :
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 ;
|
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 :
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 ;
|
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 :
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 ;
|
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 :
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 ;
|
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 :
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
|
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 :
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.
SELECT *
FROM T_PARCELLE_PCL
ORDER BY CHARACTER_LENGTH(PCL_NUM), PCL_NUM ;
|
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.
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 :
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é : |
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 :
WITH
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
),
GDT
AS
(
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
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
)
)
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 )
)
)
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
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
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)
|
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
CREATE VIEW GDT1
AS
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 |
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) ;
|
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"
CREATE VIEW GDT2
AS
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
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 |
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)
|
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
CREATE VIEW GDT3
AS
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
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 )
)
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 |
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)
|
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 !
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 :
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);
|
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 :
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 ;
|
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
:
CREATE TABLE T_NUM (NUM INTEGER);
|
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);
|
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 :
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) = '#' ;
|
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 :
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 : |
SELECT PPR_TITRE, '#' + PPR_ABSTRACT AS PPR_ABSTRACT
FROM T_PAPIER_PPR
|
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 # : |
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) = '#' ;
|
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 # : |
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
|
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 : |
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
|
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.
