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 : 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
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