Discussion :

[cerede2000]

Bonjour à tous,

Je cherche à faire une requête qui me donne le jour ouvré suivant à partir d'une date si bien sur celle ci n'est pas déjà un jour ouvré.

Le logiciel gère une table des jours taggué comme non ouvrés.

Je fais donc une requête sur ma table de départ avec un inner join sur la table de gestion des jours non ouvrés afin d'obtenir les lignes à traitées uniquement :

Code :

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

1
2
3
SELECT *
FROM TableCommandes TC
INNER JOIN TableJoursNonOuvres TJNO ON TJNO.Date = TC.DateCommande

Je cherche maintenant à déterminer le prochain jour ouvré pour modifier la date de commande.

Que pourriez vous me proposer ?

Le tout sera placé dans une procédure stocké.
Ma première idée serais une boucle avec un DATEADD, on ajoute un jour à chaque fois et si le résultat n'est pas dans TableJoursNonOuvres alors c'est que c'est ok !

Mais n'y aurait il pas plus simple et rapide ?

Merci d'avance à vous

[hmira]

Ci-dessous une solution parmi d'autre. Il suffit d'appliquer cette fonction à une date que celle-ci soit un jour ouvré ou non.
Lorsque le paramètre représente un jour non ouvré, le fonction retourne le premier jour ouvré suivant.
Lorsque le paramètre représente un jour ouvré, la fonction retourne le jour ouvré lui-même.

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
CREATE FUNCTION dbo.Fc_ProchainJourOuvre(@pi_Date date)
RETURNS date
AS
BEGIN
 
 DECLARE @JourOuvre date;
 
 WITH J_CTE (JourNonOuvre, JourOuvreSuivant)
 AS (
	SELECT JourNonOuvre, DATEADD(dd, 1, JourNonOuvre) AS JourOuvreSuivant
	FROM dbo.TableJoursNonOuvres
	WHERE JourNonOuvre=@pi_Date
	UNION ALL
	SELECT JNO_1.JourNonOuvre,
	DATEADD(dd, 1, JNO_1.JourOuvreSuivant) AS JourOuvreSuivant
	FROM J_CTE AS JNO_1
	INNER JOIN dbo.TableJoursNonOuvres AS JNO_2 
	ON JNO_1.JourOuvreSuivant=JNO_2.JourNonOuvre
	)
	SELECT TOP(1) @JourOuvre = JourOuvreSuivant
	FROM J_CTE
	WHERE JourNonOuvre=@pi_Date
	ORDER BY JourOuvreSuivant DESC
 
	IF (@JourOuvre IS NULL)
	   SET @JourOuvre=@pi_Date;
 
	RETURN @JourOuvre;
 
END

Remarque : Il existe d'autres techniques mettant en œuvre des vraies tables persistante de calendrier sur une période 20, 30 ans (voir 100 ans) et de vraies jointures ou inéquijointures sur ces tables. Ces solutions sont généralement plus optimales en termes de performances.
Fais des recherches sur le présent site, je sais que SQLPro a publié des articles très intéressants traitant des données temporelles, en lien je crois avec les problématiques que tu soulèves.

A+

[cerede2000]

Merci pour ta réponse

Pour le moment j'étais parti comme ceci :

Code :

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

1
2
3
WHILE (SELECT 1 FROM [dbo].[TableJoursNonOuvres] WHERE [JDate] = @newDate) IS NOT NULL BEGIN
    SET @newDate = DATEADD(DD, 1, @newDate)
END

En fait le but ensuite pour moi est de passer en paramètre de cette fonction une date donc et en même temps un nombre de jour positif ou négatif à ajouter (ou enlever) pour obtenir le jour ouvré correspondant.

Bien sur si on enlève 10 jours par exemple, on retire 10 jours + les jours non ouvrés présent dans intervalle

Est-ce adaptable dans ta requête avec CTE ?

[hmira]

Est-ce adaptable dans ta requête avec CTE ?

Non, je ne vois pas comment (?). Je suis désolé. L'algorithme de la fonction tel que je l'ai conçu, fait en sorte que pour une date donnée, ce dernier s'arrête au premier jour ouvré, suivant, trouvé, et ce, en avançant dans le temps. C'est la condition d'arrêt de la récursion de la CTE.

Donc si tu veux ajouter un nombre positif, il faut appeler la fonction autant de fois dans une boucle while (exemple + 10 jours équiv. à 10 itération dans la boucle while)

En revanche, si tu veux ajouter un nombre négatif, il faut déjà créer une autre fonction sur le même style dbo.Fc_PrecedentJourOuvre(@pi_Date date ) avec des adddate(dd, -1, ..) et inverser légèrement la logique de la fonction dbo.Fc_ProchainJourOuvre pour créer la fonction dbo.Fc_PrecedentJourOuvre permetant de trouver le premier jour ouvré précédent.
Ensuite appeler la fonction dbo.Fc_PrecedentJourOuvre dans une boucle while .. (idem, exemmple - 10 jours équiv. à 10 itération dans la boucle while en remontant dans le temps).

Bref, c'est faible mais cela risque d'être un petit peu "compliqué" mais pas insurmontable.

