Discussion :

[StringBuilder]

* Bonjour, *

J'ai un jeu de lignes avec dans une colonne un seuil maximum à ne pas dépasser.
=> Des employés avec une durée de travail hebdomadaire

J'ai ensuite une série de ligne en face desquelles je dois mettre une et une seule ligne de mon jeu de données précédent. Pour le moment, pas de notion d'ordre ou de priorité : premier trouvé, premier utilisé.
=> Un planning, où pour chaque heure je dois avoir un employé en activité

Cela implique donc que lorsque je mets un employé sur une case du planning, je dois décrémenter son seuil d'heures, sans pour autant modifier le seuil des autres.
Comment faire ?

Plutôt qu'un long discours, voici ma requête actuelle :

Les deux premières CTE sont là pour éviter d'avoir à crée des tables.

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
 
with horaire (heure)
as
(
    select 1 heure
    union all
    select heure + 1 from horaire where heure < 10
),
employe (id, temps_travail)
as
(
    select 1, 3
    union all
    select 2, 4
    union all
    select 3, 5
),
occupation (heure, id, duree, temps_travail)
as
(
    select horaire.heure, employe.id, count(*) over(partition by employe.id order by horaire.heure) duree, employe.temps_travail
    from horaire
    cross join employe
)
select *
from occupation
where temps_travail >= duree;

Ce qui me 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
 
heure       id          duree       temps_travail
----------- ----------- ----------- -------------
1           1           1           3
2           1           2           3
3           1           3           3
1           2           1           4
2           2           2           4
3           2           3           4
4           2           4           4
1           3           1           5
2           3           2           5
3           3           3           5
4           3           4           5
5           3           5           5
 
(12*ligne(s) affectée(s))

Moi j'aurais préféré...

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
 
heure       id          duree       temps_travail
----------- ----------- ----------- -------------
 1          1           1           3
 2          1           2           3
 3          1           3           3
 4          2           1           4
 5          2           2           4
 6          2           3           4
 7          2           4           4
 8          3           1           5
 9          3           2           5
10          3           3           5
 
(10*ligne(s) affectée(s))

Évidemment, je cherche à faire ça "proprement", c'est à dire sans langage procédural.

* Merci *

[aieeeuuuuu]

Bonjour,

Ceci doit fonctionner :

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
 
 
WITH horaire (heure)
AS
(
	SELECT 1 heure
	union ALL
	SELECT heure + 1 FROM horaire WHERE heure < 10
),
employe (id, temps_travail)
AS
(
	SELECT 1, 3
	union ALL
	SELECT 2, 4
	union ALL
	SELECT 3, 5
)
,horaire_indice (heure, indice) AS (
	SELECT heure , row_number() OVER(ORDER BY heure) as indice
	FROM horaire
)
,repartition AS (
	SELECT 
		  id
		, temps_travail
		, heure as duree
		, ROW_NUMBER() OVER(ORDER BY id, indice) AS RN
	FROM employe
	INNER JOIN horaire_indice
		ON indice <= temps_travail
)
SELECT	heure, id, duree, temps_travail
FROM	repartition
INNER JOIN horaire_indice
	ON indice= RN

[GeekMokona]

Autre 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
WITH horaire (heure)
AS
(
	SELECT 1 heure
	union ALL
	SELECT heure + 1 FROM horaire WHERE heure < 10
),
employe (id, temps_travail)
AS
(
	SELECT 1, 3
	union ALL
	SELECT 2, 4
	union ALL
	SELECT 3, 5
),
EmployeRang (Id ,temps_travail , rang )
As
(
Select ID , temps_TRavail , ROW_NUMBER() over( order By ID)
From employe
),
occupation (heure, id, duree, temps_travail , rang , DernierHeurePrecedent )
AS
(
	SELECT horaire.heure, employe.id, ROW_NUMBER() over(partition BY employe.id order by horaire.heure  ) duree, employe.temps_travail , rang , 0 as DernierHeurePrecedent 
	FROM horaire
	Inner JOIN  (select ID , temps_TRavail , rang From EmployeRang Where rang= 1) as Employe On 
	(horaire.heure <= employe.temps_travail)
 
	Union all
 
	SELECT horaire.heure, employe.id, ROW_NUMBER() over(partition BY employe.id order by horaire.heure  ) duree, employe.temps_travail , rang , DernierHeurePrecedent 
	FROM horaire
	Inner JOIN  (select occupation.temps_travail+Occupation.Heure -1 as DernierHeurePrecedent , EmployeRang.ID , EmployeRang.temps_travail ,  EmployeRang.rang 
	From EmployeRang inner Join occupation  on
	(EmployeRang.rang = Occupation.Rang + 1 
	and occupation.duree = 1 )
	) as Employe On 
	(horaire.heure <= employe.temps_travail+DernierHeurePrecedent and horaire.heure > DernierHeurePrecedent )
 
 
)
SELECT *
FROM occupation

[StringBuilder]

Merci pour les deux réponses.

Sauf que je me rends compte qu'à vouloir trop simplifier le problème pour vous l'exposer, ni la première, ni la seconde solution ne m'aide pas des masses pour mon problème de fond

J'ai vraiment du mal à me projeter dans la méthode CTE...

Du coup, je vais vous exposer le problème plus en profondeur... (et à peu de chose près complet cette fois) :

En fait, dans les horaires, j'ai en plus deux colonnes "nombre d'employés" et "nombre de chefs" qui indique combien d'employés au total doivent travailler en même temps pour chaque heure donnée, mais aussi parmi ces employés combien doivent être chefs.

Et dans les employés, j'ai une relation avec un éclatement des disponibilités de chaque employé (avec le détail : employé 1 disponible à 9h, 10h et 11h).

Quand j'essaie de rajouter l'un ou l'autre des deux éléments, j'arrive à rien...

J'ai l'impression d'avoir grillé tous mes neurones... Pis faut dire que l'heure n'aide pas des masses...

[aieeeuuuuu]

Il faudrait la structure des tables, un jeu d'essai et le résultat attendu pour bien comprendre votre besoin (est-ce que les chefs sont a comptabiliser pour le nombre total d'employés) ?

[StringBuilder]

Il s'agit d'un petit projet perso d'auto-formation/entraînement/toolbox.

Il s'agit de gérer une crèche, et notamment les planning des employés.

On a donc les règles suivantes :
- Il faut toujours un minimum de 2 employés dans la crèche
- Il doit toujours y avoir une auxiliaire puériculture dans la crèche
- Il doit y avoir une auxiliaire puériculture pour chaque tranche de 10 enfants maximum
- Il doit y avoir une personne pour chaque tranche de 4 enfants
- Une personne ne doit pas être "en deux fois" c'est à dire que dans une même journée, elle n'a qu'une heure d'arrivée et qu'une heure de départ
- Les enfants ont des horaires libres, mais doivent prévenir au minimum une semaine à l'avance (donc le planning calculé est ferme, il n'y a plus d'ajustement possible)
- Les employés on un temps de travail hebdomadaire à ne pas dépasser (bon, après on peut s'amuser à autoriser 20% d'heures supplémentaires, mais là ça commence à faire déjà pas mal)
- Les employés étant pour la plupart à temps partiel, ils ont des contraintes d'indisponibilité certaines jours, il ne faut pas les planifier à ces moments là.
- On cherche à faire travailler en priorité les auxiliaires petite enfance qui sont moins chères que les auxiliaires puériculture
- La crèche a des horaires d'ouverture "standards" mais n'ouvre que s'il y a effectivement des enfants (si elle est censée ouvrir à 7h mais qu'aucun enfant n'est prévu avant 10h, elle ouvre à 10h)
- La granularité du planning est d'une demi-heure (toute 1/2 entamée est comptabilisée entièrement)

Hmmm, dans les grandes lignes, c'est tout (c'est déjà pas mal )

Voici le script de mes tables (SQL Server 2014) ainsi que trois procédures stockées permettant de remplir facilement les tables de planning et dispo pour la crèche, les enfants et les employés :

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
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
 
drop procedure CreerDisponibiliteEmploye;
drop procedure CreerHoraireEnfant;
drop procedure CreerHoraireCreche;
go
 
