Comment vérifier si deux dates sont dans une mémé période ?

**madinadiouf** · 22/01/2020, 14h43

Bonjour,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
select T1.mission  
     , T1.dtdeb
     , T1.dtfin
     , 'est en chevauchement avec'
     , T2.mission
     , T2.dtdeb
     , T2.dtfin
from       tab1 T1
inner join tab1 T2
  on  T2.mission > T1.mission
  and T1.dtdeb <= T2.dtfin 
  and T1.dtfin >= T2.dtdeb 
order by T1.mission, T2.mission ;

Les deux tables concernées sont missions et missions_agents et la clé qui les lie est mission_id et une mission en cours est caractérisée par : missions.arrivee_mission >= CURDATE().
ici T1,T2 et mission représentent quoi ?
donc

**escartefigue** · 22/01/2020, 14h56

dans mon exemple T1 et T2 sont deux alias d'une même table, celle qui s'appelle Tab1 dans mon jeu d'essai.
C'est ce qu'on appelle une auto-jointure (jointure d'une table avec elle même).

Le principe est simple : pour chaque mission de la table mission, je recherche une autre mission (dont l'identifiant est supérieur pour éviter de détecter un faux chevauchement d'une mission avec elle même) et dont les dates sont en chevauchement.

Pour rappel, votre question initiale était :

Envoyé par madinadiouf

Bonjour,

J'ai une table missions qui a deux champs date_début_mission et date_fin_mission, je veux savoir comment extraire les missions qui se déroulent dans la même période en requête Mysql/php ?

Merci d'avance

Il n'y avait donc qu'une seule table, si c'est bien le cas, il suffit de remplacer le nom tab1 que j'ai utilisé dans mon exemple par le nom de votre table (mission ou autre) et ça roule
Si vous avez deux tables, merci de communiquer la description complète de ces deux tables (ordres CREATE TABLE) en précisant la liste des colonnes dans chaque table que vous souhaitez restituer ou sur lesquelles vous avez besoin d'appliquer un filtre

**madinadiouf** · 22/01/2020, 16h01

Bonjour,

Je comprends bien votre logique. En fait il y'a la table missions et la la table missions_agents qui relie la mission à l'agent.
Car ici je recherche tous les agents et pour chaque agent en fonction de son ID, je vais voir ses missions si elles sont en chevauchement.
voici les script sql des tables

Table agents

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE IF NOT EXISTS Agent (
    agent_id INT AUTO_INCREMENT PRIMARY KEY,
    prenom VARCHAR(255) NOT NULL,
    nom VARCHAR(255) NOT NULL,
    situation_famille VARCHAR(20) NOT NULL,
    grade VARCHAR(255) NOT NULL,
    fonction VARCHAR(255) NOT NULL,
    direction_id INT,
    CONSTRAINT fk_direction
    FOREIGN KEY (direction_id) 
        REFERENCES direction(direction_id)
)  ENGINE=INNODB;

Table missions

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS missions (
    mission_id INT AUTO_INCREMENT PRIMARY KEY,
     motif TEXT,
     moyen_transport TEXT,
    objet TEXT,
    observations TEXT,
    depart_mission date,
    arrivee_mission date,
    lieu VARCHAR(255)
   )  ENGINE=INNODB;

Table mission_agents

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE IF NOT EXISTS mission_agents (
   agent_id INT NOT NULL,
  mission_id INT NOT NULL,
  PRIMARY KEY (agent_id , mission_id),
   CONSTRAINT fk_agent_mission
    FOREIGN KEY (agent_id) 
        REFERENCES Agent(agent_id)
        ON UPDATE RESTRICT ON DELETE CASCADE,
         CONSTRAINT fk_agent_mission1
    FOREIGN KEY (mission_id) 
        REFERENCES missions(mission_id)
        ON UPDATE RESTRICT ON DELETE CASCADE
);

**escartefigue** · 22/01/2020, 16h24

vu que votre modèle comporte une table de liaison entre agent et mission, ça signifie qu'un agent peut avoir plusieurs missions (ça semble raisonable

) mais aussi qu'une même mission concerne plusieurs agents, est-ce bien le cas ?

**madinadiouf** · 22/01/2020, 16h46

Bonjour,

Oui c'est bien le cas, Dans une mission, on peut avoir un ou plusieurs agents.Et un agent peut avoir plusieurs missions.
Ce qu'on refuse et qu'on veut éviter c'est qu'un agent participe à des missions qui se chevauchent.
Exemple :il y'a rejet pour tout agent avec deux missions durant la même période
D’où j'ai besoin de cette requête qui détecte ces cas de figure

**escartefigue** · 22/01/2020, 16h52

