requête avec condition NOT BETWEEN et des heures

**TigerCX** · 30/01/2016, 18h14

Bonjour à tous,

Je travaille sur un projet qui avance bien mais j'ai besoin de votre aide pour le plus dur !

J'ai une base de données contenant une table vehicule (id, actif), assignation (id, heure_debut, heure_fin) et repvehicule (id, id_assignation, id_vehicule, date_rep).

La table vehicule n'a pas vraiment besoin d'explication, la table assignation représente l'heure de début et de fin d'une assignation (exemple 6h45 à 10h30) et la table repvehicule (pour répartition des véhicules) est une combinaison des 2 premières tables où on attribut un véhicule à une assignation.

J'ai cette requête qui fonctionne bien et qui me donne tous les véhicules actifs.

Code MYSQL :

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

SELECT id FROM vehicule WHERE actif=1

Bien mais pas assez précis car si un véhicule est déjà attribué dans une plage (6h45 à 10h30), je ne veux pas qu'il soit disponible pour une plage (7h30 à 12h30)

Code MYSQL :

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

1
2
3
4
5
6
 
SELECT vehicule.id, repvehicule.id_assignation, assignation.heure_debut, assignation.heure_fin
    FROM vehicule
        LEFT JOIN repvehicule ON repvehicule.id_vehicule = vehicule.id
        LEFT JOIN assignation ON assignation.id = repvehicule.id_assignation
    WHERE vehicule.actif = 1

J'ai fait cette requête pour m'assurer d'être en mesure de récupérer les heures de début et de fait pour un véhicule qui a déjà été attribué.

Ensuite j'ai voulu ajouter la mention NOT BETWEEN pour retirer les véhicules qui chevauchent l'assignation en cours, celle à laquelle je veux attribuer un véhicule

Code MYSQL :

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

1
2
3
4
5
6
7
 
SELECT vehicule.id, repvehicule.id_assignation, assignation.heure_debut, assignation.heure_fin
    FROM vehicule
        LEFT JOIN repvehicule ON repvehicule.id_vehicule = vehicule.id
        LEFT JOIN assignation ON assignation.id = repvehicule.id_assignation
    WHERE vehicule.actif = 1 AND
    '07:30:00' NOT BETWEEN assignation.heure_debut AND assignation.heure_fin

Cette requête ne me donne aucune ligne (pas de message d'erreur non plus) L'heure '07:30:00' je la récupère via une autre requête.

Quand j'assigne un véhicule à une assignation et que j'exécute la 2e requête, je vois toutes les informations (id_assignation, heure_debut et heure_fin) et les autres me donnent NULL (normal, elles n'ont pas de véhicule associé)

Merci de me démêler !

**Artemus24** · 02/02/2016, 13h38

Salut tigerCX.

Posons le problème.
Tu as une période de location de ta voiture allant de date_deb jusqu'à date_fin.
Et tu recherches dans une autre période s'il existe des locations : peri_deb et peri_fin.

Si on décompose le problème, on se retrouve avec quatre cas qui sont :

Code :

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

1
2
3
4
5
6
 
1) date_deb < peri_deb < peri_fin < date_fin
2) date_deb < peri_deb < date_fin < peri_fin 
 
3) peri_deb < date_deb < peri_fin < date_fin
4) peri_deb < date_deb < date_fin < peri_fin

Un seul de ces quatre cas correspondra à la période de recouvrement que tu recherches.

Avec un between, on sait résoudre les cas suivants :

Code :

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

1
2
3
 
a) peri_deb < date_deb < peri_fin
b) peri_deb < date_fin < peri_fin

le cas a) résoud les cas 3) et 4)
le cas b) résoud le cas 2).

Il manque que le cas 1) que l'on ne peut pas résoudre avec un between.
Voici un exemple avec la solution :

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
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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
drop table if exists `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`        integer unsigned  NOT NULL AUTO_INCREMENT,
  `date_deb`  datetime          NOT NULL,
  `date_fin`  datetime          NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`date_deb`,`date_fin`) values
  ('2016-01-03 11:00:00', '2016-01-03 17:00:00'),
  ('2016-01-12 14:00:00', '2016-01-13 19:00:00'),
  ('2016-01-15 15:00:00', '2016-01-15 16:00:00')
--------------
 
--------------
select * from test
--------------
 
