Vérification des dates de 2 tableaux - SQL

Version imprimable

Bonjour,

Je dois résoudre ce probleme:

"Vérifier que les étapes de chaque projet ont été faites entre les dates de début et de fin du projet. (Écrivez une requête qui permette à un humain de savoir si c’est le cas. On ne vous demande pas d’ajouter cette contrainte à la base de données.)"

Voici ma requête:
Code:

1 2 3 4 SELECT e.idEtape, e.idProjet, e.dateDebut, e.dateFin FROM (SELECT idProjet, dateDebut, dateFin FROM Projet) AS p RIGHT JOIN EtapexProjet AS e ON e.dateDebut > p.dateDebut AND e.dateFin > p.dateFin ORDER BY p.idProjet;
Voici les 2 tableau nécessaires pour résoudre le problème:

mysql> SELECT * FROM EtapexProjet;
+---------+----------+------------+------------+
| idEtape | idProjet | dateDebut | dateFin |
+---------+----------+------------+------------+
| 1 | 1 | 2011-07-01 | 2011-09-01 |
| 1 | 2 | 2012-05-01 | 2012-05-10 |
| 1 | 3 | 2011-11-01 | 2012-01-20 |
| 2 | 1 | 2011-09-02 | 2011-11-30 |
| 2 | 2 | 2012-05-11 | 2012-06-01 |
| 2 | 3 | 2012-01-21 | 2012-04-01 |
| 3 | 1 | 2011-12-01 | 2012-07-07 |
| 3 | 2 | 2012-06-02 | 2012-07-01 |
| 3 | 3 | 2012-04-02 | NULL |
| 4 | 1 | 2012-07-08 | NULL |
| 4 | 2 | 2012-07-01 | 2012-07-21 |
| 5 | 2 | 2012-07-22 | 2012-07-23 |
+---------+----------+------------+------------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM Projet;
+----------+----------+------------------------------------------------------+------------+------------+---------------+
| idProjet | idClient | nomProjet | dateDebut | dateFin | idResponsable |
+----------+----------+------------------------------------------------------+------------+------------+---------------+
| 1 | 321 | Développement du site web | 2011-08-01 | NULL | 1876 |
| 2 | 321 | Maintenance du systeme de ressources humaines | 2012-05-01 | 2012-07-23 | 2231 |
| 3 | 345 | Développement du systeme de gestion de fournisseurs| 2011-11-01 | NULL | 2231 |
+----------+----------+------------------------------------------------------+------------+------------+---------------+

Citation:

Vérification des dates de 2 tableaux - SQL

En matière de bases de données relationnelles, on ne parle pas de tableaux mais de tables ! :weird:
Citation:
Vérifier que les étapes de chaque projet ont été faites entre les dates de début et de fin du projet.
Code:

1 2 3 4 SELECT e.idEtape, e.idProjet, e.dateDebut, e.dateFin FROM (SELECT idProjet, dateDebut, dateFin FROM Projet) AS p RIGHT JOIN EtapexProjet AS e ON e.dateDebut > p.dateDebut AND e.dateFin > p.dateFin ORDER BY p.idProjet;
Votre condition de jointure n'est pas bonne. Là vous aurez toutes les étapes de tous les projets associées à un projet si elles répondent aux conditions.

Il faut écrire la condition de jointure entre idProjet des deux tables et mettre la bonne condition de restriction dans un WHERE.
Et vous n'avez pas besoin d'un RIGHT JOIN, un INNER JOIN est suffisant.

Comme ça semble être un exercice, je vous laisse y réfléchir. ;)

