Requête temps planning

**cerede2000** · 10/05/2016, 09h00

Bonjour à tous,

Pour commencer voici la structure des tables concerné par la requête que je cherche à élaborer :

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
 
CREATE TABLE [dbo].[Operateur](
	Code [nvarchar](10) NOT NULL,
	[Nom] [varchar](50) NOT NULL,
	[Prenom] [varchar](50) NOT NULL,
	[Actif] [bit] NOT NULL,
	[IdPlanning] [int] NOT NULL,
	[equipe] [tinyint] NOT NULL,
 CONSTRAINT [PK_Operateur] PRIMARY KEY CLUSTERED 
(
	Code ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
ALTER TABLE [dbo].[Operateur]  WITH CHECK ADD  CONSTRAINT [FK_Operateur_Planning] FOREIGN KEY([IdPlanning])
REFERENCES [dbo].[Planning] ([ID])
 
ALTER TABLE [dbo].[Operateur] CHECK CONSTRAINT [FK_Operateur_Planning]

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE [dbo].[Planning](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Nom] [varchar](10) NOT NULL,
	[Tolerance] [tinyint] NOT NULL CONSTRAINT [DF_Planning_Tolerance]  DEFAULT ((0)),
 CONSTRAINT [PK_Planning] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

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
CREATE TABLE [dbo].[PlanningLignes](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ID_PLANNING] [int] NOT NULL,
	[Jour] [char](1) NOT NULL,
	[Debut] [time](7) NOT NULL,
	[Fin] [time](7) NOT NULL,
	[ReposDebut] [time](7) NULL,
	[ReposFin] [time](7) NULL,
 CONSTRAINT [PK_PlanningLignes] PRIMARY KEY CLUSTERED 
(
	[ID_PLANNING] ASC,
	[Jour] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
ALTER TABLE [dbo].[PlanningLignes]  WITH CHECK ADD  CONSTRAINT [FK_PlanningLignes_Planning] FOREIGN KEY([ID_PLANNING])
REFERENCES [dbo].[Planning] ([ID])
 
ALTER TABLE [dbo].[PlanningLignes] CHECK CONSTRAINT [FK_PlanningLignes_Planning]

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
CREATE TABLE [dbo].[PointageMO](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[CodeOperateur] [nvarchar](10) NOT NULL,
	[CodeActivity] [nvarchar](10) NOT NULL,
	[CodeLancement] [nvarchar](10) NULL,
	[DateHeure] [datetime] NOT NULL,
	[Duree] [int] NOT NULL,
	[Borne] [nvarchar](25) NOT NULL,
	[Cloture] [bit] NOT NULL CONSTRAINT [DF_PointageMO_Cloture]  DEFAULT ((0)),
 CONSTRAINT [PK_PointageMO] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
ALTER TABLE [dbo].[PointageMO]  WITH CHECK ADD  CONSTRAINT [FK_PointageMO_Activity] FOREIGN KEY([CodeActivity])
REFERENCES [dbo].[Activity] (Code)
 
ALTER TABLE [dbo].[PointageMO] CHECK CONSTRAINT [FK_PointageMO_Activity]
 
ALTER TABLE [dbo].[PointageMO]  WITH CHECK ADD  CONSTRAINT [FK_PointageMO_Lancement] FOREIGN KEY([CodeLancement])
REFERENCES [dbo].[Lancement] (Code)
 
ALTER TABLE [dbo].[PointageMO] CHECK CONSTRAINT [FK_PointageMO_Lancement]
 
ALTER TABLE [dbo].[PointageMO]  WITH CHECK ADD  CONSTRAINT [FK_PointageMO_Operateur] FOREIGN KEY([CodeOperateur])
REFERENCES [dbo].[Operateur] (Code)
 
ALTER TABLE [dbo].[PointageMO] CHECK CONSTRAINT [FK_PointageMO_Operateur]

J'ai des opérateurs lié à un planning, chaque planning à donc une ligne par jour de la semaine.
Les opérateurs pointent plusieurs fois au cours de la journée et ce que je veux c'est faire une requête qui me donne la liste des pointages avec début et fin.
Mais également gérer la pause de milieu de journée.

C'est à dire par exemple, l'opérateur 1 à pour planning :
07h30 - 12h20 et 13h05 - 17h00

Il pointe à 07h30
Il pointe à 10h50
Il pointe à 11h48
Il pointe à 13h36
Il pointe à 15h53
Il pointe à 16h44

Je veux sortir dans ma requête :
07h30 - 10h50
10h50 - 11h48
11h48 - 12h20
13h05 - 13h36
13h36 - 15h53
15h53 - 16h44
16h44 - 17h00

Je n'ai pas eu de soucis pour faire le lien sur la table PointageMO elle même avec cette requête :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT *
FROM PointageMO pmo
OUTER APPLY (
	SELECT TOP 1 CodeOperateur, DateHeure
	FROM PointageMO pmoFin
        WHERE pmoFin.CodeOperateur = pmo.CodeOperateur
	AND CAST(pmoFin.DateHeure AS DATE) = CAST(pmo.DateHeure AS DATE) AND pmoFin.DateHeure > pmo.DateHeure
) pmoFin
ORDER BY CAST(pmo.DateHeure AS DATE), pmo.CodeOperateur, CAST(pmo.DateHeure AS TIME)

Par contre je bloque sur l'intégration en plus de la table PlanningLignes pour gérer la pause milieu de journée.

Avez vous une idée

Merci d'avance.

PS : Si besoin de jeu de test, j'en mettrai un dispo.

**aieeeuuuuu** · 10/05/2016, 18h04

Bonjour,

Je n'ai pas lu dans le détail, mais je pense que votre requete est fausse : un TOP(1) en sous requete sans ORDER BY...

Par ailleurs, vous auriez je pense de meilleurs résultat en utilisant des fonctions analytique pour votre problématique.

Concernant la prise en compte de la pause méridionale, vous pouvez peut-être en faire l'union avec la table de pointage.

Mais en effet, un jeu de données serait assez utile

**elsuket** · 10/05/2016, 21h06

Bonjour,

Le problème est que vous stockez :

dans PointageMO l'heure sous le type datetime
dans PlanningLignes, la plage horaire sous la forme de deux valeurs de type time(7)

L'ajout d'une colonne calculée persistée, extrayant l'heure de la valeur datetime devrait vous aider.
En outre, le type time(7) vous donne une précision à 100 nanosecondes, nécessitant 5 octets. Si l'on change à time(0), on a une précision à la seconde, et on utilise 3 octets.

Pour vous donner une idée de la façon donc vous pouvez extraire les plages horaires, voici un petit 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
35
36
DECLARE @badgeuse_log TABLE
(
	id_operateur int NOT NULL
	, heure time(3) NOT NULL
)
 
 
INSERT INTO @badgeuse_log
(
	id_operateur
	, heure
)
VALUES	(1, '07:30')
	, (2, '07:38')
	, (1, '10:50')
	, (2, '12:03')
	, (1, '11:48')
	, (2, '13:01')
	, (1, '13:36')
	, (2, '15:12')
	, (1, '15:53')
	, (1, '16:44')
 
;WITH
	CTE AS
	(
		SELECT	id_operateur
			, heure
			, ROW_NUMBER() OVER(PARTITION BY id_operateur ORDER BY heure) AS n
		FROM	@badgeuse_log
	)
SELECT		*
FROM		CTE AS A
INNER JOIN	CTE AS B
			ON A.id_operateur = B.id_operateur
			AND A.n + 1 = B.n

Pour avoir déjà eu à implémenter ce type de chose, j'avais ajouté une table utilitaire qui avait l'identifiant de l'opérateur, l'identifiant du planning, et le nombre de fois où il a badgé dans les tranches de ce planning. A chaque appel de procédure, j'incrémentais ce compteur (UPDATE maTable SET @maVariable = maColonne = maColonne + 1 ...), et je portais la nouvelle valeur dans la table stockant l'ensemble des événements de "badgeage". De cette façon, on évite l'utilisation de la fonction de fenêtrage ROW_NUMBER(), et l'indexation aidant, on obtient de bonnes performances.

@++

**cerede2000** · 11/05/2016, 08h48

Merci pour vos réponses

Déjà voici un nouvel export des structures avec jeu de test :
Data.zip

@elsuket : Ok pour la récursivité mais le planning étant à part des pointage je ne vois pas comment venir l'intégrer ensuite.

**elsuket** · 11/05/2016, 14h12

Il vous faut pour cela une relation

La requête que je vous ai proposé n'est pas récursive

@++

**aieeeuuuuu** · 12/05/2016, 17h30

Bonjour,

Il faudrait aussi définir ce que vous voulez pour les cas particuliers :

C'est à dire par exemple, l'opérateur 1 à pour planning :
07h30 - 12h20 et 13h05 - 17h00

Il pointe à 07h30
Il pointe à 10h50
Il pointe à 11h48
Il pointe à 13h36
Il pointe à 15h53
Il pointe à 16h44

Je veux sortir dans ma requête :
07h30 - 10h50
10h50 - 11h48
11h48 - 12h20
13h05 - 13h36
13h36 - 15h53
15h53 - 16h44
16h44 - 17h00

Que voulez-vous comme résultat s'il :
1/ pointe aussi à 7h28
2/ pointe aussi à 17h03
3/ pointe aussi à 12h25
4/ pointe aussi à 12h25 et à 13h01
5/ ne pointe pas du tout

**cerede2000** · 19/05/2016, 10h17

Re à tous,

Excuser ma réponse tardive, j'était complètement sur autre chose

En fait le cas particulier que tu évoque sont déjà traité côté programme de pointage.
Il n'est pas possible pour l'opérateur de pointer en dehors de son planning

Et si il pointe pendant la pause de midi et bien le pointage est automatiquement décalé à l'heure de fin de pause.

Il me faut donc juste l'intégration du planning lors de la récupération des pointages.

@elsuket : J'ai regardé ta requête et je l'ai adapté à mes tables, mais je ne comprends toujours comment on peut intercaler la table planning la dedans

En fait, ce sont les pointages qui sont justes avant la pause de midi qui sont à traités.
Si la pause est 12h20 - 13h05

Que l'opérateur pointe à 11h28 puis pointe plus tard à 15h12
Il faut que le pointage de 11h28 deviennent 11h28 - 12h20 / 13h05 - 15h12
C'est possible ça ?

**aieeeuuuuu** · 25/05/2016, 16h49

quelle est votre version de SQL Server ?

**aieeeuuuuu** · 25/05/2016, 17h20

ceci devrait être un bon début

En fonction de la version, il faudra peut être adapter le LEAD, et il y aura aussi moyen de faire mieux

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
 
WITH CTE AS(
	SELECT		
				P.CodeOperateur
			,	CAST(P.DateHeure AS DATE) AS Dte
			,	CAST(P.DateHeure AS TIME) AS Heure
			,	CASE WHEN 	CAST(P.DateHeure AS TIME) BETWEEN Debut AND ReposDebut THEN 0 ELSE 1 END AS periode
			,	PL.Debut
			,	PL.Fin
			,	PL.ReposDebut
			,	PL.ReposFin
	FROM		PointageMO P
	INNER JOIN	Operateur O	
		ON			O.Code = P.CodeOperateur
	INNER JOIN PlanningLignes PL
		ON			PL.ID_PLANNING = O.IdPlanning
		AND			PL.Jour = DATEPART(WEEKDAY,P.DateHeure)
)
,
CTE2 AS (
	SELECT Codeoperateur, Dte, Heure, periode, CASE WHEN periode = 0 THEN ReposDebut ELSE Fin END AS Fin
	FROM CTE
	UNION
	SELECT Codeoperateur, Dte, Debut, periode, CASE WHEN periode = 0 THEN ReposDebut ELSE Fin END AS Fin
	FROM CTE 
	WHERE periode = 0
	UNION
	SELECT Codeoperateur, Dte, ReposFin, periode, CASE WHEN periode = 0 THEN ReposDebut ELSE Fin END AS Fin
	FROM CTE 
	WHERE periode = 1
)
SELECT Codeoperateur, Dte, Heure AS Debut, COALESCE(LEAD(Heure) OVER(PARTITION BY Codeoperateur, Dte, periode ORDER BY Heure), Fin) AS Fin
FROM CTE2
ORDER BY Codeoperateur, Dte, Heure

**cerede2000** · 31/05/2016, 09h07

Alors je suis en SQL Server 2014.

Oua !! Super le résultat !

Merci beaucoup ! C'est top

Juste 2 petits soucis, si la personne ne pointe qu'une seule fois dans la journée.
Donc uniquement le matin à 7h30 du coup ça ne génère que 07h30 - 11h45 (heure du début de pause).

Car en fait le pointage n'est à faire qu'en changement d'activité, donc s'il fait la même chose toute la journée il n'aura qu'un seul pointage.

Est-ce que dans ce cas de pointage unique on pourrais du coup générer 2 lignes ?
Ex : 07h30 - 11h45 et 12h20 - 17h00

Et si il n'y a pas de pause, exemple le vendredi, seul le matin est travaillé donc pas de pause.
Du coup je me retrouve avec 00h00 - 07h30

Est-il possible de rajouter une vérif sur la durée de la pause, si = 0 alors on ne fait rien de particulier ?

Requête temps planning

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager