Eclater des lignes en fonction de la valeur de colonnes

**nietsabes** · 25/10/2017, 15h02

Bonjour à tous,

je gère des calendriers d'opérateurs pour mon entreprise.

J'ai d'une part une table indiquant les jours et heures (planning_start_time & planning_end_time) et d'autre part une table indiquant les plages occupées (busy_start & busy_end).

Ce que j'ai réussi à faire pour le moment est pour chaque jour travaillé, indiquer si oui ou non une plage occupée existe. Si plusieurs plages occupées existent alors les lignes sont dupliquées.

~~Par exemple :~~
EDIT: Par exemple le résultat d'une vue

operator_id	planning_start_time	planning_end_time	busy_start	busy_end
2	15/11/2017 07:00	15/11/2017 17:00	NULL	NULL
2	16/11/2017 07:00	16/11/2017 17:00	16/11/2017 08:00	16/11/2017 12:00
2	16/11/2017 07:00	16/11/2017 17:00	16/11/2017 14:00	16/11/2017 16:00

On voit que pour la journée du 16 on a deux plages occupées. Par contre pour le 15 rien du tout ce qui est normal, je n'ai pas de tâche programmée pour ce jour là.

Donc ça c'est un premier bon point.

Ce que je voudrais faire à présent c'est finir avec une table qui me donne les plages disponibles. Pour reprendre l'exemple ci-dessus ça donnerait:

operator_id	available_start_time	available_end_time
2	2017-11-15 07:00:00	2017-11-15 17:00:00
2	2017-11-16 07:00:00	2017-11-16 08:00:00
2	2017-11-16 12:00:00	2017-11-16 14:00:00
2	2017-11-16 16:00:00	2017-11-16 17:00:00

Pour la ligne en vert c'est évident, il n'y a pas de plage occupée, toute la journée travaillée est libre.

Pour les lignes en rouge c'est une autre affaire et c'est là où je cherche des idées sur la meilleure façon d'éclater ces lignes pour ne finir qu'avec les plages réellement libres (plages travaillées - plages occupées) avec une plage par ligne.

Un grand merci

**isabelle.letrong** · 25/10/2017, 17h15

Bonjour nietsabes,

Hum...2 colonnes avec NULL autorisé, cela semble suspect (Bonjour les comparaisons sur des datetime dans vos requêtes futures !)

Revenons à la conception : les 2 tables dont vous avez besoin ne sont probablement pas celles que vous évoquez :
Il vous faut :

Une table de planning général de l'entreprise où sont précisées pour chaque journée les heures travaillées,
une table des plages occupés par les opérateurs, qui n'autorisera pas de NULL. Il vous faudra par ailleurs vous poser la question du chevauchement potentiel des plages...

La table des plages disponible se déduit des autres tables et n'a donc aucun intérêt à exister en temps que table permanente,
Les tables que vous nous montrez ne devrait n'être en fait que des vues (par exemple mettre dans une même table les colonnes operator ID, planning_start_time et planning_end_time n' a pas de sens..)

**nietsabes** · 25/10/2017, 18h09

Bonjour Isabelle,

merci de votre réponse.

Comme vous le suggérez j'ai bien bien deux tables distinctes. Dans mon précédent message le premier tableau était déjà le résultat d'une vue.