J'ai essayé ce code:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT e.idetape, -> e.idprojet, -> e.datedebut, -> e.datefin, -> p.datedebut AS projet_datedebut, -> p.datefin AS projet_datefin -> FROM etapexprojet AS e -> JOIN projet AS p -> ON p.idprojet = e.idprojet -> AND ( e.datedebut BETWEEN p.datedebut AND p.datefin -> OR e.datefin BETWEEN p.datedebut AND p.datefin ) -> ORDER BY e.idprojet, e.idetape;
Cependant, j'ai les bons résultats juste pour le projet #2. Je me dit c'est parce que les date Fin est NULL pour les projet 1 et 3. Alors, mon code n'est pas capable de reconnaitre que NULL est vide....

+---------+----------+------------+------------+------------------+----------------+
| idetape | idprojet | datedebut | datefin | projet_datedebut | projet_datefin |
+---------+----------+------------+------------+------------------+----------------+
| 1 | 2 | 2012-05-01 | 2012-05-10 | 2012-05-01 | 2012-07-23 |
| 2 | 2 | 2012-05-11 | 2012-06-01 | 2012-05-01 | 2012-07-23 |
| 3 | 2 | 2012-06-02 | 2012-07-01 | 2012-05-01 | 2012-07-23 |
| 4 | 2 | 2012-07-01 | 2012-07-21 | 2012-05-01 | 2012-07-23 |
| 5 | 2 | 2012-07-22 | 2012-07-23 | 2012-05-01 | 2012-07-23 |
+---------+----------+------------+------------+------------------+----------------+

10/11/2018, 18h47
CinePhil
En principe, on met plutôt les conditions de restriction dans un WHERE !
Code:

1 2 3 4 SELECT des, colonnes FROM une_table t1 JOIN une_autre_table t2 ON [condition de jointure entre les deux tables] WHERE [condition(s) de restriction sur les données à afficher]
Avec vos conditions sur les dates, si une étape commence entre les dates de début et de fin du projet mais se termine après sa fin, ou bien si l'étape commence avant le début du projet mais se termine pendant le projet, cette étape sera considérée comme bonne.

Je tenterais plutôt d'afficher les étapes fautives que les étapes bonnes, donc celles qui ont une date de début inférieure à celle du début de leur projet ou qui ont une date de fin supérieure à celle de leur projet.

Salut iNeWx.

Citation:

Envoyé par iNeWx

Vérifier que les étapes de chaque projet ont été faites entre les dates de début et de fin du projet

En gros, ce que l'on vous demande, c'est de vérifier la conformité de toutes vos dates.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `projet`
--------------
 