S'il est normal qu'une mission concerne plusieurs agents, voici une solution applicable

Mon jeu d'essai (je suis passé par MySQL v8 et des CTE pour ne pas avoir à faire de CREATE TABLE, mais ça ne change rien au principe)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
with ag_agent (ag_ident, ag_nom) as
    (select 001,  'dupont   '  union all
     select 002,  'martin   '  union all
     select 003,  'zorglub  '  union all
     select 004,  'azerty'
    )
   , mi_mission (mi_ident, mi_code, mi_dtdeb, mi_dtfin) as
    (select 101, 'M101', '2019-12-01', '2019-12-10'  union all
     select 102, 'M102', '2019-12-05', '2019-12-30'  union all
     select 103, 'M103', '2019-12-20', '2019-12-31'  union all
     select 104, 'M104', '2020-01-02', '2020-01-16'  union all
     select 105, 'M105', '2020-12-16', '2020-01-15'  union all
     select 106, 'M106', '2020-01-18', '2020-01-19'  
    )
   , am_ag_mi (ag_ident, mi_ident) as
    (select 001, 101   union all
     select 001, 102   union all
     select 001, 103   union all
     select 002, 103   union all
     select 002, 104   union all
     select 003, 102   union all
     select 003, 103   union all
     select 004, 106)

La requête que vous pouvez utiliser en l'état, au nom des tables près :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select AG.ag_nom      as nom
     , M1.mi_code     as M1
     , M1.mi_dtdeb    as du
     , M1.mi_dtfin    as au
     , 'en chevauchement avec' as ''
     , M2.mi_code     as M2
     , M2.mi_dtdeb    as du
     , M2.mi_dtfin    as au
from ag_agent as AG
inner join am_ag_mi   as X1
   on X1.ag_ident=AG.ag_ident
inner join mi_mission as M1
   on M1.mi_ident=X1.mi_ident
inner join am_ag_mi   as X2
   on X2.ag_ident=AG.ag_ident
  and X2.mi_ident>X1.mi_ident
inner join mi_mission as M2
   on M2.mi_ident=X2.mi_ident  
  and M2.mi_dtdeb <= M1.mi_dtfin 
  and M2.mi_dtfin >= M1.mi_dtdeb    
;

Le résultat obtenu qui met bien en évidence tous les agents qui ont des missions en chevauchement et seulement ces agents là et les missions concernées :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
nom             M1      du              au                                      M2      du              au
dupont   	M101	2019-12-01	2019-12-10	en chevauchement avec	M102	2019-12-05	2019-12-30
dupont   	M102	2019-12-05	2019-12-30	en chevauchement avec	M103	2019-12-20	2019-12-31
zorglub  	M102	2019-12-05	2019-12-30	en chevauchement avec	M103	2019-12-20	2019-12-31

**madinadiouf** · 22/01/2020, 17h46

Bonjour, si je décide d'avoir le champ motif de mission à la place mi_code et en plus le service où l'agent travaille, je peux écrire cette requête comme ça

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
select AG.ag_nom      as nom
      , serv.nom_serice as "nom service"
     , M1.motif     as M1
     , M1.mi_dtdeb    as du
     , M1.mi_dtfin    as au
     , 'en chevauchement avec' as ''
     , M2.motif     as M2
     , M2.mi_dtdeb    as du
     , M2.mi_dtfin    as au
from ag_agent as AG
inner join services   as S1
   on S1.service_id=AG.service_id
inner join am_ag_mi   as X1
   on X1.ag_ident=AG.ag_ident
inner join mi_mission as M1
   on M1.mi_ident=X1.mi_ident
inner join am_ag_mi   as X2
   on X2.ag_ident=AG.ag_ident
inner join services   as S2
   on S2.service_id=AG.service_id
  and X2.mi_ident>X1.mi_ident
inner join mi_mission as M2
   on M2.mi_ident=X2.mi_ident  
  and M2.mi_dtdeb <= M1.mi_dtfin 
  and M2.mi_dtfin >= M1.mi_dtdeb    
;

j'ai ajouté les lignes suivantes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
serv.nom_serice as "nom service"
inner join services   as S1
  on S1.service_id=AG.service_id
inner join services   as S2
on S2.service_id=AG.service_id

Est-ce que la requête est toujours correcte ?

**escartefigue** · 22/01/2020, 19h00

Oui, elle est correcte, mais il n'est pas nécessaire a priori de faire deux fois la jointure avec le service sauf si l'agent est affecté à date à un service, mais en ce cas, il manque le critère date dans la jointure entre agent et service.
Donc si l'agent n'a qu'un et un seul service indépendamment de la date, ne faites qu'une fois la jointure
N'hésitez pas à marquer les contributions qui ont pu vous aider en votant (pouce vert) sur celles-ci