La première table dont je dispose précise les heures travaillées opérateur par opérateur (ils n'on pas tous le même emploi du temps)

operator_id	planning_start_time	planning_end_time
2	15/11/2017 07:00	15/11/2017 17:00
2	16/11/2017 07:00	16/11/2017 17:00
2	17/11/2017 07:00	17/11/2017 17:00

et une seconde avec les plages occupées par une tâche spécifique opérateur par opérateur:

operator_id	busy_start	busy_end	Desciption
2	16/11/2017 08:00	16/11/2017 12:00	Fraisage
2	16/11/2017 14:00	16/11/2017 16:00	Manutention

Je pense qu'avec cette clarification nous parlons bien de la même chose. Je cherche à déduire de ces deux tables une vue qui me donne les plages libres opérateur par opérateur, cette vue étant déduite des deux tables de base.

**Artemus24** · 26/10/2017, 04h57

Salut nietsabes.

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
239
240
241
242
243
244
245
246
247
--------------
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 `tabone`
--------------
 
--------------
CREATE TABLE `tabone`
( `id`                   integer  unsigned  auto_increment not null  primary key,
  `operator_id`          smallint unsigned                 not null,
  `planning_start_time`  datetime                          not null,
  `planning_end_time`    datetime                          not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabone` (`operator_id`,`planning_start_time`,`planning_end_time`) values
  (2, '2017-11-15 07:00:00', '2017-11-15 17:00:00'),
  (2, '2017-11-16 07:00:00', '2017-11-16 17:00:00'),
  (2, '2017-11-17 07:00:00', '2017-11-17 17:00:00')
--------------
 
--------------
select * from `tabone`
--------------
 
+----+-------------+---------------------+---------------------+
| id | operator_id | planning_start_time | planning_end_time   |
+----+-------------+---------------------+---------------------+
|  1 |           2 | 2017-11-15 07:00:00 | 2017-11-15 17:00:00 |
|  2 |           2 | 2017-11-16 07:00:00 | 2017-11-16 17:00:00 |
|  3 |           2 | 2017-11-17 07:00:00 | 2017-11-17 17:00:00 |
+----+-------------+---------------------+---------------------+
--------------
DROP TABLE IF EXISTS `tabtwo`
--------------
 
--------------
CREATE TABLE `tabtwo`
( `id`           integer  unsigned  auto_increment not null  primary key,
  `operator_id`  smallint unsigned                 not null,
  `busy_start`   datetime                          not null,
  `busy_end`     datetime                          not null,
  `description`  varchar(255)                      not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabtwo` (`operator_id`,`busy_start`,`busy_end`,`description`) values
  (2, '2017-11-16 08:00:00', '2017-11-16 12:00:00','Fraisage'),
  (2, '2017-11-16 14:00:00', '2017-11-16 16:00:00','Manutention')
--------------
 
--------------
select * from `tabtwo`
--------------
 
+----+-------------+---------------------+---------------------+-------------+
| id | operator_id | busy_start          | busy_end            | description |
+----+-------------+---------------------+---------------------+-------------+
|  1 |           2 | 2017-11-16 08:00:00 | 2017-11-16 12:00:00 | Fraisage    |
|  2 |           2 | 2017-11-16 14:00:00 | 2017-11-16 16:00:00 | Manutention |
+----+-------------+---------------------+---------------------+-------------+
--------------
DROP TABLE IF EXISTS `trav`
--------------
 
--------------
CREATE TABLE `trav`
( `id`                    integer  unsigned  auto_increment not null  primary key,
  `operator_id`           smallint unsigned                 not null,
  `available_start_time`  datetime                          not null,
  `available_end_time`    datetime                          not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP PROCEDURE IF EXISTS `remplir`
--------------
 
--------------
CREATE PROCEDURE `remplir` ()
DETERMINISTIC
NO SQL
BEGIN
  DECLARE _operator  smallint      default NULL;
  DECLARE _plandeb   datetime      default NULL;
  DECLARE _planfin   datetime      default NULL;
  DECLARE _busydeb   datetime      default NULL;
  DECLARE _busyfin   datetime      default NULL;
 
  DECLARE _precope   smallint      default NULL;
  DECLARE _precfin   datetime      default NULL;
 
  DECLARE _avaideb   datetime      default NULL;
  DECLARE _avaifin   datetime      default NULL;
 
  DECLARE _rupt1     varchar(255)  default NULL;
  DECLARE _rupt2     tinyint       default NULL;
 
  DECLARE _fin       INTEGER      DEFAULT 1;
  DECLARE _tab       CURSOR  FOR SELECT  t1.operator_id, t1.planning_start_time, t1.planning_end_time, t2.busy_start, t2.busy_end
                                   from  `tabone` as t1
                        left outer join  `tabtwo` as t2
                                     on  t2.operator_id  = t1.operator_id
                                    and  t2.busy_start  >= t1.planning_start_time
                                    and  t2.busy_end    <= t1.planning_end_time
                               order by  t1.operator_id, t1.planning_start_time, t2.busy_start;
 
  DECLARE CONTINUE  HANDLER FOR NOT FOUND SET _fin = 0;
 
  OPEN  _tab;
  FETCH _tab INTO _operator, _plandeb, _planfin, _busydeb, _busyfin;
 
  SET _rupt1   = concat(_operator,date(_plandeb));
  SET _rupt2   = 1;
  SET _precope = _operator;
  SET _avaideb = _plandeb;
  SET _precfin = _planfin;
 
  WHILE (_fin)
  DO
    SELECT _operator, _plandeb, _planfin, _busydeb, _busyfin;
 
    IF _busydeb is null THEN
       INSERT INTO `trav` (`operator_id`,`available_start_time`,`available_end_time`)  VALUES (_operator, _plandeb, _planfin);
       SET _rupt2 = 0;
    ELSE
       SET _avaifin = _busydeb;
       INSERT INTO `trav` (`operator_id`,`available_start_time`,`available_end_time`)  VALUES (_operator, _avaideb, _avaifin);
       SET _avaideb = _busyfin;
    END IF;
 
    FETCH _tab INTO _operator, _plandeb, _planfin, _busydeb, _busyfin;
 
    IF _rupt1 <> concat(_operator,date(_plandeb)) THEN
       IF _rupt2 THEN
           INSERT INTO `trav` (`operator_id`,`available_start_time`,`available_end_time`)  values (_precope, _avaideb, _precfin);
       END IF;
 
       SET _precope = _operator;
       SET _avaideb = _plandeb;
       SET _precfin = _planfin;
    END IF;
 
    SET _rupt1 = concat(_operator,date(_plandeb));
    SET _rupt2 = 1;
  END WHILE;
 
  CLOSE _tab;
END
--------------
 
--------------
call `remplir` ()
--------------
 
+-----------+---------------------+---------------------+----------+----------+
| _operator | _plandeb            | _planfin            | _busydeb | _busyfin |
+-----------+---------------------+---------------------+----------+----------+
|         2 | 2017-11-15 07:00:00 | 2017-11-15 17:00:00 | NULL     | NULL     |
+-----------+---------------------+---------------------+----------+----------+
+-----------+---------------------+---------------------+---------------------+---------------------+
| _operator | _plandeb            | _planfin            | _busydeb            | _busyfin            |
+-----------+---------------------+---------------------+---------------------+---------------------+
|         2 | 2017-11-16 07:00:00 | 2017-11-16 17:00:00 | 2017-11-16 08:00:00 | 2017-11-16 12:00:00 |
+-----------+---------------------+---------------------+---------------------+---------------------+
+-----------+---------------------+---------------------+---------------------+---------------------+
| _operator | _plandeb            | _planfin            | _busydeb            | _busyfin            |
+-----------+---------------------+---------------------+---------------------+---------------------+
|         2 | 2017-11-16 07:00:00 | 2017-11-16 17:00:00 | 2017-11-16 14:00:00 | 2017-11-16 16:00:00 |
+-----------+---------------------+---------------------+---------------------+---------------------+
+-----------+---------------------+---------------------+----------+----------+
| _operator | _plandeb            | _planfin            | _busydeb | _busyfin |
+-----------+---------------------+---------------------+----------+----------+
|         2 | 2017-11-17 07:00:00 | 2017-11-17 17:00:00 | NULL     | NULL     |
+-----------+---------------------+---------------------+----------+----------+
--------------
select           t1.operator_id,
                 t1.planning_start_time,
                 t1.planning_end_time,
                 t2.busy_start,
                 t2.busy_end
 
           from  `tabone` as t1
 
left outer join  `tabtwo` as t2
             on  t2.operator_id  = t1.operator_id
            and  t2.busy_start  >= t1.planning_start_time
            and  t2.busy_end    <= t1.planning_end_time
 
       order by  t1.operator_id, t1.planning_start_time, t2.busy_start
--------------
 
+-------------+---------------------+---------------------+---------------------+---------------------+
| operator_id | planning_start_time | planning_end_time   | busy_start          | busy_end            |
+-------------+---------------------+---------------------+---------------------+---------------------+
|           2 | 2017-11-15 07:00:00 | 2017-11-15 17:00:00 | NULL                | NULL                |
|           2 | 2017-11-16 07:00:00 | 2017-11-16 17:00:00 | 2017-11-16 08:00:00 | 2017-11-16 12:00:00 |
|           2 | 2017-11-16 07:00:00 | 2017-11-16 17:00:00 | 2017-11-16 14:00:00 | 2017-11-16 16:00:00 |
|           2 | 2017-11-17 07:00:00 | 2017-11-17 17:00:00 | NULL                | NULL                |
+-------------+---------------------+---------------------+---------------------+---------------------+
--------------
select  *
  from  `trav`
--------------
 
+----+-------------+----------------------+---------------------+
| id | operator_id | available_start_time | available_end_time  |
+----+-------------+----------------------+---------------------+
|  1 |           2 | 2017-11-15 07:00:00  | 2017-11-15 17:00:00 |
|  2 |           2 | 2017-11-16 07:00:00  | 2017-11-16 08:00:00 |
|  3 |           2 | 2017-11-16 12:00:00  | 2017-11-16 14:00:00 |
|  4 |           2 | 2017-11-16 16:00:00  | 2017-11-16 17:00:00 |
|  5 |           2 | 2017-11-17 07:00:00  | 2017-11-17 17:00:00 |
+----+-------------+----------------------+---------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

**nietsabes** · 26/10/2017, 11h10

Whao la surprise du matin !

Très impressionné Artemus24 et surtout très reconnaissant de ton temps là dessus.

J'ai lu avec attention tout le code. Je ne connaissais rien aux procédures et là je découvre un truc ultra-puissant....

J'aurais sûrement des questions mais déjà je voulais te dire merci et bravo.

**nietsabes** · 26/10/2017, 12h02

Me revoici.

A partir de ton code Artemus je peux définir les plages disponibles opérateur par opérateur.

A présent admettons que j'ai une tâche précise à exécuter d'une durée de 30 minutes. Admettons que je souhaite effectuer cette tâche à partir du 16 novembre à 09:00:00. Avec le code suivant:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SET @date_souhait='2017-11-16 09:00:00'
--------------
SET @duree_plage_minutes=30
 
--------------
SELECT operator_id,  available_start_time, available_end_time
FROM trav
WHERE id IN (
           SELECT min(id)
             FROM trav 
            WHERE available_end_time>@date_souhait AND
            ROUND(TIME_TO_SEC(timediff(available_end_time,available_start_time))/60) >=@duree_plage_minutes)

... je vais obtenir la première plage disponible opérateur par opérateur au vu de mon planning actuel n'est-ce pas ?

Est-ce la bonne façon de faire?

**Artemus24** · 27/10/2017, 04h45

Salut nietsabes.

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
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
--------------
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 `tabone`
--------------
 
--------------
CREATE TABLE `tabone`
( `id`                   integer  unsigned  auto_increment not null  primary key,
  `operator_id`          smallint unsigned                 not null,
  `planning_start_time`  datetime                          not null,
  `planning_end_time`    datetime                          not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabone` (`operator_id`,`planning_start_time`,`planning_end_time`) values
  (2, '2017-11-15 07:00:00', '2017-11-15 17:00:00'),
  (2, '2017-11-16 07:00:00', '2017-11-16 17:00:00'),
  (2, '2017-11-17 07:00:00', '2017-11-17 17:00:00')
--------------
 
--------------
select * from `tabone`
--------------
 
+----+-------------+---------------------+---------------------+
| id | operator_id | planning_start_time | planning_end_time   |
+----+-------------+---------------------+---------------------+
|  1 |           2 | 2017-11-15 07:00:00 | 2017-11-15 17:00:00 |
|  2 |           2 | 2017-11-16 07:00:00 | 2017-11-16 17:00:00 |
|  3 |           2 | 2017-11-17 07:00:00 | 2017-11-17 17:00:00 |
+----+-------------+---------------------+---------------------+
--------------
DROP TABLE IF EXISTS `tabtwo`
--------------
 
--------------
CREATE TABLE `tabtwo`
( `id`           integer  unsigned  auto_increment not null  primary key,
  `operator_id`  smallint unsigned                 not null,
  `busy_start`   datetime                          not null,
  `busy_end`     datetime                          not null,
  `description`  varchar(255)                      not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabtwo` (`operator_id`,`busy_start`,`busy_end`,`description`) values
  (2, '2017-11-16 08:00:00', '2017-11-16 12:00:00','Fraisage'),
  (2, '2017-11-16 14:00:00', '2017-11-16 16:00:00','Manutention')
--------------
 
--------------
select * from `tabtwo`
--------------
 
+----+-------------+---------------------+---------------------+-------------+
| id | operator_id | busy_start          | busy_end            | description |
+----+-------------+---------------------+---------------------+-------------+
|  1 |           2 | 2017-11-16 08:00:00 | 2017-11-16 12:00:00 | Fraisage    |
|  2 |           2 | 2017-11-16 14:00:00 | 2017-11-16 16:00:00 | Manutention |
+----+-------------+---------------------+---------------------+-------------+
--------------
DROP TABLE IF EXISTS `trav`
--------------
 
--------------
CREATE TABLE `trav`
( `id`                    integer  unsigned  auto_increment not null  primary key,
  `operator_id`           smallint unsigned                 not null,
  `available_start_time`  datetime                          not null,
  `available_end_time`    datetime                          not null,
  `available_duration`    integer unsigned                  not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP PROCEDURE IF EXISTS `remplir`
--------------
 
--------------
CREATE PROCEDURE `remplir` ()
DETERMINISTIC
NO SQL
BEGIN
  DECLARE _operator  smallint      default NULL;
  DECLARE _plandeb   datetime      default NULL;
  DECLARE _planfin   datetime      default NULL;
  DECLARE _busydeb   datetime      default NULL;
  DECLARE _busyfin   datetime      default NULL;
 
  DECLARE _precope   smallint      default NULL;
  DECLARE _precfin   datetime      default NULL;
 
  DECLARE _avaideb   datetime      default NULL;
  DECLARE _avaifin   datetime      default NULL;
 
  DECLARE _duration  integer       default NULL;
 
  DECLARE _rupt1     varchar(255)  default NULL;
  DECLARE _rupt2     tinyint       default NULL;
 
  DECLARE _fin       INTEGER      DEFAULT 1;
  DECLARE _tab       CURSOR  FOR SELECT  t1.operator_id, t1.planning_start_time, t1.planning_end_time, t2.busy_start, t2.busy_end
                                   from  `tabone` as t1
                        left outer join  `tabtwo` as t2
                                     on  t2.operator_id  = t1.operator_id
                                    and  t2.busy_start  >= t1.planning_start_time
                                    and  t2.busy_end    <= t1.planning_end_time
                               order by  t1.operator_id, t1.planning_start_time, t2.busy_start;
 
  DECLARE CONTINUE  HANDLER FOR NOT FOUND SET _fin = 0;
 
  OPEN  _tab;
  FETCH _tab INTO _operator, _plandeb, _planfin, _busydeb, _busyfin;
 
  SET _rupt1   = concat(_operator,date(_plandeb));
  SET _rupt2   = 1;
 
  SET _precope = _operator;
  SET _avaideb = _plandeb;
  SET _precfin = _planfin;
 
  WHILE (_fin)
  DO
    SELECT _operator, _plandeb, _planfin, _busydeb, _busyfin;
 
    IF _busydeb is null THEN
       SET _duration = time_to_sec(timediff(_planfin,_plandeb)) / 60;
       INSERT INTO `trav` (`operator_id`,`available_start_time`,`available_end_time`,`available_duration`)  VALUES (_operator, _plandeb, _planfin, _duration);
       SET _rupt2 = 0;
    ELSE
       SET _avaifin = _busydeb;
       SET _duration = time_to_sec(timediff(_avaifin,_avaideb)) / 60;
       INSERT INTO `trav` (`operator_id`,`available_start_time`,`available_end_time`,`available_duration`)  VALUES (_operator, _avaideb, _avaifin, _duration);
       SET _avaideb = _busyfin;
    END IF;
 
    FETCH _tab INTO _operator, _plandeb, _planfin, _busydeb, _busyfin;
 
    IF _rupt1 <> concat(_operator,date(_plandeb)) THEN
       IF _rupt2 THEN
           SET _duration = time_to_sec(timediff(_precfin,_avaideb)) / 60;
           INSERT INTO `trav` (`operator_id`,`available_start_time`,`available_end_time`,`available_duration`)  values (_precope, _avaideb, _precfin, _duration);
       END IF;
 
       SET _precope = _operator;
       SET _avaideb = _plandeb;
       SET _precfin = _planfin;
    END IF;
 
    SET _rupt1 = concat(_operator,date(_plandeb));
    SET _rupt2 = 1;
  END WHILE;
 
  CLOSE _tab;
END
--------------
 
--------------
call `remplir` ()
--------------
 
+-----------+---------------------+---------------------+----------+----------+
| _operator | _plandeb            | _planfin            | _busydeb | _busyfin |
+-----------+---------------------+---------------------+----------+----------+
|         2 | 2017-11-15 07:00:00 | 2017-11-15 17:00:00 | NULL     | NULL     |
+-----------+---------------------+---------------------+----------+----------+
+-----------+---------------------+---------------------+---------------------+---------------------+
| _operator | _plandeb            | _planfin            | _busydeb            | _busyfin            |
+-----------+---------------------+---------------------+---------------------+---------------------+
|         2 | 2017-11-16 07:00:00 | 2017-11-16 17:00:00 | 2017-11-16 08:00:00 | 2017-11-16 12:00:00 |
+-----------+---------------------+---------------------+---------------------+---------------------+
+-----------+---------------------+---------------------+---------------------+---------------------+
| _operator | _plandeb            | _planfin            | _busydeb            | _busyfin            |
+-----------+---------------------+---------------------+---------------------+---------------------+
|         2 | 2017-11-16 07:00:00 | 2017-11-16 17:00:00 | 2017-11-16 14:00:00 | 2017-11-16 16:00:00 |
+-----------+---------------------+---------------------+---------------------+---------------------+
+-----------+---------------------+---------------------+----------+----------+
| _operator | _plandeb            | _planfin            | _busydeb | _busyfin |
+-----------+---------------------+---------------------+----------+----------+
|         2 | 2017-11-17 07:00:00 | 2017-11-17 17:00:00 | NULL     | NULL     |
+-----------+---------------------+---------------------+----------+----------+
--------------
select           t1.operator_id,
                 t1.planning_start_time,
                 t1.planning_end_time,
                 t2.busy_start,
                 t2.busy_end
 
           from  `tabone` as t1
 
left outer join  `tabtwo` as t2
             on  t2.operator_id  = t1.operator_id
            and  t2.busy_start  >= t1.planning_start_time
            and  t2.busy_end    <= t1.planning_end_time
 
       order by  t1.operator_id, t1.planning_start_time, t2.busy_start
--------------
 
+-------------+---------------------+---------------------+---------------------+---------------------+
| operator_id | planning_start_time | planning_end_time   | busy_start          | busy_end            |
+-------------+---------------------+---------------------+---------------------+---------------------+
|           2 | 2017-11-15 07:00:00 | 2017-11-15 17:00:00 | NULL                | NULL                |
|           2 | 2017-11-16 07:00:00 | 2017-11-16 17:00:00 | 2017-11-16 08:00:00 | 2017-11-16 12:00:00 |
|           2 | 2017-11-16 07:00:00 | 2017-11-16 17:00:00 | 2017-11-16 14:00:00 | 2017-11-16 16:00:00 |
|           2 | 2017-11-17 07:00:00 | 2017-11-17 17:00:00 | NULL                | NULL                |
+-------------+---------------------+---------------------+---------------------+---------------------+
--------------
select * from `trav`
--------------
 
+----+-------------+----------------------+---------------------+--------------------+
| id | operator_id | available_start_time | available_end_time  | available_duration |
+----+-------------+----------------------+---------------------+--------------------+
|  1 |           2 | 2017-11-15 07:00:00  | 2017-11-15 17:00:00 |                600 |
|  2 |           2 | 2017-11-16 07:00:00  | 2017-11-16 08:00:00 |                 60 |
|  3 |           2 | 2017-11-16 12:00:00  | 2017-11-16 14:00:00 |                120 |
|  4 |           2 | 2017-11-16 16:00:00  | 2017-11-16 17:00:00 |                 60 |
|  5 |           2 | 2017-11-17 07:00:00  | 2017-11-17 17:00:00 |                600 |
+----+-------------+----------------------+---------------------+--------------------+
--------------
select  *
  from  `trav`
 where  available_start_time >= '2017-11-16 00:00:00'
   and  available_end_time   <  '2017-11-17 00:00:00'
   and  available_duration   >= 60
--------------
 
+----+-------------+----------------------+---------------------+--------------------+
| id | operator_id | available_start_time | available_end_time  | available_duration |
+----+-------------+----------------------+---------------------+--------------------+
|  2 |           2 | 2017-11-16 07:00:00  | 2017-11-16 08:00:00 |                 60 |
|  3 |           2 | 2017-11-16 12:00:00  | 2017-11-16 14:00:00 |                120 |
|  4 |           2 | 2017-11-16 16:00:00  | 2017-11-16 17:00:00 |                 60 |
+----+-------------+----------------------+---------------------+--------------------+
--------------
select  *
    from  `trav` as t1
   where  t1.id = (  select  min(id)
                 from  `trav` as t2
                where  t2.available_start_time >= '2017-11-16 00:00:00'
                  and  t2.available_end_time   <  '2017-11-17 00:00:00'
                  and  t2.available_duration   >= 60
                  and  t2.operator_id           = t1.operator_id
            )
--------------
 
+----+-------------+----------------------+---------------------+--------------------+
| id | operator_id | available_start_time | available_end_time  | available_duration |
+----+-------------+----------------------+---------------------+--------------------+
|  2 |           2 | 2017-11-16 07:00:00  | 2017-11-16 08:00:00 |                 60 |
+----+-------------+----------------------+---------------------+--------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

**nietsabes** · 30/10/2017, 15h48

Un grand

, c'est top!

Eclater des lignes en fonction de la valeur de colonnes

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager