Calendrier avec jours fériés

[Waldar]

Sujet récurrent, je n'avais jamais pris le temps d'écrire un sujet correct, chose que je rectifie maintenant.

Je proposer un calendrier, assez simple, qui gère les jours des XX et XXIème siècles, ainsi que les jours fériés. Ce calendrier n'est utile que pour des extractions.

Ce qui est proposé n'est pas une solution mondiale avec gestion de toutes les zones géographique.
C'est développé pour les données traitées en France métropolitaine, donc des fêtes civiles et religieuses. Et encore, certains jours (cf. lundi de Pentecôte) sont chômés ou travaillées selon les entreprises.

Néanmoins, c'est adaptable.
Si vous désirez un modèle plus complet, je vous renvoie vers l'article de SQLPro, axé lui autour de Microsoft SQL-Server.

La solution a été développée autour d'Oracle Database 11gR1 en utilisant des colonnes virtuelles.
C'est transposable aux versions précédentes.

Ce calendrier est articulé autour de deux tables : une pour enregistrer les jours spéciaux, une autre pour enregistrer tous les jours du 1er janvier 1900 au 31 décembre 2099.

Table des fêtes

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
ALTER session SET nls_date_language = 'French';
 
CREATE TABLE calendrier_fetes
(
    clf_jour_fetes  number(5)    NOT NULL
  , clf_description varchar2(50) NOT NULL
  , clf_fixe        varchar2(5)      NULL
  , clf_chome       number(1)    NOT NULL
  , constraint pk_calendrier_fetes
      PRIMARY KEY (clf_jour_fetes)
      USING INDEX
  , constraint ck_calendrier_fetes_chome
      CHECK (clf_chome IN (0, 1))
  , constraint ck_calendrier_fetes_fixe      -- Vérification du format fixe "mm-dd"
      CHECK (to_date('1900-' || coalesce(clf_fixe, '01-01'), 'yyyy-mm-dd') >= date '1900-01-01')
);
 
CREATE sequence seq_calendrier_fetes;
 
CREATE TRIGGER tbi_calendrier_fetes
before INSERT ON calendrier_fetes
FOR each row
declare
begin
    :new.clf_jour_fetes := seq_calendrier_fetes.NEXTVAL;
end;
/
 
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Jour de l''an'       , '01-01', 1);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Épiphanie'           , '01-06', 0);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Chandeleur'          , '02-02', 0);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Saint-Valentin'      , '02-14', 0);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Mardi Gras'          , NULL   , 0);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Mercredi des cendres', NULL   , 0);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Vendredi Saint'      , NULL   , 0);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Pâques'              , NULL   , 1);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Lundi de Pâques'     , NULL   , 1);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Ascension'           , NULL   , 1);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Pentecôte'           , NULL   , 1);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Lundi de Pentecôte'  , NULL   , 1);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Fête du Travail'     , '05-01', 1);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Fête de la Victoire' , '05-08', 1);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Fête Nationale'      , '07-14', 1);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Assomption'          , '08-15', 1);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Toussaint'           , '11-01', 1);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Fête des Morts'      , '11-02', 0);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Armistice de 1918'   , '11-11', 1);
INSERT INTO calendrier_fetes (clf_description, clf_fixe, clf_chome) VALUES ('Noël'                , '12-25', 1);
 
commit;

Table calendrier

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
CREATE TABLE calendrier
(
    cal_jour             date                                NOT NULL
  , cal_position_semaine AS (to_char(cal_jour, 'd'))         NOT NULL
  , cal_position_mois    AS (to_char(cal_jour, 'dd'))        NOT NULL
  , cal_position_annee   AS (to_char(cal_jour, 'ddd'))       NOT NULL
  , cal_semaine_iso      AS (to_char(cal_jour, 'iyyy"W"iw')) NOT NULL
  , cal_mois             AS (to_char(cal_jour, 'yyyy-mm'))   NOT NULL
  , cal_trimestre        AS (to_char(cal_jour, 'yyyy"Q"q'))  NOT NULL
  , cal_annee            AS (to_char(cal_jour, 'yyyy'))      NOT NULL
  -- je n'ai pas mis toutes les combinaisons possibles, à adapter selon vos besoins
  , clf_jour_fetes       number(5)                               NULL
  , constraint pk_calendrier
      PRIMARY KEY (cal_jour)
      USING INDEX
)
compress;
 