+----+---------------------+---------------------+
| id | date_deb            | date_fin            |
+----+---------------------+---------------------+
|  1 | 2016-01-03 11:00:00 | 2016-01-03 17:00:00 |
|  2 | 2016-01-12 14:00:00 | 2016-01-13 19:00:00 |
|  3 | 2016-01-15 15:00:00 | 2016-01-15 16:00:00 |
+----+---------------------+---------------------+
--------------
set @peri_deb = '2016-01-13 07:00:00'
--------------
 
--------------
set @peri_fin = '2016-01-13 12:00:00'
--------------
 
--------------
select * from test where  date_deb < @peri_deb and @peri_deb <  date_fin and  date_fin < @peri_fin union
select * from test where  date_deb < @peri_deb and @peri_deb < @peri_fin and @peri_fin <  date_fin union
select * from test where @peri_deb <  date_deb and  date_deb <  date_fin and  date_fin < @peri_fin union
select * from test where @peri_deb <  date_deb and  date_deb < @peri_fin and @peri_fin <  date_fin
--------------
 
+----+---------------------+---------------------+
| id | date_deb            | date_fin            |
+----+---------------------+---------------------+
|  2 | 2016-01-12 14:00:00 | 2016-01-13 19:00:00 |
+----+---------------------+---------------------+
--------------
select * from test where  date_deb < @peri_fin
                     and @peri_deb <  date_fin
--------------
 
+----+---------------------+---------------------+
| id | date_deb            | date_fin            |
+----+---------------------+---------------------+
|  2 | 2016-01-12 14:00:00 | 2016-01-13 19:00:00 |
+----+---------------------+---------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

**TigerCX** · 03/02/2016, 23h55

Merci Artemus24 pour ton aide.

Je crois que je n'ai pas été assez clair dans ma demande. Ma requête servira de liste déroulante dynamique. L'application est pour attribuer des autobus sur une plage horaire journalière. Je ne conserve pas les données des plages horaires avec les véhicules puisque les plages seront toujours les mêmes pour chaque jour de la semaine. De plus, on m'a toujours de ne jamais doublé les données, donc les horaires d'assignations sont à part des véhicules et je dois les retrouvés à travers une autre table qui fait l'association entre les 2 (vehicule <-> repvehicule <-> assignation).

Avec cette requête :

Code :

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

1
2
3
4
5
6
SELECT vehicule.id, repvehicule.id_assignation, assignation.heure_debut, assignation.heure_fin 
  FROM `vehicule` 
    LEFT JOIN repvehicule ON repvehicule.id_vehicule = vehicule.id 
    LEFT JOIN assignation ON repvehicule.id_assignation = assignation.id 
  WHERE vehicule.actif=1 AND (repvehicule.date_rep = '2016-01-28' OR repvehicule.date_rep IS NULL)
  ORDER BY vehicule.id ASC

Voici le résultat que j'obtiens :

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
id	id_assignation	heure_debut	heure_fin
58301	9		06:45:00		10:45:00
58302	11		06:45:00		12:45:00
58303	12		07:30:00		11:30:00
61301	NULL		NULL		NULL
61302	NULL		NULL		NULL
62301	NULL		NULL		NULL
62302	NULL		NULL		NULL
62303	NULL		NULL		NULL
63301	NULL		NULL		NULL
63302	14		07:00:00	10:30:00
64302	NULL		NULL		NULL
64303	NULL		NULL		NULL
64304	NULL		NULL		NULL
65301	NULL		NULL		NULL
65302	NULL		NULL		NULL

Dans cette requête, je n'ai besoin que du numéro du véhicule, mais à des fins de visualisation, j'affiche d'autres éléments temporaires.

Cette requête sera lancé à chaque fois que je vais ouvrir une liste déroulante et générera la dite liste déroulante pour n'afficher les véhicules qui peuvent y être affecté.

L'application ressemble à ceci :

J'aimerais supprimer les véhicules qui entre en conflit d'horaire pour qu'ils n'apparaissent pas dans la liste dynamique.

Est-ce possible de le faire avec l'architecture de ma base de données actuelle ? Si oui, est-ce possible de le faire à partir de ma requête qui me donne pratiquement ce dont j'ai besoin ?

Merci à l'avance !

**escartefigue** · 04/02/2016, 09h33

bonjour,

Je vous ai fait un petit schéma pour visualiser une façon simple de contrôler des chevauchements de périodes :
Nom : ChvPer.png
Affichages : 240
Taille : 9,6 Ko

Nom : ChvPer.png
Affichages : 240
Taille : 9,6 Ko

La traduction SQL est donc très simple

