Nombre d'entrées du 15 au 15 du mois.

**dllaurent** · 31/05/2018, 15h00

Bonjour, Tout d'abord je tiens à dire que ce forum m'a déjà été d'une aide précieuse dans mon projet. Malheureusement, je me trouve actuellement devant un problème qui me parait irréalisable.

en quelque mots, je suis actuellement en train de développer une interface web permettant à ma compagne d'enregistrer et de calculer ses heures de travail .

mon problème est que je désire pouvoir mettre un place une requête php permettant d'afficher le nombre d'entrée du 16 d'un mois au 15 du mois suivant et ce à chaque mois.

exemple:

Période	nb jours
2018-02-16 -> 2018-03-15	10
2018-03-16 -> 2018-04-15	12

J'aimerais que la période du 16 du mois au 15 du mois suivant ne soit pas entrée manuellement.

Merci d'avance de votre aide et bonne journée

**Sebwar** · 31/05/2018, 15h23

hello,

ou est ce que tu bloques ? qu'as tu déjà essayé ?

**dllaurent** · 31/05/2018, 16h29

salut, merci de ton intérêt.

Je peux déjà afficher le nombre de jours travaillé sur un mois, mon problème est que je n'arrive pas à afficher le nombres de jour travaillé du 16 d'un mois au 15 du mois suivant

ma table en question est composée comme suit:

date	debut	fin	total
2018-03-01	11:00:00	22:00:00	11:00:00
2018-03-02	11:00:00	22:00:00	11:00:00

il faudrait que la requête calcule le nombre d'entrée tout les mois entre le 16 du mois et le 15 du mois suivant

**dllaurent** · 31/05/2018, 16h40

J'ai trouvé un début de piste en rentrant manuellement la date d'intervalle.

Code SQL :

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

SELECT COUNT(*) FROM `wdaphne`  where Date BETWEEN "2018-02-16" AND "2018-03-15"

mon problème est que j'aimerais ne pas devoir entrer les intervalles manuellement, je voudrais pouvoir créer un requête qui serait capable de faire la même chose mais pour tout les mois.

merci d'avance

**CinePhil** · 31/05/2018, 19h17

Une idée consisterait à créer une table des mois décalés.
tr_mois (mois_id, mois_date_debut, mois_date_fin, mois_nom)

Exemple de données :
1, '2018-01-16', '2018-02-15', '2018-1'
2, '2018-02-16', '2018-03-15', '2018-2'
...

Dès lors, on peut tenter une jointure un peu particulière sur cette table :

Code SQL :

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

1
2
3
4
SELECT m.mois_nom, COUNT(*) 
FROM wdaphne w
INNER JOIN tr_mois m ON w.`date` BETWEEN m.mois_date_debut AND m.mois_date_fin
GROUP BY m.mois_nom

**dllaurent** · 31/05/2018, 23h48

merci beaucoup de cette réponse, qui me fais faire un grand bon en avant vers la solution.

votre requête marche parfaitement directement en sql dans phpmyadmin malheureusement elle ne me renvoie rien en php. Auriez vous une idée du pourquoi?

peut être cela viens t'il du moyen de récupérer la valeur?