INSERT /*+ append */ INTO calendrier (cal_jour)
    SELECT level - 1 + date '1900-01-01'
      FROM dual
connect BY level <= date '2100-01-01' - date '1900-01-01';
 
commit;
 
ALTER TABLE calendrier
ADD constraint fk_calendrier_fetes
      FOREIGN KEY (clf_jour_fetes)
      REFERENCES calendrier_fetes (clf_jour_fetes);
 
CREATE INDEX fk_calendrier_fetes
ON calendrier (clf_jour_fetes);
 
CREATE bitmap INDEX ib_calendrier_position_semaine
ON calendrier (cal_position_semaine);
 
CREATE bitmap INDEX ib_calendrier_position_mois
ON calendrier (cal_position_mois);
 
CREATE bitmap INDEX ib_calendrier_position_annee
ON calendrier (cal_position_annee);
 
CREATE bitmap INDEX ib_calendrier_semaine_iso
ON calendrier (cal_semaine_iso);
 
CREATE bitmap INDEX ib_calendrier_mois
ON calendrier (cal_mois);
 
CREATE bitmap INDEX ib_calendrier_trimestre
ON calendrier (cal_trimestre);
 
CREATE bitmap INDEX ib_calendrier_annee
ON calendrier (cal_annee);

Mise à jour des fêtes

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
-- Fêtes fixes
 
merge INTO calendrier cal
USING calendrier_fetes clf
   ON (to_char(cal.cal_jour, 'mm-dd') = clf.clf_fixe)
 when matched then UPDATE
  SET cal.clf_jour_fetes = clf.clf_jour_fetes
WHERE cal.clf_jour_fetes IS NULL;
 
commit;
 
-- Fêtes mobiles
 
merge INTO calendrier cal
USING (WITH sr1 AS
        (
        SELECT DISTINCT cal_annee
          FROM calendrier
        )
          ,  sr2 AS
        (
        SELECT to_date(to_char(cal_annee) || '0301', 'yyyymmdd') + 27 + floor((floor(mod(19*mod(cal_annee, 19) +
               floor(cal_annee/100) - floor(floor(cal_annee/100)/4) - floor((8*floor(cal_annee/100) + 13) / 25) +
               15, 30)/28)*floor(29/(mod(19*mod(cal_annee, 19) + floor(cal_annee/100) - floor(floor(cal_annee/100)/4) -
               floor((8*floor(cal_annee/100) + 13) / 25) + 15, 30)+1))*floor((21-mod(cal_annee, 19))/11)) - 
               1*floor(mod(19*mod(cal_annee, 19) + floor(cal_annee/100) - floor(floor(cal_annee/100)/4) -
               floor((8*floor(cal_annee/100) + 13) / 25) + 15, 30)/28) +mod(19*mod(cal_annee, 19) + floor(cal_annee/100) -
               floor(floor(cal_annee/100)/4) - floor((8*floor(cal_annee/100) + 13) / 25) + 15, 30)) -
               floor(mod(floor(cal_annee/4 + cal_annee)+floor((floor(mod(19*mod(cal_annee, 19) + floor(cal_annee/100) -
               floor(floor(cal_annee/100)/4) - floor((8*floor(cal_annee/100) + 13) / 25) + 15, 30)/28)*
               floor(29/(mod(19*mod(cal_annee, 19) + floor(cal_annee/100) - floor(floor(cal_annee/100)/4) -
               floor((8*floor(cal_annee/100) + 13) / 25) + 15, 30)+1))*floor((21-mod(cal_annee, 19))/11))-
               1*floor(mod(19*mod(cal_annee, 19) + floor(cal_annee/100) - floor(floor(cal_annee/100)/4) -
               floor((8*floor(cal_annee/100) + 13) / 25) + 15, 30)/28) + mod(19*mod(cal_annee, 19) +
               floor(cal_annee/100) - floor(floor(cal_annee/100)/4) - floor((8*floor(cal_annee/100) + 13) / 25) +
               15, 30) )+2+floor(floor(cal_annee/100)/4)-floor(cal_annee/100), 7)) AS dt
         -- C'est la version monoligne de l'algo de Claus Tøndering
          FROM sr1
        )
          ,  sr3 AS
        (
        SELECT 'Pâques' AS description, dt      FROM sr2 union ALL
        SELECT 'Lundi de Pâques'      , dt + 1  FROM sr2 union ALL
        SELECT 'Ascension'            , dt + 39 FROM sr2 union ALL
        SELECT 'Pentecôte'            , dt + 49 FROM sr2 union ALL
        SELECT 'Lundi de Pentecôte'   , dt + 50 FROM sr2 union ALL
        SELECT 'Vendredi Saint'       , dt - 2  FROM sr2 union ALL
        SELECT 'Mardi Gras'           , dt - 47 FROM sr2 union ALL
        SELECT 'Mercredi des cendres' , dt - 46 FROM sr2
        )
        SELECT clf.clf_jour_fetes
             , sr3.dt
          FROM sr3
               INNER JOIN calendrier_fetes clf
                 ON clf.clf_description = sr3.description
        ) clv
   ON (clv.dt = cal.cal_jour)
 when matched then UPDATE
  SET cal.clf_jour_fetes = clv.clf_jour_fetes
