Calcul du nombre de jours non ouvrés entre deux dates [Sources]

**allweneed** · 22/04/2010, 16h45

Bonjour,

Dans le cadre de mon travail, on m'a demandé de calculer le nombre de jours non ouvrés entre deux dates, en tenant compte à la fois des week-ends, mais aussi des jours fériés. Les recherches que j'ai faites sur internet ne m'ont fournies que des solutions en PL/SQL qui ne me convenaient pas. Je viens donc ici pour vous proposer la solution que j'ai écrite en SQL.

Au cas où qqn serait confronté au même problème, j'espère pour lui qu'il tombera sur cette solution

Le résultat est un nombre décimal car une date de début ou de fin peut très bien tomber au milieu d'un week-end ou d'un jour férié.

Avec cette requête, il est également aisé de calculer le nombre de jours ouvrés en faisant TEMP.DATEFIN - TEMP.DATEDEBUT - JOURS_NON_OUVRES.

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
WITH
  TEMP AS
    (
      SELECT TO_DATE('10/03/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEDEBUT, TO_DATE('11/03/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEFIN FROM DUAL
      UNION ALL
      SELECT TO_DATE('10/03/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEDEBUT, TO_DATE('13/03/2010 12:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEFIN FROM DUAL
      UNION ALL
      SELECT TO_DATE('10/03/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEDEBUT, TO_DATE('14/03/2010 18:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEFIN FROM DUAL
      UNION ALL
      SELECT TO_DATE('10/03/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEDEBUT, TO_DATE('15/03/2010 12:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEFIN FROM DUAL
      UNION ALL
      SELECT TO_DATE('13/03/2010 12:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEDEBUT, TO_DATE('14/03/2010 12:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEFIN FROM DUAL
      UNION ALL
      SELECT TO_DATE('14/03/2010 12:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEDEBUT, TO_DATE('31/03/2010 06:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEFIN FROM DUAL
      UNION ALL
      SELECT TO_DATE('12/03/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEDEBUT, TO_DATE('25/03/2010 12:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEFIN FROM DUAL
      UNION ALL
      SELECT TO_DATE('14/03/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEDEBUT, TO_DATE('27/03/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEFIN FROM DUAL
      UNION ALL
      SELECT TO_DATE('08/03/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEDEBUT, TO_DATE('22/03/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEFIN FROM DUAL
      UNION ALL
      SELECT TO_DATE('12/03/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEDEBUT, TO_DATE('19/03/2010 12:30:59', 'DD/MM/YYYY HH24:MI:SS') AS DATEFIN FROM DUAL
      UNION ALL
      SELECT TO_DATE('01/05/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEDEBUT, TO_DATE('01/06/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEFIN FROM DUAL
      UNION ALL
      SELECT TO_DATE('13/05/2010 06:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEDEBUT, TO_DATE('24/05/2010 18:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEFIN FROM DUAL
      UNION ALL
      SELECT TO_DATE('22/12/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEDEBUT, TO_DATE('31/12/2010 12:00:00', 'DD/MM/YYYY HH24:MI:SS') AS DATEFIN FROM DUAL
    ),
 
  FERIE AS
    (
      SELECT TO_DATE('01/01/2010', 'DD/MM/YYYY') AS JOUR FROM DUAL
      UNION ALL
      SELECT TO_DATE('05/04/2010', 'DD/MM/YYYY') AS JOUR FROM DUAL
      UNION ALL
      SELECT TO_DATE('01/05/2010', 'DD/MM/YYYY') AS JOUR FROM DUAL
      UNION ALL
      SELECT TO_DATE('13/05/2010', 'DD/MM/YYYY') AS JOUR FROM DUAL
      UNION ALL
      SELECT TO_DATE('24/05/2010', 'DD/MM/YYYY') AS JOUR FROM DUAL
      UNION ALL
      SELECT TO_DATE('21/07/2010', 'DD/MM/YYYY') AS JOUR FROM DUAL
      UNION ALL
      SELECT TO_DATE('15/08/2010', 'DD/MM/YYYY') AS JOUR FROM DUAL
      UNION ALL
      SELECT TO_DATE('01/11/2010', 'DD/MM/YYYY') AS JOUR FROM DUAL
      UNION ALL
      SELECT TO_DATE('11/11/2010', 'DD/MM/YYYY') AS JOUR FROM DUAL
      UNION ALL
      SELECT TO_DATE('25/12/2010', 'DD/MM/YYYY') AS JOUR FROM DUAL
    )
 
SELECT
  TEMP.DATEDEBUT AS DATE_DEBUT,
  TEMP.DATEFIN AS DATE_FIN,
 
  --Nombre de samedis et dimanches des semaines complètes qu'il est possible de former entre les 2 dates.
  FLOOR((TEMP.DATEFIN - TEMP.DATEDEBUT) / 7) * 2
 
  --Durée en nombre de jours du premier week-end de la semaine restante.
  + GREATEST(LEAST(NEXT_DAY(TRUNC(TEMP.DATEDEBUT, 'DAY'), 'LUNDI'), TEMP.DATEFIN - (FLOOR((TEMP.DATEFIN - TEMP.DATEDEBUT) / 7) * 7)) - GREATEST(NEXT_DAY(TRUNC(TEMP.DATEDEBUT, 'DAY'), 'SAMEDI'), TEMP.DATEDEBUT), 0)
 
  --Durée en nombre de jours du deuxième week-end de la semaine restante.
  --Exemple : ce cas peut se présenter si la semaine restante commence le samedi 10/04/2010 à 22:00, et se termine le samedi 17/04/2010 à 08:00.
  + GREATEST(LEAST(NEXT_DAY(TRUNC(TEMP.DATEDEBUT + INTERVAL '7' DAY, 'DAY'), 'LUNDI'), TEMP.DATEFIN - (FLOOR((TEMP.DATEFIN - TEMP.DATEDEBUT) / 7) * 7)) - GREATEST(NEXT_DAY(TRUNC(TEMP.DATEDEBUT + INTERVAL '7' DAY, 'DAY'), 'SAMEDI'), TEMP.DATEDEBUT), 0)
 
  --Nombre de jours fériés qui ne sont ni un samedi ni un dimanche (car déjà comptabilisés).
  + NVL(SUM(GREATEST(LEAST(TRUNC(JOUR + INTERVAL '1' DAY, 'DD'), TEMP.DATEFIN) - GREATEST(TRUNC(JOUR, 'DD'), TEMP.DATEDEBUT), 0)), 0)
 
  AS JOURS_NON_OUVRES
FROM
  TEMP
  LEFT OUTER JOIN FERIE ON TO_CHAR(JOUR, 'D') NOT IN (6, 7)
GROUP BY
  TEMP.DATEDEBUT,
  TEMP.DATEFIN

Si ce code vous a été utile, un petit merci ferait plaisir à lire

Bien à vous

PS : si qqn sait comment éviter tous les UNION ALL que j'ai placé pour remplir les tables temporaires, je suis aussi preneur.

**Waldar** · 22/04/2010, 17h45

C'est toujours bon à prendre.

De mon côté j'ai rempli une table calendrier sur une cinquantaine d'années, et j'ai mis à jour les jours fériés avec un simple update.
J'ai accessoirement d'autre information relatives aux dates fiscales et aux clotures comptables, ça me permet de répondre à ce genre de demande avec une simple requête.

**allweneed** · 26/04/2010, 10h22

Envoyé par Waldar

De mon côté j'ai rempli une table calendrier sur une cinquantaine d'années, et j'ai mis à jour les jours fériés avec un simple update.

C'est aussi une bonne solution, c'est sans doute moins coûteux en temps d'exécution. J'imagine que ça équivaut plus ou moins à ne garder que le dernier bloc de la clause SELECT, qui se trouve dans la fonction NVL ... A moins de ne comptabiliser que des jours entiers, auquel cas c'est encore plus simple et plus rapide.

Pour info, j'ai fait une petite mise à jour du code car il y avait encore moyen de simplifier l'écriture ainsi que le temps d'exécution.

**Waldar** · 26/04/2010, 11h51

Le plus long à faire c'est de bien compter les parties non-entières.

Avec une table calendrier ça donne 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
with temp as
(
select 1 as nm, to_date('10/03/2010', 'DD/MM/YYYY') as dtdeb, to_date('11/03/2010', 'DD/MM/YYYY') as dtfin from dual union all
select 2      , to_date('10/03/2010 00', 'DD/MM/YYYY HH24') , to_date('13/03/2010 12', 'DD/MM/YYYY HH24')  from dual union all
select 3      , to_date('10/03/2010 00', 'DD/MM/YYYY HH24') , to_date('14/03/2010 18', 'DD/MM/YYYY HH24')  from dual union all
select 4      , to_date('10/03/2010 00', 'DD/MM/YYYY HH24') , to_date('15/03/2010 12', 'DD/MM/YYYY HH24')  from dual union all
select 5      , to_date('13/03/2010 12', 'DD/MM/YYYY HH24') , to_date('14/03/2010 12', 'DD/MM/YYYY HH24')  from dual union all
select 6      , to_date('14/03/2010 12', 'DD/MM/YYYY HH24') , to_date('31/03/2010 06', 'DD/MM/YYYY HH24')  from dual union all
select 7      , to_date('12/03/2010 00', 'DD/MM/YYYY HH24') , to_date('25/03/2010 12', 'DD/MM/YYYY HH24')  from dual union all
select 8      , to_date('14/03/2010 00', 'DD/MM/YYYY HH24') , to_date('27/03/2010 00', 'DD/MM/YYYY HH24')  from dual union all
select 9      , to_date('08/03/2010 00', 'DD/MM/YYYY HH24') , to_date('22/03/2010 00', 'DD/MM/YYYY HH24')  from dual union all
select 10     , to_date('12/03/2010 00', 'DD/MM/YYYY HH24') , to_date('19/03/2010 12:30:59', 'DD/MM/YYYY HH24:MI:SS') from dual union all
select 11     , to_date('01/05/2010 00', 'DD/MM/YYYY HH24') , to_date('01/06/2010 00', 'DD/MM/YYYY HH24')  from dual union all
select 12     , to_date('13/05/2010 06', 'DD/MM/YYYY HH24') , to_date('24/05/2010 18', 'DD/MM/YYYY HH24')  from dual union all
select 13     , to_date('22/12/2010 00', 'DD/MM/YYYY HH24') , to_date('31/12/2010 12', 'DD/MM/YYYY HH24')  from dual
)
  select tp.nm, tp.dtdeb, tp.dtfin,
         sum(
         case
           when cl.day_number in (6, 7)
             or cl.fg_holiday = 1
           then 1 else 0
         end -
         case
           when (cl.day_number in (6, 7) or cl.fg_holiday = 1)
            and trunc(tp.dtfin) = cl.day
           then 1 - (tp.dtfin - trunc(tp.dtfin))
           else 0
         end -
         case
           when (cl.day_number in (6, 7) or cl.fg_holiday = 1)
            and trunc(tp.dtdeb) = cl.day
           then tp.dtdeb - trunc(tp.dtdeb)
           else 0
         end
         ) as jours_non_ouvres
    from temp tp
         inner join calendar cl
           on cl.day between trunc(tp.dtdeb)
                         and trunc(tp.dtfin)
group by tp.nm, tp.dtdeb, tp.dtfin
order by tp.nm asc;
 
NM	DTDEB			DTFIN			JOURS_NON_OUVRES
1	10/03/2010		11/03/2010		0
2	10/03/2010		13/03/2010 12:00:00	0.5
3	10/03/2010		14/03/2010 18:00:00	1.75
4	10/03/2010		15/03/2010 12:00:00	2
5	13/03/2010 12:00:00	14/03/2010 12:00:00	1
6	14/03/2010 12:00:00	31/03/2010 06:00:00	4.5
7	12/03/2010		25/03/2010 12:00:00	4
8	14/03/2010		27/03/2010		3
9	08/03/2010		22/03/2010		4
10	12/03/2010		19/03/2010 12:30:59	2
11	01/05/2010		01/06/2010		12
12	13/05/2010 06:00:00	24/05/2010 18:00:00	5.5
13	22/12/2010		31/12/2010 12:00:00	2

**allweneed** · 26/04/2010, 16h38

Sympa d'avoir posté ta solution

C'est bien ainsi que je le voyais, sauf que je n'aurais pas utilisé les CASE.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH ...
 
SELECT 
  TP.NM, TP.DTDEB, TP.DTFIN,
  SUM(LEAST(TRUNC(CL.DAY + INTERVAL '1' DAY, 'DD'), TP.DTFIN) - GREATEST(TRUNC(CL.DAY, 'DD'), TP.DTDEB)) AS JOURS_NON_OUVRES
FROM 
  TEMP TP
INNER JOIN CALENDAR CL
  ON CL.DAY BETWEEN TRUNC(TP.DTDEB)AND TRUNC(TP.DTFIN) AND
     (CL.DAY_NUMBER IN (6, 7) OR CL.FG_HOLIDAY = 1)
GROUP BY 
  TP.NM, TP.DTDEB, TP.DTFIN
ORDER BY 
  TP.NM ASC

L'inconvénient, c'est que le SUM ne retourne pas de ligne si il n'y a pas de jours non ouvré, et qu'on ne sait pas faire un MAX(SUM()).

Donc au final, il vaut certainement mieux le faire tel que tu l'as fait.

**Waldar** · 26/04/2010, 16h48

Il suffit de faire une jointure externe :

Code :

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

1
2
3
4
5
6
7
8
  SELECT TP.NM, TP.DTDEB, TP.DTFIN,
         COALESCE(SUM(LEAST(CL.DAY + 1, TP.DTFIN) - GREATEST(CL.DAY, TP.DTDEB)), 0) AS JOURS_NON_OUVRES
    FROM TEMP TP
         LEFT OUTER JOIN CALENDAR CL
           ON CL.DAY BETWEEN TRUNC(TP.DTDEB) AND TRUNC(TP.DTFIN)
          AND (CL.DAY_NUMBER IN (6, 7) OR CL.FG_HOLIDAY = 1)
GROUP BY TP.NM, TP.DTDEB, TP.DTFIN
ORDER BY TP.NM ASC;

Celà dit j'aime bien votre méthode pour les journées partielles, ça s'applique aussi à la solution avec la jointure forte :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
  SELECT tp.nm, tp.dtdeb, tp.dtfin,
         sum(
         case
           when cl.day_number IN (6, 7) OR cl.fg_holiday = 1
           then LEAST(CL.DAY + 1, TP.DTFIN) - GREATEST(CL.DAY, TP.DTDEB)
           else 0
         end)  AS jours_non_ouvres
    FROM temp tp
         INNER JOIN calendar cl
           ON cl.day BETWEEN trunc(tp.dtdeb) AND trunc(tp.dtfin)
GROUP BY tp.nm, tp.dtdeb, tp.dtfin
ORDER BY tp.nm ASC;

J'ai un peu simplifié les opérateurs au passage.