Attention à tenir compte de la façon dont localement les dates ou heures de fin sont gérées (null, valeur fixe...)

**Artemus24** · 04/02/2016, 09h36

Salut TigerCX.

Envoyé par TigerCX

Je crois que je n'ai pas été assez clair dans ma demande.

Vous avez été assez clair.

Votre problème concernait la faisabilité de ne pas sélectionner des plages horaires comprises ou à cheval sur une autre plage horaire.
Voici vos explications :

Envoyé par TigerCX

Bien mais pas assez précis car si un véhicule est déjà attribué dans une plage (6h45 à 10h30), je ne veux pas qu'il soit disponible pour une plage (7h30 à 12h30)

J'ai compris que la première plage horaire, celle allant de 6h45 jusqu'à 10h30 correspond à l'usage du véhicule.
Autrement dit, durant cette plage horaire, quelqu'un se sert de ce véhicule, et de ce fait, il n'est plus disponible.
Je suis parti de l'hypothèse que c'est cette plage horaire qui est stocké dans votre table "assignation".

La seconde plage horaire, celle allant de 7h30 jusqu'à 12h30 correspond à la demande de la disponibilité des véhicules.
Autrement dit, quels sont les véhicules disponibles durant cette seconde plage horaire.
Sur ce point, j'ai répondu par un exemple qui fonctionne. J'ai introduit aussi la date en plus de l'heure, mais le principe reste le même.

Par contre, ce que je n'ai pas compris, c'est d'où vous sortez cette seconde plage horaire.

Envoyé par TigerCX

Je ne conserve pas les données des plages horaires avec les véhicules puisque les plages seront toujours les mêmes pour chaque jour de la semaine.

Dans votre premier message, vous indiquez :

Envoyé par TigerCX

J'ai une base de données contenant une table vehicule (id, actif), assignation (id, heure_debut, heure_fin) et repvehicule (id, id_assignation, id_vehicule, date_rep).

J'ai donné un exemple à partir de la table "assignation".
Et donc vous ne savez pas comment réintégrer mon exemple dans votre application. Est-ce bien cela ?

Envoyé par TigerCX

J'aimerais supprimer les véhicules qui entre en conflit d'horaire pour qu'ils n'apparaissent pas dans la liste dynamique.

Dans l'exemple que vous donnez, je ne voie que la plage horaire de l'utilisation du véhicule. Exemple :

Ici, la plage d'utilisation de votre véhicule va de 6H45 jusqu'à 10H45, soit 4H00.

C'est la phrase : "qui entre en conflit horaire" que je ne comprends pas. Elle entre en conflit avec quoi ?

@+

**TigerCX** · 04/02/2016, 16h21

Rebonjour Artemus24 !

Merci encore pour votre temps. J'ai travaillé hier soir et ce matin sur mon problème. Au départ je croyais que vous me proposiez une réorganisation de mon architecture, mais après plusieurs lectures de votre texte, j'ai compris que vous essayiez de m'aider avec la «logique» de ma requête.

La logique ne me posait pas problème mais bien la création de ma requête. En ajoutant la date et les heures à ma requête, je récupérais seulement les véhicules qui étaient déjà assignés, donc les véhicules libres (non-utilisés) n'étaient pas affiché. J'ai compris que je devais ajouter la clause «OR IS NULL» pour y inclure les véhicules non utilisés.

Comme j'ai travaillé beaucoup sur ma requête, j'en suis au problème de logique maintenant !

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT vehicule.id, repvehicule.id_assignation, assignation.heure_debut, assignation.heure_fin 
  FROM `vehicule` 
    LEFT JOIN repvehicule ON repvehicule.id_vehicule = vehicule.id 
    LEFT JOIN assignation ON repvehicule.id_assignation = assignation.id 
  WHERE
    vehicule.actif=1 AND 
    (((repvehicule.date_rep = '$date_rep' OR repvehicule.date_rep IS NULL) AND
    (('$heure_debut' NOT BETWEEN assignation.heure_debut AND assignation.heure_fin AND '$heure_fin' NOT BETWEEN assignation.heure_debut AND assignation.heure_fin) OR assignation.heure_debut IS NULL))
    $where_old_vehicule)
  ORDER BY vehicule.id ASC

Envoyé par Artemus24

Par contre, ce que je n'ai pas compris, c'est d'où vous sortez cette seconde plage horaire.

Dans votre premier message, vous indiquez :