WHERE cal.clf_jour_fetes IS NULL;
 
commit;

Vue de sélection, statistiques

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE OR REPLACE VIEW v_calendrier
AS
SELECT cal.cal_jour
     , cal.cal_position_semaine
     , cal.cal_position_mois
     , cal.cal_position_annee
     , cal.cal_semaine_iso
     , cal.cal_mois
     , cal.cal_trimestre
     , cal.cal_annee
     , clf.clf_description
     , cast(coalesce(clf.clf_chome, 0) AS number(1)) AS clf_chome
  FROM calendrier cal
       LEFT OUTER JOIN calendrier_fetes clf
         ON clf.clf_jour_fetes = cal.clf_jour_fetes;
 
begin
    dbms_stats.gather_table_stats(ownname => user, tabname => 'CALENDRIER'      , estimate_percent => 100, cascade => true); 
    dbms_stats.gather_table_stats(ownname => user, tabname => 'CALENDRIER_FETES', estimate_percent => 100, cascade => true); 
end;
/

Pour information, ces deux siècles de données pèsent un peu moins de 2Mo sans les index, un peu plus de 10Mo avec.

À partir de cette vue, on peut s'amuser à faire plein de choses.

Connaître les jours fériés à venir :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
  SELECT cal_jour, clf_description
    FROM v_calendrier
   WHERE cal_annee = '2012'
     AND clf_chome = 1
ORDER BY cal_jour ASC;
 
CAL_JOUR   CLF_DESCRIPTION     
---------- --------------------
2012-01-01 Jour de l’an        
2012-04-08 Pâques              
2012-04-09 Lundi de Pâques     
2012-05-01 Fête du Travail     
2012-05-08 Fête de la Victoire 
2012-05-17 Ascension           
2012-05-27 Pentecôte           
2012-05-28 Lundi de Pentecôte  
2012-07-14 Fête Nationale      
2012-08-15 Assomption          
2012-11-01 Toussaint           
2012-11-11 Armistice de 1918   
2012-12-25 Noël

Connaître les jours ouvrables :

Code :

1
2
3
4
5
6
  SELECT *
    FROM v_calendrier
   WHERE cal_mois = '2012-02'
     AND clf_chome = 0
     AND cal_position_semaine NOT IN ('6', '7')
ORDER BY cal_jour ASC;