Mon code (je me suis permis d'enlever les alias pour plus de clarté.):

Code :

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

1
2
3
4
5
6
7
8
9
10
 
 
$req = "SELECT tr_mois.mois_nom, COUNT(*) FROM wdaphne INNER JOIN tr_mois ON wdaphne.date BETWEEN tr_mois.mois_date_debut AND tr_mois.mois_date_fin GROUP BY tr_mois.mois_nom ORDER BY Month(tr_mois.mois_nom)";
$res = $conn->query($req);				
while ($data = mysqli_fetch_array($res))
{
 
           echo "<tr><td>".$data['tr_mois.mois_nom']."</td>";
           // j'ai essayé avec nb, mois_nom aussi
}

Merci de l’intérêt porté à mon problème en espérant avoir été assez clair.

**Artemus24** · 01/06/2018, 16h01

Salut dllaurent.

Votre date, il suffit de retrancher 15 jours !
Ce qui donne pour le '2018-04-16' --> '2018-04-01'.
Ainsi pour une période allant du '2018-04-16' au '2018-05-15', vous aurez la correspondance avec '2018-04-01' au '2018-05-30'
Il suffit de tester le mois d'avril, pour obtenir la période de chevauchement que vous avez.

Ce qui donne l'exemple suivant :

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
--------------
select    date_format((`date` - interval 15 day),'%Y-%m') as periode,
          count(*)                                        as nbre_jour,
          cast(sum(timediff(`fin`,`debut`)) as time)      as nbre_heure
    from  `test`
group by  periode
--------------
 
+---------+-----------+------------+
| periode | nbre_jour | nbre_heure |
+---------+-----------+------------+
| 2018-03 |        15 | 90:00:00   |
| 2018-04 |        30 | 180:00:00  |
| 2018-05 |        31 | 186:00:00  |
| 2018-06 |        15 | 90:00:00   |
+---------+-----------+------------+

@+

**CinePhil** · 01/06/2018, 17h14

Euh...

Code SQL :

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

SELECT '2018-03-01' - INTERVAL 15 DAY

=> 2018-02-14
C'est à dire le "mois" de dllaurent allant du 16 janvier au 15 février.

**Artemus24** · 01/06/2018, 17h23

Salut CinePhil.

Envoyé par dllaurent

mon problème est que je désire pouvoir mettre un place une requête php permettant d'afficher le nombre d'entrée du 16 d'un mois au 15 du mois suivant et ce à chaque mois.

Autrement dit, sa période allant du 16 du mois courant au 15 du mois suivant est à cheval sur deux mois.

Pour éviter ce chevauchement, il suffit d'appliquer un décalage de 15 jours.
Soit le "2018-04-16" depuis le "2018-04-01", ..., et le "2018-05-15" devient le "2018-04-30".

A partir de cela, la période n'est plus à cheval sur deux mois, mais est contenu dans un seul mois, ici le mois d'avril.

Voici sur un exemple simple, la correspondance entre la date et la période :

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
--------------
select    `date`,
          `date` - interval 15 day as periode
    from  `test`
--------------
 
+------------+------------+
| date       | periode    |
+------------+------------+
| 2018-04-01 | 2018-03-17 |
| 2018-04-02 | 2018-03-18 |
| 2018-04-03 | 2018-03-19 |
| 2018-04-04 | 2018-03-20 |
| 2018-04-05 | 2018-03-21 |
| 2018-04-06 | 2018-03-22 |
| 2018-04-07 | 2018-03-23 |
| 2018-04-08 | 2018-03-24 |
| 2018-04-09 | 2018-03-25 |
| 2018-04-10 | 2018-03-26 |
| 2018-04-11 | 2018-03-27 |
| 2018-04-12 | 2018-03-28 |
| 2018-04-13 | 2018-03-29 |
| 2018-04-14 | 2018-03-30 |
| 2018-04-15 | 2018-03-31 |
| 2018-04-16 | 2018-04-01 |
| 2018-04-17 | 2018-04-02 |
| 2018-04-18 | 2018-04-03 |
| 2018-04-19 | 2018-04-04 |
| 2018-04-20 | 2018-04-05 |
| 2018-04-21 | 2018-04-06 |
| 2018-04-22 | 2018-04-07 |
| 2018-04-23 | 2018-04-08 |
| 2018-04-24 | 2018-04-09 |
| 2018-04-25 | 2018-04-10 |
| 2018-04-26 | 2018-04-11 |
| 2018-04-27 | 2018-04-12 |
| 2018-04-28 | 2018-04-13 |
| 2018-04-29 | 2018-04-14 |
| 2018-04-30 | 2018-04-15 |
| 2018-05-01 | 2018-04-16 |
| 2018-05-02 | 2018-04-17 |
| 2018-05-03 | 2018-04-18 |
| 2018-05-04 | 2018-04-19 |
| 2018-05-05 | 2018-04-20 |
| 2018-05-06 | 2018-04-21 |
| 2018-05-07 | 2018-04-22 |
| 2018-05-08 | 2018-04-23 |
| 2018-05-09 | 2018-04-24 |
| 2018-05-10 | 2018-04-25 |
| 2018-05-11 | 2018-04-26 |
| 2018-05-12 | 2018-04-27 |
| 2018-05-13 | 2018-04-28 |
| 2018-05-14 | 2018-04-29 |
| 2018-05-15 | 2018-04-30 |
| 2018-05-16 | 2018-05-01 |
| 2018-05-17 | 2018-05-02 |
| 2018-05-18 | 2018-05-03 |
| 2018-05-19 | 2018-05-04 |
| 2018-05-20 | 2018-05-05 |
| 2018-05-21 | 2018-05-06 |
| 2018-05-22 | 2018-05-07 |
| 2018-05-23 | 2018-05-08 |
| 2018-05-24 | 2018-05-09 |
| 2018-05-25 | 2018-05-10 |
| 2018-05-26 | 2018-05-11 |
| 2018-05-27 | 2018-05-12 |
| 2018-05-28 | 2018-05-13 |
| 2018-05-29 | 2018-05-14 |
| 2018-05-30 | 2018-05-15 |
| 2018-05-31 | 2018-05-16 |
| 2018-06-01 | 2018-05-17 |
| 2018-06-02 | 2018-05-18 |
| 2018-06-03 | 2018-05-19 |
| 2018-06-04 | 2018-05-20 |
| 2018-06-05 | 2018-05-21 |
| 2018-06-06 | 2018-05-22 |
| 2018-06-07 | 2018-05-23 |
| 2018-06-08 | 2018-05-24 |
| 2018-06-09 | 2018-05-25 |
| 2018-06-10 | 2018-05-26 |
| 2018-06-11 | 2018-05-27 |
| 2018-06-12 | 2018-05-28 |
| 2018-06-13 | 2018-05-29 |
| 2018-06-14 | 2018-05-30 |
| 2018-06-15 | 2018-05-31 |
| 2018-06-16 | 2018-06-01 |
| 2018-06-17 | 2018-06-02 |
| 2018-06-18 | 2018-06-03 |
| 2018-06-19 | 2018-06-04 |
| 2018-06-20 | 2018-06-05 |
| 2018-06-21 | 2018-06-06 |
| 2018-06-22 | 2018-06-07 |
| 2018-06-23 | 2018-06-08 |
| 2018-06-24 | 2018-06-09 |
| 2018-06-25 | 2018-06-10 |
| 2018-06-26 | 2018-06-11 |
| 2018-06-27 | 2018-06-12 |
| 2018-06-28 | 2018-06-13 |
| 2018-06-29 | 2018-06-14 |
| 2018-06-30 | 2018-06-15 |
+------------+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Qu'est-ce que vous ne comprenez pas ?

@+

**Dendrite** · 01/06/2018, 22h34

Ben moi, j'ai encore une autre méthode, toute SQL...
Je compte sur vous pour la perfectionner... mais elle me semble fonctionner comme je veux.
J'ai tenu pour acquis que le début et la fin sont toujours le même jour (pas de travail de nuit)...

Code SQL :

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
CREATE TABLE IF NOT EXISTS `w_daphne` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `debut` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fin` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
INSERT INTO `w_daphne` (`id`, `debut`, `fin`) VALUES
(1, '2018-05-14 09:00:00', '2018-05-14 21:00:00'),
(2, '2018-05-15 08:00:00', '2018-05-15 10:00:00'),
(3, '2018-05-16 09:00:00', '2018-05-16 18:00:00'),
(4, '2018-05-17 09:00:00', '2018-05-17 18:00:00'),
(5, '2018-05-18 09:00:00', '2018-05-18 18:00:00'),
(6, '2018-05-19 09:00:00', '2018-05-19 18:00:00'),
(7, '2018-05-20 09:00:00', '2018-05-20 18:00:00'),
(8, '2018-05-21 09:00:00', '2018-05-21 18:00:00'),
(9, '2018-05-22 09:00:00', '2018-05-22 18:00:00'),
(10, '2018-05-23 09:00:00', '2018-05-23 18:00:00'),
(11, '2018-05-24 09:00:00', '2018-05-24 18:00:00'),
(12, '2018-05-25 09:00:00', '2018-05-25 18:00:00'),
(13, '2018-05-26 09:00:00', '2018-05-26 18:00:00'),
(14, '2018-05-27 09:00:00', '2018-05-27 18:00:00'),
(15, '2018-05-28 09:00:00', '2018-05-28 18:00:00'),
(16, '2018-05-29 09:00:00', '2018-05-29 18:00:00'),
(17, '2018-05-30 09:00:00', '2018-05-30 18:00:00'),
(18, '2018-05-31 09:00:00', '2018-05-31 18:00:00'),
(19, '2018-06-01 09:00:00', '2018-06-01 18:00:00'),
(20, '2018-06-02 09:00:00', '2018-06-02 18:00:00'),
(21, '2018-06-03 09:00:00', '2018-06-03 18:00:00'),
(22, '2018-06-04 09:00:00', '2018-06-04 18:00:00'),
(23, '2018-06-05 09:00:00', '2018-06-05 18:00:00'),
(24, '2018-06-06 09:00:00', '2018-06-06 18:00:00'),
(25, '2018-06-07 09:00:00', '2018-06-07 18:00:00'),
(26, '2018-06-08 09:00:00', '2018-06-08 18:00:00'),
(27, '2018-06-09 09:00:00', '2018-06-09 18:00:00'),
(28, '2018-06-10 09:00:00', '2018-06-10 18:00:00'),
(29, '2018-06-11 09:00:00', '2018-06-11 18:00:00'),
(30, '2018-06-12 09:00:00', '2018-06-12 18:00:00'),
(31, '2018-06-13 09:00:00', '2018-06-13 18:00:00'),
(32, '2018-06-14 09:00:00', '2018-06-14 18:00:00'),
(33, '2018-06-15 09:00:00', '2018-06-15 18:00:00'),
(34, '2018-06-16 09:00:00', '2018-06-16 18:00:00'),
(35, '2018-01-01 07:00:00', '2018-01-01 11:30:00'),
(36, '2018-01-15 07:00:00', '2018-01-15 11:00:00');

Code SQL :

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
create or replace view v_w_daphne AS
SELECT 
CASE
	WHEN MONTH(fin)=1 AND DAY(fin) <= 15 THEN YEAR(fin)-1
	ELSE YEAR(fin)
END AS annee,
CASE
	WHEN MONTH(fin)=1 and DAY(fin) <= 15 THEN 12
	WHEN DAY(fin) <= 15 THEN MONTH(fin)-1
	ELSE MONTH(fin)
END AS mois,
sum( TIMEDIFF(fin, debut)) AS total
from w_daphne
group by annee, mois;
 
SELECT * 
from v_w_daphne
order by annee desc, mois desc;

Je la trouve très simple à comprendre, la requête de la vue.

Code :

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

1
2
3
4
5
annee	mois	total	
2018	      6	90000
2018	      5	2790000	
2018       4	140000	
2017	     12	83000

Heu un truc idiot en passant, quelqu'un sait s'il y a un truc dans PHPMyAdmin pour afficher le tableau graphique tel quel ?
Faudra améliorer un peu la mise en forme : décembre 2017 se lit "08H30", avril 2018 se lit "14H", mai 279H (ouaip, pour Daphné, c'est pas 'en mai, fais ce qu'il te plaît'

), juin 09H...

**Artemus24** · 02/06/2018, 09h46

Bonjour dendrite.

Vous êtes une des rares personnes à nous fournir un jeu d'essai.

La dernière requête est celle que je vous propose :

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
--------------
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 `w_daphne`
--------------
 
--------------
CREATE TABLE `w_daphne`
(  `id`     integer unsigned not null auto_increment primary key,
   `debut`  timestamp        not null,
   `fin`    timestamp        not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `w_daphne` (`debut`, `fin`) VALUES
('2018-05-14 09:00:00', '2018-05-14 21:00:00'),
('2018-05-15 08:00:00', '2018-05-15 10:00:00'),
('2018-05-16 09:00:00', '2018-05-16 18:00:00'),
('2018-05-17 09:00:00', '2018-05-17 18:00:00'),
('2018-05-18 09:00:00', '2018-05-18 18:00:00'),
('2018-05-19 09:00:00', '2018-05-19 18:00:00'),
('2018-05-20 09:00:00', '2018-05-20 18:00:00'),
('2018-05-21 09:00:00', '2018-05-21 18:00:00'),
('2018-05-22 09:00:00', '2018-05-22 18:00:00'),
('2018-05-23 09:00:00', '2018-05-23 18:00:00'),
('2018-05-24 09:00:00', '2018-05-24 18:00:00'),
('2018-05-25 09:00:00', '2018-05-25 18:00:00'),
('2018-05-26 09:00:00', '2018-05-26 18:00:00'),
('2018-05-27 09:00:00', '2018-05-27 18:00:00'),
('2018-05-28 09:00:00', '2018-05-28 18:00:00'),
('2018-05-29 09:00:00', '2018-05-29 18:00:00'),
('2018-05-30 09:00:00', '2018-05-30 18:00:00'),
('2018-05-31 09:00:00', '2018-05-31 18:00:00'),
('2018-06-01 09:00:00', '2018-06-01 18:00:00'),
('2018-06-02 09:00:00', '2018-06-02 18:00:00'),
('2018-06-03 09:00:00', '2018-06-03 18:00:00'),
('2018-06-04 09:00:00', '2018-06-04 18:00:00'),
('2018-06-05 09:00:00', '2018-06-05 18:00:00'),
('2018-06-06 09:00:00', '2018-06-06 18:00:00'),
('2018-06-07 09:00:00', '2018-06-07 18:00:00'),
('2018-06-08 09:00:00', '2018-06-08 18:00:00'),
('2018-06-09 09:00:00', '2018-06-09 18:00:00'),
('2018-06-10 09:00:00', '2018-06-10 18:00:00'),
('2018-06-11 09:00:00', '2018-06-11 18:00:00'),
('2018-06-12 09:00:00', '2018-06-12 18:00:00'),
('2018-06-13 09:00:00', '2018-06-13 18:00:00'),
('2018-06-14 09:00:00', '2018-06-14 18:00:00'),
('2018-06-15 09:00:00', '2018-06-15 18:00:00'),
('2018-06-16 09:00:00', '2018-06-16 18:00:00'),
('2018-01-01 07:00:00', '2018-01-01 11:30:00'),
('2018-01-15 07:00:00', '2018-01-15 11:00:00')
--------------
 
--------------
select * from `w_daphne`
--------------
 
+----+---------------------+---------------------+
| id | debut               | fin                 |
+----+---------------------+---------------------+
|  1 | 2018-05-14 09:00:00 | 2018-05-14 21:00:00 |
|  2 | 2018-05-15 08:00:00 | 2018-05-15 10:00:00 |
|  3 | 2018-05-16 09:00:00 | 2018-05-16 18:00:00 |
|  4 | 2018-05-17 09:00:00 | 2018-05-17 18:00:00 |
|  5 | 2018-05-18 09:00:00 | 2018-05-18 18:00:00 |
|  6 | 2018-05-19 09:00:00 | 2018-05-19 18:00:00 |
|  7 | 2018-05-20 09:00:00 | 2018-05-20 18:00:00 |
|  8 | 2018-05-21 09:00:00 | 2018-05-21 18:00:00 |
|  9 | 2018-05-22 09:00:00 | 2018-05-22 18:00:00 |
| 10 | 2018-05-23 09:00:00 | 2018-05-23 18:00:00 |
| 11 | 2018-05-24 09:00:00 | 2018-05-24 18:00:00 |
| 12 | 2018-05-25 09:00:00 | 2018-05-25 18:00:00 |
| 13 | 2018-05-26 09:00:00 | 2018-05-26 18:00:00 |
| 14 | 2018-05-27 09:00:00 | 2018-05-27 18:00:00 |
| 15 | 2018-05-28 09:00:00 | 2018-05-28 18:00:00 |
| 16 | 2018-05-29 09:00:00 | 2018-05-29 18:00:00 |
| 17 | 2018-05-30 09:00:00 | 2018-05-30 18:00:00 |
| 18 | 2018-05-31 09:00:00 | 2018-05-31 18:00:00 |
| 19 | 2018-06-01 09:00:00 | 2018-06-01 18:00:00 |
| 20 | 2018-06-02 09:00:00 | 2018-06-02 18:00:00 |
| 21 | 2018-06-03 09:00:00 | 2018-06-03 18:00:00 |
| 22 | 2018-06-04 09:00:00 | 2018-06-04 18:00:00 |
| 23 | 2018-06-05 09:00:00 | 2018-06-05 18:00:00 |
| 24 | 2018-06-06 09:00:00 | 2018-06-06 18:00:00 |
| 25 | 2018-06-07 09:00:00 | 2018-06-07 18:00:00 |
| 26 | 2018-06-08 09:00:00 | 2018-06-08 18:00:00 |
| 27 | 2018-06-09 09:00:00 | 2018-06-09 18:00:00 |
| 28 | 2018-06-10 09:00:00 | 2018-06-10 18:00:00 |
| 29 | 2018-06-11 09:00:00 | 2018-06-11 18:00:00 |
| 30 | 2018-06-12 09:00:00 | 2018-06-12 18:00:00 |
| 31 | 2018-06-13 09:00:00 | 2018-06-13 18:00:00 |
| 32 | 2018-06-14 09:00:00 | 2018-06-14 18:00:00 |
| 33 | 2018-06-15 09:00:00 | 2018-06-15 18:00:00 |
| 34 | 2018-06-16 09:00:00 | 2018-06-16 18:00:00 |
| 35 | 2018-01-01 07:00:00 | 2018-01-01 11:30:00 |
| 36 | 2018-01-15 07:00:00 | 2018-01-15 11:00:00 |
+----+---------------------+---------------------+
--------------
create or replace view v_w_daphne AS
SELECT
CASE
        WHEN MONTH(fin)=1 AND DAY(fin) <= 15 THEN YEAR(fin)-1
        ELSE YEAR(fin)
END AS annee,
 
CASE
        WHEN MONTH(fin)=1 and DAY(fin) <= 15 THEN 12
        WHEN DAY(fin) <= 15 THEN MONTH(fin)-1
        ELSE MONTH(fin)
END AS mois,
 
sum( TIMEDIFF(fin, debut)) AS total
from w_daphne
group by annee, mois
--------------
 
--------------
SELECT *
from v_w_daphne
order by annee desc, mois desc
--------------
 
+-------+------+---------+
| annee | mois | total   |
+-------+------+---------+
|  2018 |    6 |   90000 |
|  2018 |    5 | 2790000 |
|  2018 |    4 |  140000 |
|  2017 |   12 |   83000 |
+-------+------+---------+
--------------
select    date_format(`fin` - interval 15 day,'%Y-%m') as periode,
          cast(sum(timediff(`fin`,`debut`)) as time)   as total
    from  w_daphne
group by  periode
order by  periode desc
--------------
 
+---------+-----------+
| periode | total     |
+---------+-----------+
| 2018-06 | 09:00:00  |
| 2018-05 | 279:00:00 |
| 2018-04 | 14:00:00  |
| 2017-12 | 08:30:00  |
+---------+-----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

**Dendrite** · 02/06/2018, 10h17

Artemus, je dis chapeau !

edit : Corrigé pour tenir compte des remarques d'Artemus dans le message qui suit.

Ta solution est condensée et fonctionne à merveille.
Il ne reste plus à dllaurent qu'à revoir sa table de départ pour ôter le champ total.
@laurent : en effet, tu posais un calcul sur une table, ton champ total. C'est tout à fait inutile : les calculs sur les données déjà saisies sont le fruit de requêtes ou de vues (ce qui revient au même). Mais pas une saisie "figée" supplémentaire, c'est de la redondance et source d'incohérences possibles. Côté modélisation, il faut proscrire toute redondance. Côté algo, il ne faut pas faire faire à PHP ce que SQL sait parfaitement faire.

Quand on peut dire plus avec moins, c'est la bonne solution SQL.

Et si demain tu prêtes ton appli à Cécile, jeune interne en médecine qui aligne les gardes et peut travailler 26H d'affilée... ça serait bien que ça fonctionne aussi...
En l'espèce, tu mettrais dans ton fichier config.php la date de bascule du mois : genre 1 pour Cécile, 16 pour Daphné et le tour est joué !

Code :

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

1
2
3
4
define('BASCULE_MOIS',16);//pour Daphné, 1 pour les autres
if(defined(BASCULE_MOIS)){
   define('INTERVAL_SQL',BASCULE_MOIS--);//15 pour Daphné, 0 pour les autres
}

Questions aux experts SQL ici présents :
1) timestamp ou datetime, les champs debut et fin ? je ne sais jamais !
2) pendant que je vous tiens : faut-il poser des indexes sur les champs debut et fin sur lesquels on va opérer tous les calculs ? J'aurais tendance à répondre oui, moi...

@Artemus
2) Je vais poser et reposer ton travail sur l'ouvrage, il est si condensé que je ne le pige pas.
Comment cette élégante requête si courte peut-elle tout gérer ? Je vais relire tes explications ci-dessus.

Code SQL :

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

1
2
3
4
5
select    date_format(`date` - interval 15 day,'%Y-%m') as periode,
          cast(sum(timediff(`fin`,`debut`)) as time)   as total
    from  w_daphne
group by  periode
order by  periode desc;

@dllaurent

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
 
include('config.php');
if(defined(INTERVAL_SQL)){
	$sql="select    date_format(`date` - interval ? day,'%Y-%m') as periode,
			  cast(sum(timediff(`fin`,`debut`)) as time)   as total
		from  w_daphne
	group by  periode
	order by  periode desc";
	$sth = $db->prepare($sql);
	$sth->execute(array(INTERVAL_SQL));
	$data = $sth->fetchAll();
	echo '<pre>';
	print_r($data);
	echo '</pre>';
}

3) Ta requête gère-t-elle sans problème un cas limite du genre : Daphné a travaillé de nuit du 15 au 16 du mois, de 23:45:00 à 03H00 ? Ce qui fait 15 minutes côté mois précédent, et 03H côté mois courant ? Je vais ajouter ça dans le jeu de données.

J'aime bien jouer avec vous !

**Artemus24** · 02/06/2018, 10h47

Salut dendrite.

Envoyé par Dendrite

Artemis, je dis chapeau !

Je ne suis pas la déesse de la chasse mais Mr. Artemus Gordon, agent secret au service du président Ulysse Grant !

Envoyé par Dendrite

timestamp ou datetime, les champs debut et fin ? je ne sais jamais !

Le timestamp gère le format suivant : "YYYY-MM-DD HH:MM:SS.CCCCCC".
Le datetime gère le format suivant : "YYYY-MM-DD HH:MM:SS".
Si vous avez besoin que de la date "YYYY-MM-DD" et de l'heure "HH:MM:SS", le type datetime suffit grandement.

Envoyé par Dendrite

Comment cette élégante requête si courte peut-elle tout gérer ? Je vais relire tes explications ci-dessus.

La colonne "fin" est dans le type "timestamp".
Donc au niveau stockage, il s'agit d'un nombre au millionième de seconde et qui débute le 1er janvier 1970.

Code :

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

`fin` - interval 15 day

Ceci retranche 15 jours à la date "fin". De ce fait, il tient compte des mois et des années.

Code :

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

date_format(...,'%Y-%m') as periode,

Date_format() est une fonction qui met en forme la date d'une façon lisible, ici, l'année et le mois.

Envoyé par Dendrite

3) Ta requête gère-t-elle sans problème un cas limite du genre : Daphné a travaillé de nuit du 15 au 16 du mois, de 23:45:00 à 03H00 ? Ce qui fait 15 minutes côté mois précédent, et 03H côté mois courant ?

Non, car votre modélisation n'est pas bonne.
Vous devez créer une ligne par jour. Pour la nouvelle colonne "fin", la fin de la journée sera "24:00:00" et non "00:00:00".
J'ai ajoué deux lignes en fin des insertions. Ce qui donne :

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
--------------
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 `w_daphne`
--------------
 
--------------
CREATE TABLE `w_daphne`
(  `id`     integer unsigned not null auto_increment primary key,
   `date`   date             not null,
   `debut`  time             not null,
   `fin`    time             not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `w_daphne` (`date`,`debut`, `fin`) VALUES
('2018-05-14', '09:00:00', '21:00:00'),
('2018-05-15', '08:00:00', '10:00:00'),
('2018-05-16', '09:00:00', '18:00:00'),
('2018-05-17', '09:00:00', '18:00:00'),
('2018-05-18', '09:00:00', '18:00:00'),
('2018-05-19', '09:00:00', '18:00:00'),
('2018-05-20', '09:00:00', '18:00:00'),
('2018-05-21', '09:00:00', '18:00:00'),
('2018-05-22', '09:00:00', '18:00:00'),
('2018-05-23', '09:00:00', '18:00:00'),
('2018-05-24', '09:00:00', '18:00:00'),
('2018-05-25', '09:00:00', '18:00:00'),
('2018-05-26', '09:00:00', '18:00:00'),
('2018-05-27', '09:00:00', '18:00:00'),
('2018-05-28', '09:00:00', '18:00:00'),
('2018-05-29', '09:00:00', '18:00:00'),
('2018-05-30', '09:00:00', '18:00:00'),
('2018-05-31', '09:00:00', '18:00:00'),
('2018-06-01', '09:00:00', '18:00:00'),
('2018-06-02', '09:00:00', '18:00:00'),
('2018-06-03', '09:00:00', '18:00:00'),
('2018-06-04', '09:00:00', '18:00:00'),
('2018-06-05', '09:00:00', '18:00:00'),
('2018-06-06', '09:00:00', '18:00:00'),
('2018-06-07', '09:00:00', '18:00:00'),
('2018-06-08', '09:00:00', '18:00:00'),
('2018-06-09', '09:00:00', '18:00:00'),
('2018-06-10', '09:00:00', '18:00:00'),
('2018-06-11', '09:00:00', '18:00:00'),
('2018-06-12', '09:00:00', '18:00:00'),
('2018-06-13', '09:00:00', '18:00:00'),
('2018-06-14', '09:00:00', '18:00:00'),
('2018-06-15', '09:00:00', '18:00:00'),
('2018-06-16', '09:00:00', '18:00:00'),
('2018-01-01', '07:00:00', '11:30:00'),
('2018-01-15', '07:00:00', '11:00:00'),
 
('2018-03-15', '18:00:00', '24:00:00'),
('2018-03-16', '00:00:00', '04:00:00')
--------------
 
--------------
select * from `w_daphne`
--------------
 
+----+------------+----------+----------+
| id | date       | debut    | fin      |
+----+------------+----------+----------+
|  1 | 2018-05-14 | 09:00:00 | 21:00:00 |
|  2 | 2018-05-15 | 08:00:00 | 10:00:00 |
|  3 | 2018-05-16 | 09:00:00 | 18:00:00 |
|  4 | 2018-05-17 | 09:00:00 | 18:00:00 |
|  5 | 2018-05-18 | 09:00:00 | 18:00:00 |
|  6 | 2018-05-19 | 09:00:00 | 18:00:00 |
|  7 | 2018-05-20 | 09:00:00 | 18:00:00 |
|  8 | 2018-05-21 | 09:00:00 | 18:00:00 |
|  9 | 2018-05-22 | 09:00:00 | 18:00:00 |
| 10 | 2018-05-23 | 09:00:00 | 18:00:00 |
| 11 | 2018-05-24 | 09:00:00 | 18:00:00 |
| 12 | 2018-05-25 | 09:00:00 | 18:00:00 |
| 13 | 2018-05-26 | 09:00:00 | 18:00:00 |
| 14 | 2018-05-27 | 09:00:00 | 18:00:00 |
| 15 | 2018-05-28 | 09:00:00 | 18:00:00 |
| 16 | 2018-05-29 | 09:00:00 | 18:00:00 |
| 17 | 2018-05-30 | 09:00:00 | 18:00:00 |
| 18 | 2018-05-31 | 09:00:00 | 18:00:00 |
| 19 | 2018-06-01 | 09:00:00 | 18:00:00 |
| 20 | 2018-06-02 | 09:00:00 | 18:00:00 |
| 21 | 2018-06-03 | 09:00:00 | 18:00:00 |
| 22 | 2018-06-04 | 09:00:00 | 18:00:00 |
| 23 | 2018-06-05 | 09:00:00 | 18:00:00 |
| 24 | 2018-06-06 | 09:00:00 | 18:00:00 |
| 25 | 2018-06-07 | 09:00:00 | 18:00:00 |
| 26 | 2018-06-08 | 09:00:00 | 18:00:00 |
| 27 | 2018-06-09 | 09:00:00 | 18:00:00 |
| 28 | 2018-06-10 | 09:00:00 | 18:00:00 |
| 29 | 2018-06-11 | 09:00:00 | 18:00:00 |
| 30 | 2018-06-12 | 09:00:00 | 18:00:00 |
| 31 | 2018-06-13 | 09:00:00 | 18:00:00 |
| 32 | 2018-06-14 | 09:00:00 | 18:00:00 |
| 33 | 2018-06-15 | 09:00:00 | 18:00:00 |
| 34 | 2018-06-16 | 09:00:00 | 18:00:00 |
| 35 | 2018-01-01 | 07:00:00 | 11:30:00 |
| 36 | 2018-01-15 | 07:00:00 | 11:00:00 |
| 37 | 2018-03-15 | 18:00:00 | 24:00:00 |
| 38 | 2018-03-16 | 00:00:00 | 04:00:00 |
+----+------------+----------+----------+
--------------
select    date_format(`date` - interval 15 day,'%Y-%m') as periode,
          cast(sum(timediff(`fin`,`debut`)) as time)   as total
    from  w_daphne
group by  periode
order by  periode desc
--------------
 
+---------+-----------+
| periode | total     |
+---------+-----------+
| 2018-06 | 09:00:00  |
| 2018-05 | 279:00:00 |
| 2018-04 | 14:00:00  |
| 2018-03 | 04:00:00  |
| 2018-02 | 06:00:00  |
| 2017-12 | 08:30:00  |
+---------+-----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

**Dendrite** · 02/06/2018, 11h13

Merci infiniment ArtemUs ! Je teste tout ça en base de ce pas.

edit : c'est proche de la perfection !

Invité · 02/06/2018, 11h36

Bonjour,
Je ne fais que passer...

...Vous devez créer une ligne par jour. ...

1- Dans le cas (classique) où la personne bosse de 9h à 12h et de 13h30 à 17h30 (par exemple) :
On doit bien créer 2 lignes dans la bdd, pour le même jour, non ?

2- Idem si on chevauche 2 jours (ex. de 20h à 4h) :
On doit aussi créer 2 lignes : 20h-24h (jour1) et 0h-4h (jour2)

A moins qu'on impose que le jour à considérer est celui du début de prise de poste (jour1) ?
Auquel cas, le calcul (4 - 20) devient faux (et nécessite un ajustement)

3- D'autre part la période fevrier-mars aura + ou - 1 jour (année bissextile ou non)

Me trompe-je ?

N.B. la pauvre Daphné à des horaires de fou...
Mais que font les syndicats ??

**Dendrite** · 02/06/2018, 11h51

Coucou Jreaux,

1- Dans le cas (classique) où la personne bosse de 9h à 12h et de 13h30 à 17h30

Je viens de tester. Parfaitement géré par la requête d'Artemus.
Ce qu'il voulait dire, c'est qu'on n'y coupe pas de saisir 2 jours quand il y a bascule du genre : de 22H à 02H du jour suivant.
En d'autres termes :
"Vous devez créer AU MOINS une ligne par jour différent."
Mais bien sûr qu'on peut saisir plusieurs périodes le même jour. (personnel de service).

Question subsidiaire (je suis intarissable de questions dès lors qu'on parle SQL)
Serait-il possible de poser une clé d'unicité pour que les périodes ne puissent pas se recouper ? J'imagine que non ?

edit : je vois que le dalmatien pose une question sur le même terrain en 2...

PS : je compte bien utiliser tout ça très concrètement au boulot ! Merci pour ce merveilleux sujet dllaurent (et Daphné !). Et merci pour mes collègues contractuels à qui je pourrai fournir un petit jouet bien pratique !

**Dendrite** · 02/06/2018, 12h13

Une piste (en angliche) par ici :

https://ask.sqlservercentral.com/que...te-ranges.html

si je résume, il vaut mieux chercher du côté des triggers (petite fonction qui se déclenche à un événement sur la table - ici n'importe quel événement en écriture) que du côté des check constraints (clé de contrainte).

du coup, je vais fouiller du côté des triggers.

Invité · 02/06/2018, 12h19

Envoyé par Dendrite

Et merci pour mes collègues contractuels à qui je pourrai fournir un petit jouet bien pratique !

LOL...
J'avais lu : "...un petit fouet bien pratique..."

50 nuances de Dendrite........

**Artemus24** · 02/06/2018, 13h19

Salut à tous.

Envoyé par jreaux62

On doit bien créer 2 lignes dans la bdd, pour le même jour, non ?

Je me suis mal exprimé, et vous avez raison !
Entre l'heure de début et l'heure de fin, il ne doit pas y avoir de trou.

En fait, je voulais dire de ne pas mettre dans une ligne une date de début et une date de fin à cheval sur deux jours.

Envoyé par jreaux62

On doit aussi créer 2 lignes : 20h-24h (jour1) et 0h-4h (jour2)

Oui, vous avez encore raison. C'est ce que j'ai fait dans mon exemple (le dernier).

Envoyé par jreaux62

3- D'autre part la période fevrier-mars aura + ou - 1 jour (année bissextile ou non)

Cela ne pose aucun problème car dans ce cas là, le mois de février aura 29 jours.

Envoyé par jreaux62

Me trompe-je ?

Vos remarques sont pertinentes et cela mérite un +1.

Envoyé par Dendrite

"Vous devez créer AU MOINS une ligne par jour différent."

Je dirais plutôt une ligne par intervalle de temps, ce qui donne :

Code :

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

1
2
3
4
5
6
7
8
+--------------+--------------+--------------+
| date du jour | heure  début |  heure  fin  |
+--------------+--------------+--------------+
|  2018-05-31  |   09:00:00   |   12:00:00   |
|  2018-05-31  |   13:30:00   |   17:00:00   |
|  2018-05-31  |   22:30:00   |   24:00:00   |
|  2018-06-01  |   00:00:00   |   04:00:00   |
+--------------+--------------+--------------+

Envoyé par Dendrite

Serait-il possible de poser une clé d'unicité pour que les périodes ne puissent pas se recouper ? J'imagine que non ?

Je suppose que vous pensez à un index pour déterminer des périodes qui se chevauchent. A moins de me tromper, je pense que c'est non.

Par contre, vous pouvez faire une requête pour tester l'insertion. Par exemple :

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
--------------
insert into w_daphne (`date`,`debut`,`fin`)
  select  t1.date,
          t1.debut,
          t1.fin
    from  (  select  '2018-03-15' as date,
                     '19:00:00'   as debut,
                     '21:00:00'   as fin
               from  dual
          ) as t1
 
where not exists (  select  1
                      from  w_daphne as t2
                     where  t2.date   = t1.date
                       and  t2.fin   >= t1.debut
                       and  t2.debut <= t1.fin
                 )
--------------
 
--------------
select  *
  from  `w_daphne`
 where  `date` between '2018-03-01' and '2018-03-31'
--------------
 
+----+------------+----------+----------+
| id | date       | debut    | fin      |
+----+------------+----------+----------+
| 37 | 2018-03-15 | 18:00:00 | 24:00:00 |
| 38 | 2018-03-16 | 00:00:00 | 04:00:00 |
+----+------------+----------+----------+
 
Appuyez sur une touche pour continuer...

Dans cet exemple, j'utilise la période "2018-03-15 19:00:00 21:00:00" recouvre une période déjà existante "2018-03-15 18:00:00 24:00:00"

Autre exemple :

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
--------------
insert into w_daphne (`date`,`debut`,`fin`)
  select  t1.date,
          t1.debut,
          t1.fin
    from  (  select  '2018-03-12' as date,
                     '19:00:00'   as debut,
                     '21:00:00'   as fin
               from  dual
          ) as t1
 
where not exists (  select  1
                      from  w_daphne as t2
                     where  t2.date   = t1.date
                       and  t2.fin   >= t1.debut
                       and  t2.debut <= t1.fin
                 )
--------------
 
--------------
select  *
  from  `w_daphne`
 where  `date` between '2018-03-01' and '2018-03-31'
--------------
 
+----+------------+----------+----------+
| id | date       | debut    | fin      |
+----+------------+----------+----------+
| 37 | 2018-03-15 | 18:00:00 | 24:00:00 |
| 38 | 2018-03-16 | 00:00:00 | 04:00:00 |
| 39 | 2018-03-12 | 19:00:00 | 21:00:00 |
+----+------------+----------+----------+
 
Appuyez sur une touche pour continuer...

Ici, on peut faire l'insertion car pas de résultat en retour de la requête.

@+

**Dendrite** · 02/06/2018, 13h42

Je te lis bien sûr, Artemus...
Mais je souhaiterais d'abord me débloquer avec cette histoire de syntaxe de trigger qui coince... J'en suis là et j'applique à la lettre ce que j'ai trouvé sur la doc mysql. Et je pose ce code dans l'écran SQL de mon phpmyadmin. Et ça me jette sur "near END IF"... comprends pas...

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
delimiter |
CREATE TRIGGER no_insert_overlapping BEFORE INSERT ON horaire
  FOR EACH ROW
  BEGIN
	IF EXISTS(
		SELECT *
		FROM horaire
		WHERE STR_TO_DATE(CONCAT(jour, ' ', debut), '%Y-%m-%d %H:%i:%s') < NEW.STR_TO_DATE(CONCAT(jour, ' ', fin), '%Y-%m-%d %H:%i:%s')
		AND STR_TO_DATE(CONCAT(jour, ' ', fin), '%Y-%m-%d %H:%i:%s') > NEW.STR_TO_DATE(CONCAT(jour, ' ', debut), '%Y-%m-%d %H:%i:%s')
	)
	END IF;
  END;
|
delimiter ;

Nombre d'entrées du 15 au 15 du mois. [MySQL]

PHP & Base de données

Vue hybride

Discussions similaires

Partager

Partager