--------------
CREATE TABLE `projet`
( `idProjet`       integer unsigned  NOT NULL auto_increment primary key,
  `idClient`       integer unsigned  NOT NULL,
  `nomProjet`      varchar(255)      NOT NULL,
  `datedeb`        date              NOT NULL,
  `datefin`        date                  NULL,
  `idResponsable`  integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `projet` (`idProjet`,`idClient`,`nomProjet`,`datedeb`,`datefin`,`idResponsable`) VALUES
(1, 321, 'Développement du site web'                          , '2011-08-01', NULL,         1876),
(2, 321, 'Maintenance du systeme de ressources humaines'      , '2012-05-01', '2012-07-23', 2231),
(3, 345, 'Développement du systeme de gestion de fournisseurs', '2011-11-01', NULL,         2231)
--------------
 
--------------
select    *
    from  `projet`
--------------
 
+----------+----------+-----------------------------------------------------+------------+------------+---------------+
| idProjet | idClient | nomProjet                                           | datedeb    | datefin    | idResponsable |
+----------+----------+-----------------------------------------------------+------------+------------+---------------+
|        1 |      321 | Développement du site web                           | 2011-08-01 | NULL       |          1876 |
|        2 |      321 | Maintenance du systeme de ressources humaines       | 2012-05-01 | 2012-07-23 |          2231 |
|        3 |      345 | Développement du systeme de gestion de fournisseurs | 2011-11-01 | NULL       |          2231 |
+----------+----------+-----------------------------------------------------+------------+------------+---------------+
--------------
DROP TABLE IF EXISTS `etape`
--------------
 
--------------
CREATE TABLE `etape`
( `idProjet`   integer unsigned  NOT NULL,
  `idEtape`    integer unsigned  NOT NULL,
  `datedeb`    date              NOT NULL,
  `datefin`    date                  NULL,
  CONSTRAINT `FK_PROJET` FOREIGN KEY (`idProjet`) REFERENCES `projet` (`idProjet`) ON DELETE CASCADE ON UPDATE CASCADE,
  primary key (`idEtape`,`idProjet`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `etape` (`idProjet`,`idEtape`,`datedeb`,`datefin`) VALUES
  (1, 1, '2011-07-01', '2011-09-01'),
  (1, 2, '2011-09-02', '2011-11-30'),
  (1, 3, '2011-12-01', '2012-07-07'),
  (1, 4, '2012-07-08', NULL),
  (2, 1, '2012-05-01', '2012-05-10'),
  (2, 2, '2012-05-11', '2012-06-01'),
  (2, 3, '2012-06-02', '2012-07-01'),
  (2, 4, '2012-07-01', '2012-07-21'),
  (2, 5, '2012-07-22', '2012-07-23'),
  (3, 1, '2011-11-01', '2012-01-20'),
  (3, 2, '2012-01-21', '2012-04-01'),
  (3, 3, '2012-04-02', NULL)
--------------
 
--------------
select    *
    from  `etape`
order by  idProjet, idEtape
--------------
 
+----------+---------+------------+------------+
| idProjet | idEtape | datedeb    | datefin    |
+----------+---------+------------+------------+
|        1 |       1 | 2011-07-01 | 2011-09-01 |
|        1 |       2 | 2011-09-02 | 2011-11-30 |
|        1 |       3 | 2011-12-01 | 2012-07-07 |
|        1 |       4 | 2012-07-08 | NULL       |
|        2 |       1 | 2012-05-01 | 2012-05-10 |
|        2 |       2 | 2012-05-11 | 2012-06-01 |
|        2 |       3 | 2012-06-02 | 2012-07-01 |
|        2 |       4 | 2012-07-01 | 2012-07-21 |
|        2 |       5 | 2012-07-22 | 2012-07-23 |
|        3 |       1 | 2011-11-01 | 2012-01-20 |
|        3 |       2 | 2012-01-21 | 2012-04-01 |
|        3 |       3 | 2012-04-02 | NULL       |
+----------+---------+------------+------------+
--------------
select      t3.nomProjet,
            t3.datedeb as 'date début projet',
            t3.datefin as 'date fin projet',
            t1.idetape,
            t1.datedeb as 'début précédente',
            t1.datefin as 'fin précédente',
            t2.idetape,
            t2.datedeb as 'début suivante',
            t2.datefin as 'fin suivante',
 
            case when t1.datedeb > t1.datefin then "début étape précédente plus grande que fin étape précédente"
                 when t2.datedeb > t2.datefin then "début étape suivante plus grande que fin étape suivante"
                 when t1.datefin > t2.datedeb then "fin étape précédente plus grande que début étape suivante"
                 when t1.datedeb < t3.datedeb then "début étape précédente avant démarrage projet"
                 when t2.datedeb < t3.datedeb then "début étape suivante avant démarrage projet"
                 when t1.datefin > t3.datefin then "fin étape précédente après fin projet"
                 when t2.datefin > t3.datefin then "fin étape suivante après fin projet"
                 when t3.datedeb > t3.datefin then "début projet plus grande que fin projet"
                 else null end as test
 
      from  `etape` as t1
 
inner join  `etape` as t2
        on  t2.idProjet = t1.idProjet
       and  t2.idEtape    = (  select  min(idEtape)
                                 from  `etape` as tx
                                where  tx.idProjet = t1.idProjet
                                  and  tx.idEtape  > t1.idEtape
                            )
 
inner join  `projet` as t3
        on  t3.idProjet = t1.idProjet
 
  order by  t1.idProjet, t1.idEtape
--------------
 
+-----------------------------------------------------+-------------------+-----------------+---------+------------------+----------------+---------+----------------+--------------+-----------------------------------------------+
| nomProjet                                           | date début projet | date fin projet | idetape | début précédente | fin précédente | idetape | début suivante | fin suivante | test                                          |
+-----------------------------------------------------+-------------------+-----------------+---------+------------------+----------------+---------+----------------+--------------+-----------------------------------------------+
| Développement du site web                           | 2011-08-01        | NULL            |       1 | 2011-07-01       | 2011-09-01     |       2 | 2011-09-02     | 2011-11-30   | début étape précédente avant démarrage projet |
| Développement du site web                           | 2011-08-01        | NULL            |       2 | 2011-09-02       | 2011-11-30     |       3 | 2011-12-01     | 2012-07-07   | NULL                                          |
| Développement du site web                           | 2011-08-01        | NULL            |       3 | 2011-12-01       | 2012-07-07     |       4 | 2012-07-08     | NULL         | NULL                                          |
| Maintenance du systeme de ressources humaines       | 2012-05-01        | 2012-07-23      |       1 | 2012-05-01       | 2012-05-10     |       2 | 2012-05-11     | 2012-06-01   | NULL                                          |
| Maintenance du systeme de ressources humaines       | 2012-05-01        | 2012-07-23      |       2 | 2012-05-11       | 2012-06-01     |       3 | 2012-06-02     | 2012-07-01   | NULL                                          |
| Maintenance du systeme de ressources humaines       | 2012-05-01        | 2012-07-23      |       3 | 2012-06-02       | 2012-07-01     |       4 | 2012-07-01     | 2012-07-21   | NULL                                          |
| Maintenance du systeme de ressources humaines       | 2012-05-01        | 2012-07-23      |       4 | 2012-07-01       | 2012-07-21     |       5 | 2012-07-22     | 2012-07-23   | NULL                                          |
| Développement du systeme de gestion de fournisseurs | 2011-11-01        | NULL            |       1 | 2011-11-01       | 2012-01-20     |       2 | 2012-01-21     | 2012-04-01   | NULL                                          |
| Développement du systeme de gestion de fournisseurs | 2011-11-01        | NULL            |       2 | 2012-01-21       | 2012-04-01     |       3 | 2012-04-02     | NULL         | NULL                                          |
+-----------------------------------------------------+-------------------+-----------------+---------+------------------+----------------+---------+----------------+--------------+-----------------------------------------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Pour chaque cas, il y a un message d'anomalie.
Maintenant, si vous ne désirez que les messages d'anomalies, il suffit à la requête d'ajouter "having test is not null".

@+

12/11/2018, 14h20
iNeWx

Merci a vous 2 pour vos conseils et solutions.

Résolu!
13/11/2018, 12h09
Artemus24

Salut iNeWx.

Il serait intéressant de savoir comment avez-vous résolu votre problème.

A la question posée :

Citation:

Envoyé par iNeWx

Vérifier que les étapes de chaque projet ont été faites entre les dates de début et de fin du projet.

j'ai compris qu'il s'agissait de vérifier la conformité des dates.
A savoir que la date début doit être inférieurs ou égale à la date fin, que ce soit de l'étape ou du projet.
Mais encore que la date de fin de l'étape précédente doit aussi être inférieure à la date de début de l'étape suivante.
Et le tout, vérifier que la date de début de la première étape soit supérieure ou égale à la date de début du projet.
Et idem en ce qui concerne la date de fin de la dernière étape, vis-à-vis de la date de fin du projet.

En tout cas, je l'ai compris ainsi.

Maintenant, il est fort possible que l'on ne recherche pas un contrôle de toutes les dates, mais juste l'encadrement des étapes dans les dates du projet.

@+