Connaître les bornes des semaines ayant un jour dans une année :

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
  SELECT cal_semaine_iso
       , min(cal_jour) AS cal_jour_deb
       , max(cal_jour) AS cal_jour_fin
    FROM v_calendrier
   WHERE cal_semaine_iso IN (SELECT cal_semaine_iso
                               FROM v_calendrier
                              WHERE cal_annee = '2012')
GROUP BY cal_semaine_iso
ORDER BY cal_semaine_iso ASC;
 
CAL_SEMAINE_ISO CAL_JOUR_DEB CAL_JOUR_FIN
--------------- ------------ ------------
2011W52         2011-12-26   2012-01-01  
2012W01         2012-01-02   2012-01-08  
2012W02         2012-01-09   2012-01-15  
2012W03         2012-01-16   2012-01-22  
2012W04         2012-01-23   2012-01-29  
2012W05         2012-01-30   2012-02-05  
2012W06         2012-02-06   2012-02-12  
2012W07         2012-02-13   2012-02-19  
2012W08         2012-02-20   2012-02-26  
2012W09         2012-02-27   2012-03-04  
2012W10         2012-03-05   2012-03-11  
2012W11         2012-03-12   2012-03-18  
2012W12         2012-03-19   2012-03-25  
2012W13         2012-03-26   2012-04-01  
2012W14         2012-04-02   2012-04-08  
2012W15         2012-04-09   2012-04-15  
2012W16         2012-04-16   2012-04-22  
2012W17         2012-04-23   2012-04-29  
2012W18         2012-04-30   2012-05-06  
2012W19         2012-05-07   2012-05-13  
2012W20         2012-05-14   2012-05-20  
2012W21         2012-05-21   2012-05-27  
2012W22         2012-05-28   2012-06-03  
2012W23         2012-06-04   2012-06-10  
2012W24         2012-06-11   2012-06-17  
2012W25         2012-06-18   2012-06-24  
2012W26         2012-06-25   2012-07-01  
2012W27         2012-07-02   2012-07-08  
2012W28         2012-07-09   2012-07-15  
2012W29         2012-07-16   2012-07-22  
2012W30         2012-07-23   2012-07-29  
2012W31         2012-07-30   2012-08-05  
2012W32         2012-08-06   2012-08-12  
2012W33         2012-08-13   2012-08-19  
2012W34         2012-08-20   2012-08-26  
2012W35         2012-08-27   2012-09-02  
2012W36         2012-09-03   2012-09-09  
2012W37         2012-09-10   2012-09-16  
2012W38         2012-09-17   2012-09-23  
2012W39         2012-09-24   2012-09-30  
2012W40         2012-10-01   2012-10-07  
2012W41         2012-10-08   2012-10-14  
2012W42         2012-10-15   2012-10-21  
2012W43         2012-10-22   2012-10-28  
2012W44         2012-10-29   2012-11-04  
2012W45         2012-11-05   2012-11-11  
2012W46         2012-11-12   2012-11-18  
2012W47         2012-11-19   2012-11-25  
2012W48         2012-11-26   2012-12-02  
2012W49         2012-12-03   2012-12-09  
2012W50         2012-12-10   2012-12-16  
2012W51         2012-12-17   2012-12-23  
2012W52         2012-12-24   2012-12-30  
2013W01         2012-12-31   2013-01-06

[milka]

Bonjour Waldar,

Aurais-tu la possibilité de mettre à disposition un fichier csv (extraction date+description de ta table oracle) contenant toutes les dates de jours fériés des années 2000 à 2035 ou + ?

Code :

1
2
3
4
5
SELECT cal_jour, clf_description
    FROM v_calendrier
   WHERE cal_annee BETWEEN '2000' AND '2035'
     AND clf_chome = 1
ORDER BY cal_jour ASC;

Merci d'avance

[skuatamad]

Il manque juste :

Code :

ALTER session SET nls_territory = 'FRANCE';

Pour que le to_char(cal_jour, 'd') renvoie 1 pour le lundi (1er jour de la semaine à la mode française).