Je sollicite votre aide pour la création du requête sur des intervalles de dates

**jejeadsl** · 22/05/2015, 17h02

Bonjour à tous et à tous,

étant newbie sur Sql server . Je sollicite votre aide pour la création du requête.

Voici mon besoin :

j'ai besoin de récupérer les disponibilités (du 19/05/2015 00:00 au 29/05/2015 23:59) des employés à partir d'une table "assignations_planifiees" avec les données suivantes :

Employe Start date End date
Eric 19/05/2015 08:00 19/05/2015 18:00
Eric 21/05/2015 08:00 21/05/2015 18:00
Didier 18/05/2015 08:00 22/05/2015 18:00
Didier 25/05/2015 08:00 25/05/2015 18:00
Didier 27/05/2015 08:00 29/05/2015 18:00

La table "assignations_planifiees" recense des dates où les employées sont en intervention donc pas disponible.

Le résultat de la requête devrait me remonter :

Employe Start date End date
Eric 19/05/2015 19:00 21/05/2015 07:59
Eric 21/05/2015 18:01 29/05/2015 23:59
Didier 22/05/2015 18:01 25/05/2015 07:59
Didier 25/05/2015 18:01 27/05/2015 07:59
Didier 29/05/2015 18:01 27/05/2015 23:59

Merci de votre aide.

Bon week-end

Jejeadsl

**hmira** · 24/05/2015, 21h09

Juste quelques précisions, néanmoins importantes !

1 - Il n'est pas rare de voir certains développeurs - vous en faites apparemment partie ! - représenter les borne ou limites des intervalles de date et heures sous la forme avec yyyy-mm-ddT23:59
Exemple : 2015-05-24T23:59