Vous pouvez également marquer votre désapprobation en votant contre (pouce rouge), mais en ce cas, il est utile d'expliquer pourquoi

EDIT : en marge de cette discussion, vos tables sont très mal définies, certains attributs devraient être des FK pointant sur des typologies, d'autres sont d'un type ou d'une longueur inappropriés. Ces choix conduisent à des performances dégradées et à des risques sur l'intégrité des données de la base.

**madinadiouf** · 22/01/2020, 20h36

Bonjour,

Dans mon cas l'agent n'est pas affecté à une date à un service donc je peux faire une seule fois la jointure.
Qu'est ce que vous entendez par typologies?
Ok je ne manquerais pas de corriger certaines types de données en réduisant leur longueur

**escartefigue** · 23/01/2020, 10h51

Ce qu'on appelle des typologies ce sont les données telles que les codes devises, codes pays, codes langues etc.

Par exemple, pour les moyens de transport, une donnée de type texte est inadaptée. D'une par le type texte est très encombrant, d'autre part, sans table de typologie, vous allez décrire un même moyen de transport de plein de façon différentes en fonction de l'orthographe, des majuscules/minuscules, des séparateurs....
Par exemple : Bus, bus, Autobus, Auto-bus ... selon celui qui saisit les infos. Pas facile de sortir des analyses avec ce genre de situations

Ce qu'il faut faire, vu que vos missions n'ont qu'un seul moyen de transport, c'est créer une table des moyens de transports avec un identifiant (type integer auto_increment), un code(type char 3 ou 4 car), un libellé (type varchar) et éventuellement des dates de début et fin de validité et ajouter une foreign key dans la table des missions faisant référence à l'identifiant de la table des moyens de transport
Le même principe que ce que vous avez fait pour les directions (FK direction_id dans la table mission pointant sur la table direction)

**madinadiouf** · 24/01/2020, 11h06

Bonjour,

Je vois ce que vous voulez dire. Mais dans mon cas, selon le formulaire de données qu'on m'a donné, un moyen de transport c'est l'immatriculation d'une voiture.
Exemple : DK-722-41-45
Donc vu cela,je ne peux que le déclarer de type texte.

**escartefigue** · 24/01/2020, 16h15

Plutôt qu'un type texte, s'il s'agit d'immatriculations françaises dont la taille est limitée à une quinzaine de caractères (à vérifier mais c'est dans cet ordre d'idées) il est est nettement préférable de choisir du char(15)

En termes de performances, pour des attributs de petite taille (< 25 caractères environ) CHAR > VARCHAR > TEXT

**madinadiouf** · 27/01/2020, 13h54

Bonjour,

J'ai écrit la requête sous cette forme et je le met dans un curseur pour afficher les résultats dans un tableau mais j'ai des incohérences .
Est-ce normal

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

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
$reponse_mission_chevauch = $bdd_mission_msas->query('select AG.nom as nom
     , S1.nom_service     as nomservice
	 , M1.motif     as motif1
     , M1.depart_mission    as du1
     , M1.arrivee_mission    as au1
     , "en chevauchement avec" as ""
     , M2.motif     as motif2
     , M2.depart_mission    as du2
     , M2.arrivee_mission as au2
from agent as AG
inner join services   as S1
   on S1.service_id=AG.service_id
inner join mission_agents   as X1
   on X1.agent_id=AG.agent_id
inner join missions as M1
   on M1.mission_id=X1.mission_id
inner join mission_agents   as X2
   on X2.agent_id=AG.agent_id
inner join missions as M2
   on M2.mission_id=X2.mission_id  
  and M2.depart_mission <= M1.arrivee_mission 
  and M2.arrivee_mission >= M1.depart_mission
    ');
 
$htm_fiche_mission_chevauch='<table>
<tr>
<th>Nom agent</th>
<th>Service agent</th>
<th>Motif mission</th>
<th>Date debut mission</th>
<th>Date fin mission</th>
<th></th>
<th>Motif mission</th>
<th>Date debut mission</th>
<th>Date fin mission</th>
</tr>';
 
	  while ($donnees_mission_chevauch = $reponse_mission_chevauch->fetch())
{
	    $nom_agent_chevauch=$donnees_mission_chevauch ['nom'];
		$service_chevauch=$donnees_mission_chevauch ['nomservice'];
		$motif_mission1=$donnees_mission_chevauch ['motif1'];
		$date_debut_mission1=$donnees_mission_chevauch['du1'];
		$date_fin_mission1=$donnees_mission_chevauch['au1'];
		$en_chevauchement_avec=$donnees_mission_chevauch[''];
		$motif_mission2=$donnees_mission_chevauch ['motif2'];
		$date_debut_mission2=$donnees_mission_chevauch['au2'];
		$date_fin_mission2=$donnees_mission_chevauch['du2'];
 
 
$htm_fiche_mission_chevauch .='<tr><td align="center">'.$nom_agent_chevauch.'</td>';
$htm_fiche_mission_chevauch .='<td align="center">'.$service_chevauch.'</td>';
$htm_fiche_mission_chevauch .='<td align="center">'.$motif_mission1.'</td>';
$htm_fiche_mission_chevauch .='<td align="center">'.$date_debut_mission1.'</td>';
$htm_fiche_mission_chevauch .='<td align="center">'.$date_fin_mission1.'</td>';
$htm_fiche_mission_chevauch .='<td align="center">'.$en_chevauchement_avec.'</td>';
$htm_fiche_mission_chevauch .='<td align="center">'.$motif_mission2.'</td>';
$htm_fiche_mission_chevauch .='<td align="center">'.$date_debut_mission2.'</td>';
$htm_fiche_mission_chevauch .='<td align="center">'.$date_fin_mission2.'</td>';
$htm_fiche_mission_chevauch .='</tr>';
 
 
 
}
$htm_fiche_mission_chevauch .='</table>';
 $reponse_mission_chevauch->closeCursor();
 echo $htm_fiche_mission_chevauch;

**escartefigue** · 27/01/2020, 14h57

Par définition une incohérence ... ce n'est pas normal

Cela étant, merci de décrire précisément le résultat obtenu, les messages d'erreur éventuels et le résultat attendu.

**madinadiouf** · 27/01/2020, 19h35

Bonjour,
D'abord je vous donne le jeu de données que j'au utilisé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Table agent
agent_id       prenom                 nom                   service_id
1                 Aissatou                Sall                           3
2                 Mame Djilane       Seck                           3
3                  Fatou                  Dieye                         3
4                  Yacine               Bocoum                       3

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Table mission
mission_id                  motif                                     depart_mission                                    arrivee_mission
1                               Tests ihris                                 2019-12-01                                          2019-12-10
2                                test ihris 2                               2019-12-05                                           2019-12-30
3                                test E_learning                        2019-12-20                                            2019-12-31
4                               rapidPro                                   2020-01-02                                              2020-01-16
5                                rapid prok                                2020-12-16                                                2020-01-15
6                                1000j                                      2020-01-18                                                 2020-01-19

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Table mission_agents
agent_id                                  mission_id
1                                                 1
1                                                 2
1                                                 3
2                                                 3
2                                                 4
3                                                 2
3                                                 3
4                                                 6

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Table services
service_id                                     nom_service
3                                                  Cellule informatique

Le résultat de la requête est en pièce jointe

**escartefigue** · 29/01/2020, 09h58

Bonjour,

Il faut communiquer la requête sous forme de texte et pas sous forme d'image pour qu'on puisse la récupérer

**madinadiouf** · 03/02/2020, 11h42

Bonjour,

je sais pas comment récupérer la requête sous phpmyadmin sous forme de fichier texte ? Vous en avez une idée?

**madinadiouf** · 03/02/2020, 12h37

Bonjour,

Voilà la requête et son résultat sous forme de fichier texte.j'ai réussi à l'avoir.

**madinadiouf** · 06/02/2020, 12h16

Bonjour,

Pour ceux qui s’intéressent à la solution, voici la requête à appliquer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SELECT
 S1.nom_service,A.agent_id, A.prenom, A.nom, M1.mission_id AS id_m1,M1.motif as motif1, M1.depart_mission AS depart_m1, M1.arrivee_mission AS arrivee_m1,
 'en chevauchement avec' as '',
 M2.mission_id AS id_m2,M2.motif as motif2, M2.depart_mission AS depart_m2, M2.arrivee_mission AS arrivee_m2 
 FROM mission_agents MA1 INNER JOIN mission_agents MA2 ON MA1.agent_id = MA2.agent_id AND MA1.mission_id < MA2.mission_id 
 INNER JOIN missions M1 ON MA1.mission_id = M1.mission_id INNER JOIN missions M2 ON MA2.mission_id = M2.mission_id
 INNER JOIN agent A ON MA1.agent_id = A.agent_id 
 INNER JOIN services S1 ON S1.service_id = A.service_id 
 WHERE M1.depart_mission <= M2.arrivee_mission AND M1.arrivee_mission >= M2.depart_mission

Merci et à plus

Comment vérifier si deux dates sont dans une mémé période ?

Requêtes MySQL

Discussions similaires

Partager

Partager