drop table horaire_enfant;
drop table planning_employe;
drop table disponibilite_employe;
drop table horaire;
drop table enfant;
drop table employe;
drop table creche;
drop table qualification;
go
 
create table qualification
(
	id int primary key identity,
	nom varchar(50) not null
);
 
create table creche
(
	id int primary key identity,
	nom varchar(50) not null,
	places int not null
);
 
create table employe
(
	id int primary key identity,
	creche_id int not null references creche(id),
	qualification_id int not null references qualification(id),
	nom varchar(50) not null,
	heures_hebdo int not null
);
 
create table enfant
(
	id int primary key identity,
	creche_id int not null references creche(id),
	nom varchar(50) not null,
	naissance date not null
);
 
create table horaire
(
	id int primary key identity,
	creche_id int not null references creche(id),
	heure datetime2 not null,
	unique (creche_id, heure)
);
 
create table horaire_enfant
(
	id int primary key identity,
	enfant_id int not null references enfant(id),
	horaire_id int not null references horaire(id)
);
 
create table disponibilite_employe
(
	id int primary key identity,
	employe_id int not null references employe(id),
	horaire_id int not null references horaire(id)
);
 
create table planning_employe
(
	id int primary key identity,
	planning_id int not null references disponibilite_employe(id)
);
go
 
create procedure CreerHoraireCreche
(
	@creche_id int,
	@ouverture datetime2,
	@fermeture datetime2
)
as
begin
	if DATEPART(minute, @ouverture) not in (0, 30) or DATEPART(second, @ouverture) > 0 or DATEPART(millisecond, @ouverture) > 0 or DATEPART(microsecond, @ouverture) > 0
	begin
		print 'L''heure d''ouverture doit avoir une granularité de 30 minutes maximum.';
		return;
	end;
	if DATEPART(minute, @fermeture) not in (0, 30) or DATEPART(second, @fermeture) > 0 or DATEPART(millisecond, @fermeture) > 0 or DATEPART(microsecond, @fermeture) > 0
	begin
		print 'L''heure de fermeture doit avoir une granularité de 30 minutes maximum.';
		return;
	end;
 
	delete horaire
	where creche_id = @creche_id
	and cast(heure As Date) between cast(@ouverture As Date) and cast(@fermeture As Date);
 
	with T (horaire)
	as
	(
		select @ouverture horaire
		union all
		select DATEADD(minute, 30, horaire) from T where DATEADD(minute, 30, horaire) < @fermeture
	)
	insert into horaire (creche_id, heure)
	select @creche_id, horaire from T;
end;
go
 
create procedure CreerHoraireEnfant
(
	@enfant_id int,
	@arrivee datetime2,
	@depart datetime2
)
as
begin
	if DATEPART(minute, @arrivee) not in (0, 30) or DATEPART(second, @arrivee) > 0 or DATEPART(millisecond, @arrivee) > 0 or DATEPART(microsecond, @arrivee) > 0
	begin
		print 'L''heure d''arrivée doit avoir une granularité de 30 minutes maximum.';
		return;
	end;
 
	if DATEPART(minute, @depart) not in (0, 30) or DATEPART(second, @depart) > 0 or DATEPART(millisecond, @depart) > 0 or DATEPART(microsecond, @depart) > 0
	begin
		print 'L''heure de départ doit avoir une granularité de 30 minutes maximum.';
		return;
	end;
        -- TODO : Gérer le dépassement de capacité de la crèche
 
	set @depart = dateadd(second, -1, @depart)
 
	delete he
	from horaire_enfant he
	inner join horaire h on h.id = he.id
	where he.enfant_id = @enfant_id
	and h.heure between @arrivee and @depart;
 
	insert into horaire_enfant (enfant_id, horaire_id)
	select @enfant_id, horaire.id
	from horaire
	where heure between @arrivee and @depart;
end;
go
 