J'ai donné un exemple à partir de la table "assignation".
Et donc vous ne savez pas comment réintégrer mon exemple dans votre application. Est-ce bien cela ?

Exactement, mais après plusieurs tentatives, j'y suis «presque» arrivé.

Envoyé par Artemus24

Dans l'exemple que vous donnez, je ne voie que la plage horaire de l'utilisation du véhicule. Exemple :

Ici, la plage d'utilisation de votre véhicule va de 6H45 jusqu'à 10H45, soit 4H00.

C'est la phrase : "qui entre en conflit horaire" que je ne comprends pas. Elle entre en conflit avec quoi ?

Exemple concret : Quand je clique sur la liste déroulante de l'assignation, je compare la plage d'horaire de cette assignation avec les autres pour éliminer les véhicules qui sont déjà sur la route (en conflit).

Donc si je clique sur le menu déroulant de l'assignation A-07 (8h00 à 13h00), les véhicules 58301, 58302, 58302 et 63302 qui sont déjà assignés dans la même plage horaire.

____________________________________________

Ça doit faire 2-3 heures que j'écris ce texte, me pose des questions, viens pour les écrire, j'y réponds par moi-même... etc...

Voici où j'en suis rendu avec ma requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT vehicule.id, repvehicule.id_assignation, assignation.heure_debut, assignation.heure_fin 
  FROM `vehicule` 
    LEFT JOIN repvehicule ON repvehicule.id_vehicule = vehicule.id 
    LEFT JOIN assignation ON repvehicule.id_assignation = assignation.id 
  WHERE
    vehicule.actif=1 AND 
    (((repvehicule.date_rep = '2016-01-28' OR repvehicule.date_rep IS NULL) AND
    (('15:00:00' NOT BETWEEN assignation.heure_debut AND assignation.heure_fin
      '19:00:00' NOT BETWEEN assignation.heure_debut AND assignation.heure_fin) OR 
      assignation.heure_debut IS NULL)))
  ORDER BY vehicule.id ASC

Avec tout ça, j'ai le résultat espéré, ou presque (je sais, je suis un éternel insatisfait !)

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
id		id_assignation	heure_debut	heure_fin
58301	NULL		NULL		NULL
58302	NULL		NULL		NULL
58303	NULL		NULL		NULL
61301	NULL		NULL		NULL
61302	NULL		NULL		NULL
62301	21		08:30:00		12:30:00
62302	17		08:00:00	13:00:00
62303	29		07:45:00		10:00:00
63301	25		07:45:00		12:15:00
63302	23		07:30:00		10:30:00
64302	12		07:30:00		11:30:00
64303	14		07:00:00	10:30:00
64304	27		06:45:00		09:30:00
65301	9		06:45:00		10:45:00
65301	5		11:00:00	15:00:00
65302	11		06:45:00		12:45:00

