SQL calcul d'heures de travail + jours feries + congés

[cedricmail]

Bonjour,

je dois calculer le nombre d'heures de travail(8h30-17h) qui sont effectuées entre 2 dates, en tenant compte des jours feries et congés (table a part).

Sachant qu'une date peut commencer et se finir en dehors des heures de travail, je suis arrivé à un (long) code comme ceci:

Code :

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
CASE WHEN  DATEDIFF(hh,@vStartDate ,@vEndDate)<= 24 AND DATEPART(DAY,@vStartDate) != DATEPART(DAY,@vEndDate) THEN
					DATEDIFF(hh,@vStartDate ,@vEndDate)- 15.5
			ELSE 
	(DATEDIFF(d,@vStartDate,@vEndDate) --Get the number of days between start and end dates
		- DATEDIFF(wk,@vStartDate,@vEndDate) * 2 -- for each week, subtract 2 days (by default a week occurs between sat and sunday on sql server)
		- CASE 
			--WHEN DATENAME(dw, @vStartDate) <> 'Saturday' AND DATENAME(dw, @vEndDate) = 'Saturday' 
			--	THEN 1 --subtract 1 day if the end date falls on a saturday and the startdate is a weekday, or sunday 
			WHEN DATENAME(dw, @vStartDate) = 'Sunday' AND  DATENAME(dw, @vEndDate) = 'Sunday'
				THEN 1 	
			WHEN DATENAME(dw, @vStartDate) = 'Saturday' AND  DATENAME(dw, @vEndDate) = 'Saturday'
				THEN 1 
			WHEN DATENAME(dw, @vStartDate) = 'Saturday' AND  DATENAME(dw, @vEndDate) = 'Sunday'
				THEN 0 
			WHEN DATENAME(dw, @vStartDate) = 'Sunday' AND  DATENAME(dw, @vEndDate) = 'Saturday'
				THEN 2 	
			WHEN DATENAME(dw, @vStartDate) = 'Saturday' 
				THEN 0 	
			WHEN DATENAME(dw, @vEndDate) = 'Sunday' 
				THEN -1 			
			WHEN DATENAME(dw, @vStartDate) = 'Sunday' 
				THEN 1
			WHEN DATENAME(dw, @vEndDate) = 'Saturday' 
				THEN 1 
 
			ELSE 0 
		END
		 - (SELECT COUNT(DATEPART(weekday,datum)) 
					  FROM W_FAC_VAKANTIEDAGEN
					  WHERE DATUM BETWEEN @vStartDate AND @vEndDate
				)
		)*8.5
 
			- CASE 
				WHEN DATENAME(dw, @vStartDate) <>  'Sunday' AND DATENAME(dw, @vStartDate) <>  'Saturday'
					THEN (DATEPART(hh,@vStartDate) - 8.5)
					ELSE 0
				END
 
			- CASE 
				WHEN DATENAME(dw, @vEndDate) =  'Saturday'
					THEN -8.5 --ads the 8.5h from friday
				WHEN DATENAME(dw, @vEndDate) =  'Sunday'
					THEN 0	
				ELSE 
					-(DATEPART(hh,@vEndDate)-8.5)    --if weekday, calculates the hours worked during the last day
				END 
 
			- CASE 
					WHEN DATENAME(dw, @vStartDate) =  'Saturday' OR DATENAME(dw, @vStartDate) =  'Sunday'
						THEN 0
					WHEN DATEPART(hh,@vStartDate) <= 8 AND DATEPART(n,@vStartDate) <=30
						THEN 	(8.5 - DATEPART(hh,@vStartDate))	--if start time <8h30 then add the difference (ex: start: 6h then it adds 2h30)
					ELSE 0
				END
			- CASE 
					WHEN DATENAME(dw, @vEndDate) =  'Saturday' OR DATENAME(dw, @vEndDate) =  'Sunday'
						THEN 0
					WHEN DATEPART(hh,@vEndDate) > 17
						THEN 	(DATEPART(hh,@vEndDate) - 17)
					ELSE 0
				END
 
			- CASE
					WHEN EXISTS (SELECT *
								FROM W_FAC_VAKANTIEDAGEN
								WHERE YEAR(DATUM) = YEAR(@vStartDate) AND MONTH(DATUM) = MONTH(@vStartDate) AND DAY(DATUM) = DAY(@vStartDate)
								AND DATEPART(weekday, @vStartDate) >1 AND DATEPART(weekday, @vStartDate) <7
								)	  
					THEN 
						CASE
							WHEN YEAR(@vEndDate) = YEAR(@vStartDate) AND MONTH(@vEndDate) = MONTH(@vStartDate) AND DAY(@vEndDate) = DAY(@vStartDate)
							THEN (DATEPART(hh,@vEndDate) - DATEPART(hh,@vStartDate))-1
							ELSE
								CASE
									WHEN DATEPART(hh,@vStartDate) < 17
									THEN (17 - DATEPART(hh,@vStartDate))
									ELSE 0
								END
						END
					ELSE 0	
				END		
END

qui commence à etre pas mal complexe.

Je ne peut pas utiliser de fonction ni de variable (sauf temporairement pour faire un test avec plein de dates).

Avez vous deja fait ca? Le code fonctionne mais donne des réponses exotiques qd par exemple on commence et fini pendant un jour ferie, fini un jour ferié etc...

Merci beaucoup de votre aide!!

Ced

[elsuket]

Bonjour,

Le plus simple dans ce cas est de créer une table de dates, dans laquelle vous marquez les jours fériés par une colonne de type bit.
Vous avez aussi le choix de créer une table qui stocke tous les types de jours dont votre métier a besoin, et marquer les jours fériés / ouvrables / ... à l'aide de cette table.
Le code est alors d'une simplicité enfantine, et le remplissage de la table de dates se fait à l'aide d'un jeu de procédures stockées qui vous marquent les jours fériés fixes (comme le 1er mai ou le 14 juillet) et les jours fériés "volants" comme Pâques

@++

[SQLpro]

Regardez la solution de calendrier dans mon article... Vous y trouverez tout ce que vous voulez : http://sqlpro.developpez.com/cours/gestiontemps/
jour fériés fixes et variables, dates de congés et heures d'ouverture fermeture.
reste plus qu'a faire de JOIN.

A +