Sachez qu'il s'agit d'une très mauvaise pratique. En effet, la précision du type Datatime varie entre 3 à 4 millisecondes !
et donc entre 2015-05-24T23:59 et 2015-05-25T00:00 il y a plus précisément ~~4 Tops~~ 15000 Tops d'horloges ! (ou TickCount) voir rectificatif ci-dessous (1) :
Pour vous en convaincre examiner le résultat de la requête ci-aprè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
SELECT '2015-05-24T23:59:59.991' ValeurDate,  CAST (CAST('2015-05-24T23:59:59.991' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee  
UNION  
SELECT '2015-05-24T23:59:59.992' ValeurDate, CAST (CAST('2015-05-24T23:59:59.992' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee  
UNION  
SELECT '2015-05-24T23:59:59.993' ValeurDate, CAST (CAST('2015-05-24T23:59:59.993' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee  
UNION  
SELECT '2015-05-24T23:59:59.994' ValeurDate, CAST (CAST('2015-05-24T23:59:59.994' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee  
UNION  
SELECT '2015-05-24T23:59:59.995' ValeurDate, CAST (CAST('2015-05-24T23:59:59.995' AS DATETIME) AS BINARY (8)) AS ValeurRellementStockee  
UNION ALL  
SELECT '2015-05-24T23:59:59.996' ValeurDate, CAST (CAST('2015-05-24T23:59:59.996' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee  
UNION ALL  
SELECT '2015-05-24T23:59:59.997' ValeurDate, CAST (CAST('2015-05-24T23:59:59.997' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee  
UNION ALL  
SELECT '2015-05-24T23:59:59.998' ValeurDate, CAST (CAST('2015-05-24T23:59:59.998' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee  
UNION ALL  
SELECT '2015-05-24T23:59:59.999' ValeurDate, CAST (CAST('2012-05-16T23:59:59.999' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee

Résultat :

Code SQL :

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

1
2
3
4
5
6
7
8
9
2015-05-24T23:59:59.991	0x0000A4A2018B81FD
2015-05-24T23:59:59.992	0x0000A4A2018B81FE
2015-05-24T23:59:59.993	0x0000A4A2018B81FE
2015-05-24T23:59:59.994	0x0000A4A2018B81FE
2015-05-24T23:59:59.995	0x0000A4A2018B81FF
2015-05-24T23:59:59.996	0x0000A4A2018B81FF
2015-05-24T23:59:59.997	0x0000A4A2018B81FF
2015-05-24T23:59:59.998	0x0000A4A2018B81FF
2015-05-24T23:59:59.999	0x0000A05400000000

2 - Avant d'aborder la représentation du temps, je vous conseille vivement de commencer par lire l’excellent l'article de SQL Pro ci-dessous :
http://sqlpro.developpez.com/cours/gestiontemps/

Vous comprendrez mieux notamment comment, en SQL, on exprime les prédicats OVERLAPS (recouvrement entre périodes), et bien d'autres choses ...

3 - Sinon la solution à votre problème peut être élaborée aisément par l'utilisation des fonctions analytique LEAD, LAGsi vous êtes sous SQL Server 2012. sinon, (si vous êtes sous SQL Server 2005, 2008 ou 2008 R2) par le recours aux jointures récurssives entre CTE (Common Table Expression) ou tout autre technique permettant de simuler les fonctions LEAD, LAG dans les versions d'avant SQL Server 2012.

4 - Je vous laisse étudier plus en détails ce sujet traitant de la représentation du temps. ainsi que les fonctions analytiques LEAD, LAG. Je vous donnerais, le cas échéant, une solution à votre problème, à moins que ce soit quelqu'un d'autre du Forum qui vous donne une autre solution.

PS : Selon le philosophe Gaston Bachelard :
"Le temps n’a qu’une réalité, celle de l’Instant. Autrement dit, le temps est une réalité resserrée sur l’instant et suspendue entre deux néants."

Rectificatif :
(1) : 15000 Tops d'horloge au lieu de 4 Tops d'horloge ! En effet :
Entre 2015-05-24T23:59 et 2015-05-25T00:00 il y a 1 minute
1 minute = 60 secondes soit 60000 millisecondes
A raison de 4 tops horloge tous les 4 millisecondes, cela donne 60000/4 soit 15000 Tops d'horloge !

A+

**elsuket** · 31/05/2015, 17h55

Envoyé par hmira

En effet, la précision du type Datatime varie entre 3 à 4 millisecondes !

Votre point est tout à fait valide, mais la précision d'une date dépend des contraintes de l'application.
Par exemple pour la création d'un client d'une banque, cela importe peu; mais quand il s'agit de transactions sur les marchés, il faut une précision très élevée.

Comme jejeadsl travaille sous SQL Server 2005, il ne peut pas utiliser les expression de table (dès SQL Server 2008) commune et les fonctions LEAD and LAG (dès SQL Server 2012).

Néanmoins ce type de problème a été abordé bien avant ces deux fonctionnalités.
Vous pouvez lire le chapitre d'Itzik Ben Gan sur le sujet.
Le chapitre 32 de la 5e édition de l'ouvrage SQL For Smarties : Advanced SQL Programming de Joe Celko expose les solutions à ce type de problème de façon plus extensive.

Bonne lecture !

**hmira** · 01/06/2015, 12h44

Envoyé par elsuket

Votre point est tout à fait valide, mais la précision d'une date dépend des contraintes de l'application.
Par exemple pour la création d'un client d'une banque, cela importe peu; mais quand il s'agit de transactions sur les marchés, il faut une précision très élevée.

Concernant la précision, je faisais effectivement allusion à la précision maximale théorique du type Datetime, mais tu as raison sur le principe, sur le fait qu'il faut également tenir compte de la précision fonctionnelle. Le problème est que la précision fonctionnelle n'est pas prise en compte au niveau du stockage des données au travers le type Datetime, contrairement au type Datetime2, le type datetime ne permet pas de définir la précision au niveau de type de données lui même, en tant que domaine au sens relationnel. Et donc la précision, à défaut d'être implémentée au niveau du type de donnée Datetime, devient une affaire d'interprétation.
Le type datetime, rappelons le, est maintenant devenu "obsolète" et qu'il faudra utiliser en lieu et place le type datetime2 à partir de SQL Server 2008. Ce dernier est plus optimal et propose les niveaux de précisions ci-dessous :

Code :

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

1
2
3
4
5
6
7
8
9
Type  Précision  Taille de stockage 
datetime2(0)   10^0 secondes (s) soit 1 seconde  6 octets 
datetime2(1)   10^-1	secondes (s) soit 1/10 ème de secondes  6 octets 
datetime2(2)   10^-2	secondes (s) soit 1/100 ème de secondes 6 octets 
datetime2(3)   10^-3 secondes, soit 1 millisecondes (ms)  soit 1/1000 ème de secondes  7 octets 
datetime2(4)   10^-4 secondes soit 1/10 ème de millisecondes 7 octets 
datetime2(5)   10^-5 secondes soit 1/100 ème de millisecondes 8 octets 
datetime2(6)   10^-6 secondes soit 1 microsecondes (µs)  soit 1/1000 ème de millisecondes  µs  8 octets
datetime2(7)   10^-7 secondes, soit 100 * 10^-9 secondes  soit 100 nanosecondes (100 ns) 8 octets

Envoyé par elsuket

Comme jejeadsl travaille sous SQL Server 2005, il ne peut pas utiliser les expression de table (dès SQL Server 2008) commune et les fonctions LEAD and LAG (dès SQL Server 2012).

Pour SQL 2005, je croyais que les CTE était déjà disponible (?), mais tu as sans doute raison, j'avoue que je n'ai plus aucune instance 2005 pour vérifier (plus de clients sous SQL Server 2005, donc le problème est réglé ! c'est petite provocation bien sûr envers les utilisateur de SQL Server 2005 !)

Envoyé par elsuket

Néanmoins ce type de problème a été abordé bien avant ces deux fonctionnalités.
Vous pouvez lire le chapitre d'Itzik Ben Gan sur le sujet.
Le chapitre 32 de la 5e édition de l'ouvrage SQL For Smarties : Advanced SQL Programming de Joe Celko expose les solutions à ce type de problème de façon plus extensive.

Bonne lecture !

Merci pour ces suggestions de lecture.
J'ai parcours rapidement le chapitre 5, d'Itzik Ben Gan, document accessible gratuitement. les différentes approches et solutions proposées me paraissent effectivement très intéressantes.

PS : Merci pour tes contributions

A+

**SQLpro** · 01/06/2015, 17h17

Envoyé par jejeadsl

Bonjour à tous et à tous,

étant newbie sur Sql server . Je sollicite votre aide pour la création du requête.

Voici mon besoin :

j'ai besoin de récupérer les disponibilités (du 19/05/2015 00:00 au 29/05/2015 23:59) des employés à partir d'une table "assignations_planifiees" avec les données suivantes :

Employe Start date End date
Eric 19/05/2015 08:00 19/05/2015 18:00
Eric 21/05/2015 08:00 21/05/2015 18:00
Didier 18/05/2015 08:00 22/05/2015 18:00
Didier 25/05/2015 08:00 25/05/2015 18:00
Didier 27/05/2015 08:00 29/05/2015 18:00

La table "assignations_planifiees" recense des dates où les employées sont en intervention donc pas disponible.

Le résultat de la requête devrait me remonter :

Employe Start date End date
Eric 19/05/2015 19:00 21/05/2015 07:59
Eric 21/05/2015 18:01 29/05/2015 23:59
Didier 22/05/2015 18:01 25/05/2015 07:59
Didier 25/05/2015 18:01 27/05/2015 07:59
Didier 29/05/2015 18:01 27/05/2015 23:59

A mon avis le résultat donné ci dessus est faux... Il devrait être celui là :

Eric 19/05/2015 18:01 21/05/2015 07:59
Eric 21/05/2015 18:01 29/05/2015 23:59 --> pourquoi s'arrêter la ??? C'est la fin de son contrat ou arbitraire ??? Moi je mettrait '31/12/9999 23h59'
Didier 22/05/2015 18:01 25/05/2015 07:59
Didier 25/05/2015 18:01 27/05/2015 07:59
Didier 29/05/2015 18:01 27/05/2015 23:59 --> pourquoi s'arrêter la ??? C'est la fin de son contrat ou arbitraire ??? Moi je mettrait '31/12/9999 23h59'

De plus il est courant pour calculer les OVERLAPS, de prévoir que vos intervalles sont fermés d'un côté, ouvert de l'autre :
exemple assignations_planifiees :
Eric [ 19/05/2015 08:00 19/05/2015 18:00 [
Eric [ 21/05/2015 18:01 29/05/2015 23:59 [
Il est alors d'usage que les bornes se touchent car elle ne se recoupent jamais.
la solution de disponibilité est alors :
Eric 19/05/2015 18:00 21/05/2015 08:00
Eric 21/05/2015 18:00 31/12/9999 23:59

A +

**SQLpro** · 01/06/2015, 17h36

La solution à votre problème est alors la suivante :

Code :

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

1
2
3
4
5
6
-- la table :
CREATE TABLE assignations_planifiees
(Employe      VARCHAR(16),
 Start_date   DATETIME,
 End_date     DATETIME);
GO

Code :

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

1
2
3
4
5
6
7
8
-- les données de test :
INSERT INTO assignations_planifiees VALUES
('Eric',   '19/05/2015 08:00', '19/05/2015 18:00'),
('Eric',   '21/05/2015 08:00', '21/05/2015 18:00'),
('Didier', '18/05/2015 08:00', '22/05/2015 18:00'),
('Didier', '25/05/2015 08:00', '25/05/2015 18:00'),
('Didier', '27/05/2015 08:00', '29/05/2015 18:00');
GO

Code :

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

1
2
3
4
5
6
7
8
-- la requête
SELECT deb.Employe, deb.End_date AS Start_date, 
       COALESCE(MIN(fin.Start_date), '99991231 23:59') AS End_date 
FROM   assignations_planifiees AS deb
       LEFT OUTER JOIN assignations_planifiees AS fin
	        ON deb.End_date < fin.Start_date
			AND deb.Employe = fin.Employe
GROUP  BY deb.Employe, deb.End_date;

ATTENTION :dans votre modèle vous devez avoir mis impérativement 2 contraintes sinon les résultats pourrait être incohérent en cas d'erreur de saisie :
1) pour la première :

Code :

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

1
2
3
4
ALTER TABLE assignations_planifiees
   ADD CONSTRAINT CK_assignations_planifiees_CHRONOLOGIE 
   CHECK (Start_date < End_date);
GO

2) pour la seconde, quelque chose comme :

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
CREATE TRIGGER E_IU_assignations_planifiees_NON_OVERLAPS
ON assignations_planifiees
FOR INSERT, UPDATE
AS
SET NOCOUNT ON;
IF NOT UPDATE(Start_date) AND NOT UPDATE(End_date)
   RETURN;
IF EXISTS(SELECT 1
          FROM   inserted AS i
		         JOIN assignations_planifiees AS ap
				      ON i.Employe = ap.Employe
          WHERE  (i.Start_date >= ap.Start_date AND i.Start_date < ap.End_date)
		     OR  (i.End_date > ap.Start_date AND i.End_date <= ap.End_date)
		  GROUP  BY i.Employe
		  HAVING COUNT(*) > 1)
BEGIN
   ROLLBACK;
   RAISERROR('Une période d''assignation planifiée ne peut en recouper une autre pour un même employé', 16, 1);
END;
GO

A +