Vous pouvez voir que le 65301 se trouve 2 fois dans le résultat de la requête (je compare avec l'assignation P-08 15h00 à 19h00). J'ai pensé faire un GROUP BY vehicule.id, ça amène un autre problème, qui est déjà présent dans la requête actuelle.

J'attribue le 65301 à la plage 6h45 à 10h45. J'attribue le 65301 à la plage 11h00 à 15h00. Si je recherche les véhicules disponibles pour une période 11h00 à 15h45, le 65301 va apparaître dans ma liste puisque la première période (6h45 à 10h45) ne chevauche pas le 11h00 à 15h45.

Je ne sais pas si c'est possible de le faire uniquement en MySQL ou si je dois faire ce traitement en PHP. J'essais de réduire au maximum les requêtes et traitements puisque je ne veux pas que la liste prenne de temps à s'afficher.

Merci encore !

**TigerCX** · 04/02/2016, 17h57

Je viens d'avoir une idée !

Je crois que ça pourrait se faire avec un count. Est-ce possible de faire un count sur la condition et que si on trouve au moins une correspondance, on enlève le véhicule ?

**Artemus24** · 04/02/2016, 18h36

Salut TigerCX.

J'ai repris la totalité de votre exemple et j'ai créé une base de données afin de faire des tests.
Je travaille mieux sur un exemple que de réfléchir dans le vide.

Il me faudrait un jeu d'essai réaliste de votre problème. C'est-à-dire vos trois tables : véhicule, assignation et repvéhicule.
Puis ensuite, sur ce jeu d'essai, le résultat que vous attendez.

Envoyé par TigerCX

Exemple concret : Quand je clique sur la liste déroulante de l'assignation, je compare la plage d'horaire de cette assignation avec les autres pour éliminer les véhicules qui sont déjà sur la route (en conflit).
Donc si je clique sur le menu déroulant de l'assignation A-07 (8h00 à 13h00), les véhicules 58301, 58302, 58302 et 63302 qui sont déjà assignés dans la même plage horaire.

Pouvez-vous être plus clair sur ces périodes ?
Mon problème n'est pas de calculer les exclusions mais de connaitre la nature de ces deux périodes.
La première période correspondrait à l'assignation d'un véhicule, soit heure_debut et heure_fin qui se trouve dans votre table assignation.
Autrement dit, ce véhicule a été réservé pour une période donnée.

La deuxième période correspondrait à la demande de disponibilité des véhicules. Cette période est une saisie.
Je dis cela, car je retrouve une période en tant que paramètre dans votre dernière requête.

A titre indicatif, je vous communique ce que j'ai fait :

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
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
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
drop table if exists `vehicule`
--------------
 
--------------
CREATE TABLE `vehicule`
( `id`    integer unsigned NOT NULL,
  `actif` tinyint unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `vehicule` (`id`,`actif`) values
  (58301, 1), (58302, 1), (58303, 1), (61301, 1), (61302, 1),
  (62301, 1), (62302, 1), (62303, 1), (63301, 1), (63302, 1),
  (64302, 1), (64303, 1), (64304, 1), (65301, 1), (65302, 1)
--------------
 
--------------
select * from vehicule
--------------
 
+-------+-------+
| id    | actif |
+-------+-------+
| 58301 |     1 |
| 58302 |     1 |
| 58303 |     1 |
| 61301 |     1 |
| 61302 |     1 |
| 62301 |     1 |
| 62302 |     1 |
| 62303 |     1 |
| 63301 |     1 |
| 63302 |     1 |
| 64302 |     1 |
| 64303 |     1 |
| 64304 |     1 |
| 65301 |     1 |
| 65302 |     1 |
+-------+-------+
--------------
drop table if exists `assignation`
--------------
 
--------------
CREATE TABLE `assignation`
( `id`          integer unsigned NOT NULL,
  `heure_debut` time             NOT NULL,
  `heure_fin`   time             NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `assignation` (`id`,`heure_debut`,`heure_fin`) values
  ( 5, '11:00:00', '15:00:00'),
  ( 9, '06:45:00', '10:45:00'),
  (11, '06:45:00', '12:45:00'),
  (12, '07:30:00', '11:30:00'),
  (14, '07:00:00', '10:30:00'),
  (17, '08:00:00', '13:00:00'),
  (21, '08:30:00', '12:30:00'),
  (23, '07:30:00', '10:30:00'),
  (25, '07:45:00', '12:15:00'),
  (27, '06:45:00', '09:30:00'),
  (29, '07:45:00', '10:00:00')
--------------
 
--------------
select * from `assignation`
--------------
 
+----+-------------+-----------+
| id | heure_debut | heure_fin |
+----+-------------+-----------+
|  5 | 11:00:00    | 15:00:00  |
|  9 | 06:45:00    | 10:45:00  |
| 11 | 06:45:00    | 12:45:00  |
| 12 | 07:30:00    | 11:30:00  |
| 14 | 07:00:00    | 10:30:00  |
| 17 | 08:00:00    | 13:00:00  |
| 21 | 08:30:00    | 12:30:00  |
| 23 | 07:30:00    | 10:30:00  |
| 25 | 07:45:00    | 12:15:00  |
| 27 | 06:45:00    | 09:30:00  |
| 29 | 07:45:00    | 10:00:00  |
+----+-------------+-----------+
--------------
drop table if exists `repvehicule`
--------------
 
--------------
CREATE TABLE `repvehicule`
( `id`              integer unsigned NOT NULL auto_increment,
  `id_assignation`  integer unsigned NOT NULL,
  `id_vehicule`     integer unsigned NOT NULL,
  `date_rep`        date             NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `repvehicule` (`id_assignation`,`id_vehicule`,`date_rep`) values
  ( 5, 65301, '2016-01-26'),
  ( 9, 65301, '2016-01-26'),
  (11, 65302, '2016-01-26'),
  (12, 64302, '2016-01-26'),
  (14, 64303, '2016-01-26'),
  (17, 62302, '2016-01-26'),
  (21, 62301, '2016-01-26'),
  (23, 63302, '2016-01-26'),
  (25, 63301, '2016-01-26'),
  (27, 64304, '2016-01-26'),
  (29, 62303, '2016-01-26')
--------------
 
--------------
select * from repvehicule
--------------
 
+----+----------------+-------------+------------+
| id | id_assignation | id_vehicule | date_rep   |
+----+----------------+-------------+------------+
|  1 |              5 |       65301 | 2016-01-26 |
|  2 |              9 |       65301 | 2016-01-26 |
|  3 |             11 |       65302 | 2016-01-26 |
|  4 |             12 |       64302 | 2016-01-26 |
|  5 |             14 |       64303 | 2016-01-26 |
|  6 |             17 |       62302 | 2016-01-26 |
|  7 |             21 |       62301 | 2016-01-26 |
|  8 |             23 |       63302 | 2016-01-26 |
|  9 |             25 |       63301 | 2016-01-26 |
| 10 |             27 |       64304 | 2016-01-26 |
| 11 |             29 |       62303 | 2016-01-26 |
+----+----------------+-------------+------------+
--------------
select  v.id,
       rv.id_assignation,
        a.heure_debut,
        a.heure_fin
from            vehicule    as v
 
left outer join repvehicule as rv
on rv.id_vehicule = v.id
 
left outer join assignation as a
on a.id = rv.id_assignation
 
where   v.actif = 1
  and (rv.date_rep in ('2016-01-26')
   or  rv.date_rep is null)
order by v.id
--------------
 
+-------+----------------+-------------+-----------+
| id    | id_assignation | heure_debut | heure_fin |
+-------+----------------+-------------+-----------+
| 58301 |           NULL | NULL        | NULL      |
| 58302 |           NULL | NULL        | NULL      |
| 58303 |           NULL | NULL        | NULL      |
| 61301 |           NULL | NULL        | NULL      |
| 61302 |           NULL | NULL        | NULL      |
| 62301 |             21 | 08:30:00    | 12:30:00  |
| 62302 |             17 | 08:00:00    | 13:00:00  |
| 62303 |             29 | 07:45:00    | 10:00:00  |
| 63301 |             25 | 07:45:00    | 12:15:00  |
| 63302 |             23 | 07:30:00    | 10:30:00  |
| 64302 |             12 | 07:30:00    | 11:30:00  |
| 64303 |             14 | 07:00:00    | 10:30:00  |
| 64304 |             27 | 06:45:00    | 09:30:00  |
| 65301 |              5 | 11:00:00    | 15:00:00  |
| 65301 |              9 | 06:45:00    | 10:45:00  |
| 65302 |             11 | 06:45:00    | 12:45:00  |
+-------+----------------+-------------+-----------+
--------------
select tb1.id,
       tb1.heure_debut,
       tb1.heure_fin,
       tb2.id          as 'periode_id',
       tb2.heure_debut as 'periode_debut',
           tb2.heure_fin   as 'periode_fin'
from   assignation as tb1,
       assignation as tb2
 
where not (tb1.heure_debut < tb2.heure_fin and tb1.heure_fin > tb2.heure_debut)
  and tb1.id != tb2.id
--------------
 
+----+-------------+-----------+------------+---------------+-------------+
| id | heure_debut | heure_fin | periode_id | periode_debut | periode_fin |
+----+-------------+-----------+------------+---------------+-------------+
|  9 | 06:45:00    | 10:45:00  |          5 | 11:00:00      | 15:00:00    |
| 14 | 07:00:00    | 10:30:00  |          5 | 11:00:00      | 15:00:00    |
| 23 | 07:30:00    | 10:30:00  |          5 | 11:00:00      | 15:00:00    |
| 27 | 06:45:00    | 09:30:00  |          5 | 11:00:00      | 15:00:00    |
| 29 | 07:45:00    | 10:00:00  |          5 | 11:00:00      | 15:00:00    |
|  5 | 11:00:00    | 15:00:00  |          9 | 06:45:00      | 10:45:00    |
|  5 | 11:00:00    | 15:00:00  |         14 | 07:00:00      | 10:30:00    |
|  5 | 11:00:00    | 15:00:00  |         23 | 07:30:00      | 10:30:00    |
|  5 | 11:00:00    | 15:00:00  |         27 | 06:45:00      | 09:30:00    |
|  5 | 11:00:00    | 15:00:00  |         29 | 07:45:00      | 10:00:00    |
+----+-------------+-----------+------------+---------------+-------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

requête avec condition NOT BETWEEN et des heures

Requêtes MySQL

Discussions similaires

Partager

Partager