Sinon, la solution la plus pérenne , la plus compète, et la plus optimale, est de rendre "consistant" l'espace temps.
Pour cela je te suggère de lire l'article complet de SQLPro
http://sqlpro.developpez.com/cours/gestiontemps/

A+

[cerede2000]

Le truc c'est que je suis sur un ERP qui gère déjà un calendrier.
Je ne peux donc pas venir regreffer une logique dessus.

Je me base donc sur sa table de gestion des jours déclarés non ouvrés.

[elsuket]

Bonjour,

On peut réutiliser la table calendrier de l'ERP, ou bien en créer une nouvelle très simplement :

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
CREATE TABLE t_jour
(
	jour_date date NOT NULL
		CONSTRAINT PK_t_jour_ouvre PRIMARY KEY
	, is_ouvre AS(CASE WHEN DATENAME(weekday, jour_date) IN ('Saturday', 'Sunday') THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END)
)
GO
 
WITH
	N AS(SELECT NULL AS v UNION ALL SELECT NULL)
	, N1 AS (SELECT A.v FROM N AS A CROSS JOIN N AS B)
	, N2 AS (SELECT A.v FROM N1 AS A CROSS JOIN N1 AS B)
	, N3 AS (SELECT A.v FROM N2 AS A CROSS JOIN N2 AS B)
	, N4 AS (SELECT A.v FROM N3 AS A CROSS JOIN N3 AS B)
	, D AS
	(
		SELECT	0 AS n
		UNION ALL
		SELECT	ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS n
		FROM N4
	)
INSERT	INTO t_jour
(
	jour_date
)
SELECT	DATEADD(day, n, '20000101')
FROM	D

Ceci génère toutes les dates de tous les jours depuis le 1er Janvier 2000 jusqu'à 65536 jours ensuite. Dès lors la requête serait :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT		TC.DateCommande
		, JO.jour_date AS prochainJourOuvrable
FROM		TableCommandes  AS TC
CROSS APPLY	(
			SELECT	TOP 1 J.jour_date
			FROM	dbo.t_jour AS J
			WHERE	J.is_ouvre = 1
			AND	TC.DateCommande < J.jour_date
			ORDER	BY J.jour_date
		) AS JO

Ou si vous préférez :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT		TC.DateCommande
		, (
			SELECT	TOP 1 J.jour_date
			FROM	dbo.t_jour AS J
			WHERE	J.is_ouvre = 1
			AND	TC.DateCommande < J.jour_date
			ORDER	BY J.jour_date
		) AS prochainJourOuvrable
FROM		TableCommandes AS TC

Dans les deux cas, cela génère beaucoup d'IOs. On peut donc stocker tous les jours, en ajoutant une clause INTO juste avant le FROM avec le nom de la table cible à la requête suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT		F.jour_date
		, JO.jour_date AS ouvre
FROM		dbo.t_jour AS F
CROSS APPLY	(
			SELECT	TOP 1 J.jour_date
			FROM	dbo.t_jour AS J
			WHERE	J.is_ouvre = 1
			AND	F.jour_date < J.jour_date
			ORDER	BY J.jour_date
		) AS JO

Ceci permet alors d'effectuer une jointure aussi simplement que vous l'avez exprimée dans votre première requête, et de retrouver dans cette nouvelle table le jour ouvrable suivant directement.

@++

[cerede2000]

Merci elsuket mais tu ne prend en compte ici que les weekends.

Hors dans l'ERP les responsables de productions viennent rajouter en plus des jours fériés d'autres jours de fermeture qui sont alors non ouvrés.

Cela peux evolué au cours de l'année et ça change forcément tout les ans.
Il faut donc impérativement que je me base sur cette table.

[EddiGordo]

Bonjour,

Merci elsuket mais tu ne prend en compte ici que les weekends.

Hors dans l'ERP les responsables de productions viennent rajouter en plus des jours fériés d'autres jours de fermeture qui sont alors non ouvrés.

Cela peux evolué au cours de l'année et ça change forcément tout les ans.
Il faut donc impérativement que je me base sur cette table.

Dans ce cas, il te suffit d'enlever de la table les jours en question.
Pour cela modifie un peu la procédure créée par elsuket.:

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
CREATE TABLE t_jour
(
	jour_date date NOT NULL
		CONSTRAINT PK_t_jour_ouvre PRIMARY KEY
	, is_ouvre AS(CASE WHEN DATENAME(weekday, jour_date) IN ('Saturday', 'Sunday') THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END)
)
GO
 
WITH
	N AS(SELECT NULL AS v UNION ALL SELECT NULL)
	, N1 AS (SELECT A.v FROM N AS A CROSS JOIN N AS B)
	, N2 AS (SELECT A.v FROM N1 AS A CROSS JOIN N1 AS B)
	, N3 AS (SELECT A.v FROM N2 AS A CROSS JOIN N2 AS B)
	, N4 AS (SELECT A.v FROM N3 AS A CROSS JOIN N3 AS B)
	, D AS
	(
		SELECT	0 AS n
		UNION ALL
		SELECT	ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS n
		FROM N4
	)
INSERT	INTO t_jour
(
	jour_date
)
SELECT	DATEADD(day, n, '20000101')
FROM	D

Delete From t_Jour
Where jour_date In (Select [leChampDate] From [laTableDesJoursNonOuvres])

[cerede2000]

Ce qui m'embête avec ce fonctionnement c'est qu'il faut intervenir tous les ans, car les jours non ouvrés sont déclarés en fin d'année tous les ans

Je propose ceci :

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
CREATE FUNCTION [dbo].[GetWorkingDay]
(
	@date_init date
   ,@delai int = 0
)
RETURNS date
AS
BEGIN
	DECLARE @NewDate AS date
	DECLARE @sign AS int
 
	SET @sign = SIGN(@delai)
 
	-- Si la date passé en paramètre est un JF alors on recherche la date SUIVANTE qui est ouvrée
	WHILE (SELECT 1 FROM [dbo].[CALEND_DAY] WHERE [Cal_Code] = 'Calendrier général' AND [Calday_date] = @date_init) IS NOT NULL BEGIN
		SET @date_init = DATEADD(DD, 1, @date_init)
	END
 
	SET @NewDate = @date_init
 
	-- Tant que delai n'est pas à 0 on retire un jour, si c'est un jour férié on ne le retire pas de délai
	WHILE @delai != 0 BEGIN
		IF (SELECT 1 FROM [dbo].[CALEND_DAY] WHERE [Cal_Code] = 'Calendrier général' AND [Calday_date] = @NewDate) IS NOT NULL BEGIN
			SET @NewDate = DATEADD(DD, @sign, @NewDate)
		END
		ELSE BEGIN
			SET @NewDate = DATEADD(DD, SIGN(@delai), @NewDate)
			SET @delai += -@sign
		END
	END
 
	-- Si la date final trouvé est un JF alors on recherche la date précédente ou suivante (fonction du signe du délai) qui est ouvrée
	WHILE (SELECT 1 FROM [dbo].[CALEND_DAY] WHERE [Cal_Code] = 'Calendrier général' AND [Calday_date] = @NewDate) IS NOT NULL BEGIN
		SET @NewDate = DATEADD(DD, @sign, @NewDate)
	END
 
	RETURN @NewDate
END

Qu'en pensez vous ?

[elsuket]

Bonjour,

e qui m'embête avec ce fonctionnement c'est qu'il faut intervenir tous les ans, car les jours non ouvrés sont déclarés en fin d'année tous les ans

Je comprends cela tout à fait; néanmoins si les jours fériés non-fixes, ou les RTT "imposés" par l'entreprise varient, il suffit de remplir la table en conséquence tous les ans : c'est d'ailleurs le but que la table que vous avez doit servir.
La fonction que vous donnez ne permet pas non plus de savoir si un jour ouvré non-fixe l'est.

Pour les weekend, effectivement je n'ai pas tenu compte des autres jours fériés, puisqu'ils sont non-fixes et varient suivant les pays.
J'ai pris pour exemple ceux-ci, mais comme EddiGordo vous en faisait part, vous pouvez adapter votre table calendrier à vos besoins.

@++

[aieeeuuuuu]

Je propose ceci :

Qu'en pensez vous ?

Que comme bien souvent, il convient d'éviter les SELECT dans des boucles.

Vous pouvez en effet rester sur une CTE récursive :

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 
		@date_init as dte
		,COALESCE( 
				(SELECT 0
				 FROM  CALEND_DAY 
				 WHERE [Cal_Code] = 'Calendrier général' 		
				 AND [Calday_date] = @date_init
				 )
				, 1
			) d
	UNION ALL
	SELECT 
		DATEADD(DAY, COALESCE(NULLIF(SIGN(@delai),0),1), dte)
		, CTE.d 
			+ COALESCE( 
				(SELECT 0
				 FROM  CALEND_DAY 
				 WHERE [Cal_Code] = 'Calendrier général' 		
				 AND [Calday_date] = DATEADD(DAY, COALESCE(NULLIF(SIGN(@delai),0),1), dte)
				 )
				, 1
			)
	FROM CTE
 
	WHERE  CTE.d <= ABS(@delai) 
 
)
SELECT *--CASE WHEN @delai > 0 THEN MAX(dte) ELSE MIN(dte) END
FROM CTE
;

[cerede2000]

Great !!!!

Super résultat avec ce CTE

Merci beaucoup !

Difficile à maîtriser les CTE quand on en fait pas beaucoup

Petit ajustement si c'est possible, lorsque le délais est à 0, est il possible de trouver quand même le prochain jour ouvré ?

[aieeeuuuuu]

Il faut modifier l'expression CASE de la requete qui est erronée et mettre un >=

Code :

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

CASE WHEN @delai >= 0 THEN MAX(dte) OVER() ELSE MIN(dte) OVER() END

.

si vous voulez chercher le jour suivant lorque @delai = 0 même si le @date_init est un jour ouvré, alors le plus simple est certainement de traiter ce cas en avant la requete, en mettant tout simplement @delai à 1 s'il vaut 0, ce qui permet par ailleurs de simplifier la requete qui n'aura plus à traiter ce cas particulier.