Discussion :

[SQL_i]

Bonjour,

Il s'agit d'une problématique que nous rencontrons souvent en entreprise et dont le calcul me semble compliqué sans table de référence.
La question porte sur la méthode à utiliser pour pouvoir calculer une différence en heures entre deux dates mais en tenant compte seulement des horaires ouvertures.

Exemple :
date_deb_activité : 07/09/2010 14h:00
date_fin_activité le 09/09/2011 14h:00
sachant que les horaires d'ouvertures sont : 08h - 18h
et les jours ouvrés lundi au samedi

dans l'exemple au dessus : le total à trouver est de
4h+8h+6h=18h

Pouvez vous svp m'aider à rédiger la requête sous sql ?
Merci beaucoup

[tedo01]

Bonjour,

La durée de travail du premier jour est

Code :

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

18 - to_char(date_deb, 'hh24') - to_char(date_deb, 'mi')/60

Celle du dernier jour est

Code :

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

(to_char(date_fin, 'hh24') + to_char(date_fin, 'mi')/60) - 8

Et si des jours sont travaillés entre, cela donne (toujours en heures)

Code :

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

10 * (trunc(date_fin) - trunc(date_deb) - 1)

Ce qui donne un total de

Code :

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

1
2
3
  18 - to_char(date_deb, 'hh24') - to_char(date_deb, 'mi')/60 
+ (to_char(date_fin, 'hh24') + to_char(date_fin, 'mi')/60) - 8
+ 10 * (trunc(date_fin) - trunc(date_deb) - 1)

[SQL_i]

Super ..fonctionnement parfait
Un grand merci

[Mohamed.Houri]

Super ..fonctionnement parfait
Un grand merci

Il me semble que cela ne tienne pas compte de toutes les possibilités. Je pense à une date de fin se trouvant le dimanche ou après dimanche. Je pense également au jours fériés

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_date (start_date date, end_date date);
insert into t_date values ( to_date('07092010 14:00:00','ddmmyyyy hh24:mi:ss')
                           ,to_date('09092010 14:00:00','ddmmyyyy hh24:mi:ss')
                           );
commit;
 
select 
    case 
      when trunc(start_date) = trunc(end_date)  
       then 
           to_char(end_date,'HH24') - to_char(start_date,'HH24')
      else 
           10 + to_char(end_date,'HH24') - to_char(start_date,'HH24')
              + 8 * round(end_date - start_date - 1) 
    end
  -
    case
      when next_day(trunc(start_date),'SUN') = trunc(start_date) 
      then 18 - to_char(start_date,'HH24')
      when next_day(trunc(start_date),'SUN') < trunc(end_date) 
      then 8
      when next_day(trunc(start_date),'SUN') = trunc(end_date) 
      then to_char(end_date,'HH24') - 8
      else 0
    end  X
from t_date;

[tedo01]

Bonjour,

Bonne remarque pour les dimanches, Mohamed, mais ton code est faux et ne tient compte que des heures entières.

Il faut retrancher de ma formule le nombre de dimanches entre date_deb et date_fin, qui vaut 1 + le nombre de semaines entières entre date_fin et le prochain dimanche après date_deb si le prochain dimanche après date_deb est avant date_fin, 0 sinon :

Code :

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

1
2
3
(CASE WHEN TRUNC(NEXT_DAY(date_deb - 1,'SUN')) >= TRUNC(date_fin) THEN 0
      ELSE 1 + TRUNC( ( TRUNC(date_fin) - TRUNC(NEXT_DAY(date_deb - 1,'SUN') ) ) / 7 )
 END)

Je suis partie de l'hypothèse que les date_deb et date_fin étaient en période d'activité... mais on peut étendre aux cas où l'activité démarre et/ou arrête un dimanche ou la nuit.
Si on veut calculer avec des début et fin en dehors des périodes d'activité, la durée de travail du premier jour devient nulle si c'est un dimanche :

Code :

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

1
2
3
(CASE WHEN TO_CHAR(date_deb,'D') = 1 THEN 0 
      ELSE 18 - to_char(date_deb, 'hh24') - to_char(date_deb, 'mi')/60
 END)

De la même manière, la durée de travail du dernier jour s'annule si c'est un dimanche :

Code :

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

1
2
3
(CASE WHEN TO_CHAR(date_fin,'D') = 1 THEN 0 
      ELSE (to_char(date_fin, 'hh24') + to_char(date_fin, 'mi')/60) - 8
 END)

En recollant les morceaux, et avec quelques exemples, on obtient :

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
WITH t_date AS
(
          SELECT TO_DATE('01.09.2011 09:30','DD.MM.YYYY HH24:MI')date_deb, TO_DATE('01.09.2011 15:00','DD.MM.YYYY HH24:MI') date_fin FROM dual
UNION ALL SELECT TO_DATE('01.09.2011 14:00','DD.MM.YYYY HH24:MI'), TO_DATE('02.09.2011 10:00','DD.MM.YYYY HH24:MI') FROM DUAL
UNION ALL SELECT TO_DATE('01.09.2011 14:00','DD.MM.YYYY HH24:MI'), TO_DATE('05.09.2011 10:00','DD.MM.YYYY HH24:MI') FROM DUAL
UNION ALL SELECT TO_DATE('02.09.2011 14:00','DD.MM.YYYY HH24:MI'), TO_DATE('12.09.2011 14:00','DD.MM.YYYY HH24:MI') FROM DUAL
UNION ALL SELECT TO_DATE('04.09.2011 14:00','DD.MM.YYYY HH24:MI'), TO_DATE('12.09.2011 14:00','DD.MM.YYYY HH24:MI') FROM DUAL
)                           
 
SELECT
date_deb, 
date_fin,
-- durée de travail du premier jour
(CASE WHEN TO_CHAR(date_deb,'D') = 1 THEN 0 
      ELSE 18 - TO_CHAR(date_deb, 'hh24') - TO_CHAR(date_deb, 'mi')/60
 END) 
-- durée de travail du dernier jour
+ (CASE WHEN TO_CHAR(date_fin,'D') = 1 THEN 0 
      ELSE (TO_CHAR(date_fin, 'hh24') + TO_CHAR(date_fin, 'mi')/60) - 8
 END)
-- durée correspondant à des jours entiers
+ 10 * 
(
  -- nombre de jours entiers entre le début et la fin
  TRUNC(date_fin) - TRUNC(date_deb) - 1 
-
  --nombre de dimanches dans l'intervalle 
 (CASE WHEN TRUNC(NEXT_DAY(date_deb - 1,'SUN')) >= TRUNC(date_fin) THEN 0
       ELSE 1 + TRUNC( ( TRUNC(date_fin) - TRUNC(NEXT_DAY(date_deb - 1,'SUN') ) ) / 7 )
  END) 
)  duree
FROM t_date

SQL_I, est-ce que ça correspond à ce que tu attends ?

[SQL_i]

Bonjour,
Effectivement, ceci répond en grande partie à ma problématique
par contre, concernant les jours fériés et dimanche, je me base sur une table temporaire pour ne pas les prendre en compte ( un pré calcul dans un With qui permet de calculer le nombre de jours ouvrés entre la date debut et date de fin)

il reste une question concernant :

-- durée correspondant à des jours entiers
+ 10 *
(
-- nombre de jours entiers entre le début et la fin
TRUNC(date_fin) - TRUNC(date_deb) - 1

si la date début = date fin j'obtiens (-10 ), or dans ce cas, il faut avoir 0
dois je gérer deux cas ( date deb=date fin) et date fin > date deb) ?
Merci

[SQL_i]

Bonjour,
Pour répondre à la dernière question, j'ai ajouté un case when pour tenir compte du cas de l’égalité des des deux dates (JJ/MM/YYYY)
Cordialement,

[tedo01]

Bonjour,
Si les deux dates sont égales, il faut bien enlever 10 à la somme des durées de travail du premier jour et du dernier jour, sinon le résultat est faux.
Par exemple, pour une plage de 10h à 12h dans une journée, on a
- durée 1er jour = 18-10 = 8h
- durée 2e jour = 12-8 = 4h
Total = 8 + 4 - 10 = 2