create procedure CreerDisponibiliteEmploye
(
	@employe_id int,
	@debut datetime2,
	@fin datetime2
)
as
begin
	if DATEPART(minute, @debut) not in (0, 30) or DATEPART(second, @debut) > 0 or DATEPART(millisecond, @debut) > 0 or DATEPART(microsecond, @debut) > 0
	begin
		print 'L''heure dde début doit avoir une granularité de 30 minutes maximum.';
		return;
	end;
 
	if DATEPART(minute, @fin) not in (0, 30) or DATEPART(second, @fin) > 0 or DATEPART(millisecond, @fin) > 0 or DATEPART(microsecond, @fin) > 0
	begin
		print 'L''heure de fin doit avoir une granularité de 30 minutes maximum.';
		return;
	end;
 
	set @fin = dateadd(second, -1, @fin)
 
	delete de
	from disponibilite_employe de
	inner join horaire h on h.id = de.id
	where de.employe_id = @employe_id
	and h.heure between @debut and @fin;
 
	insert into disponibilite_employe (employe_id, horaire_id)
	select @employe_id, horaire.id
	from horaire
	where heure between @debut and @fin;
end;
go

PS : Au lieu du DELETE puis INSERT, j'aurais pu utiliser MERGE ou un NOT EXISTS mais je me suis dit que conserver des ID consécutifs pour les données d'un même lot n'était pas forcément plus mal en cas de changement d'horaire.

Je cherche à créer la requête (et la PS qui va permettre d'alimenter la table planning, non présente dans le script) en respectant les règles ci-dessus.
Le souci, c'est que j'ai trop voulu simplifié ma question initiale, et du coup je préfère toutes les données cette fois

Un jeu de tests (incomplet, mais pas bien compliqué à compléter, j'ai voulu commencer par un cas simple, sur une seule journée et sans difficulté particulière) :

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
 
insert into creche (nom, places) values ('Weill', 10);
insert into enfant (nom, naissance, creche_id) values ('Enfant 1', DATEFROMPARTS(2014, 2, 10), 1);
insert into enfant (nom, naissance, creche_id) values ('Enfant 2', DATEFROMPARTS(2013, 1, 5), 1);
insert into enfant (nom, naissance, creche_id) values ('Enfant 3', DATEFROMPARTS(2003, 10, 17), 1);
insert into qualification (nom) values ('Auxiliaire puéricultrice');
insert into qualification (nom) values ('Auxiliaire petite enfance');
insert into employe (nom, qualification_id, creche_id, heures_hebdo) values ('Mme A', 1, 1, 35 * 2); -- on stock le temps de travail sous forme de demi-heures
insert into employe (nom, qualification_id, creche_id, heures_hebdo) values ('Mme B', 1, 1, 2.5 * 2);
insert into employe (nom, qualification_id, creche_id, heures_hebdo) values ('Mme C', 2, 1, 4 * 2);
insert into employe (nom, qualification_id, creche_id, heures_hebdo) values ('Mme D', 2, 1, 4 * 2);
 
declare @d1 datetime2;
declare @d2 datetime2;
set @d1 = DATETIMEFROMPARTS(2014, 9, 4, 9, 0, 0, 0);
set @d2 = DATETIMEFROMPARTS(2014, 9, 4, 18, 30, 0, 0);
exec dbo.CreerHoraireCreche 1, @d1, @d2;
 
set @d1 = DATETIMEFROMPARTS(2014, 9, 4, 10, 0, 0, 0);
set @d2 = DATETIMEFROMPARTS(2014, 9, 4, 15, 0, 0, 0);
exec dbo.CreerHoraireEnfant 1, @d1, @d2;
 
set @d1 = DATETIMEFROMPARTS(2014, 9, 4, 9, 0, 0, 0);
set @d2 = DATETIMEFROMPARTS(2014, 9, 4, 12, 00, 0, 0);
exec dbo.CreerDisponibiliteEmploye 1, @d1, @d2;
set @d2 = DATETIMEFROMPARTS(2014, 9, 4, 16, 00, 0, 0);
exec dbo.CreerDisponibiliteEmploye 2, @d1, @d2;
exec dbo.CreerDisponibiliteEmploye 3, @d1, @d2;
exec dbo.CreerDisponibiliteEmploye 4, @d1, @d2;

Comment ça, être jeune père ne me réussi pas ?