Discussion :

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

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
alter session set nls_date_language = 'French';
alter session set nls_territory = 'FRANCE';
 
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 :

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

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

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

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

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

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 :

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

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

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 :

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

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).

[stephane.jeanrenaud]

Bonjour à tous,

En m'inspirant de ce code, j'ai fait une fonction qui permet de savoir si un jour est férié. Il peut être facilement adapté pour ajouter ou enlever des 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
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
84
85
86
create or replace function estFerie(p_date_a_tester date) return boolean is
 
  type t_tableau_date is table of date;
 
  v_trunc_date_a_tester date;
  v_jours_feries        t_tableau_date;
  v_annee_a_tester      number;
  v_paques              date;
 
  function calculerPaques(p_annee number) return date is
    v_dt_1_mars date;
    v_1         number;
    v_2         number;
    v_3         number;
    v_4         number;
    v_5         number;
    v_6         number;
  begin
 
    v_dt_1_mars := to_date(to_char(p_annee) || '0301', 'yyyymmdd');
    v_1 := floor(p_annee / 100);
    v_2 := floor(v_1 / 4);
    v_3 := mod(p_annee, 19);
    v_4 := mod(19 * v_3 + v_1 - v_2 - floor((8 * v_1 + 13) / 25) + 15, 30);
    v_5 := floor(v_4 / 28);
    v_6 := floor((v_5 * floor(29 / (v_4 + 1)) * floor((21 - v_3) / 11)) - 1 * v_5 + v_4);
    return v_dt_1_mars + 27 + v_6 - floor(mod(floor(p_annee / 4 + p_annee) + v_6 + 2 + v_2 - v_1, 7));
 
  end calculerPaques;
 
  procedure ajouterJourFerie(p_jours_feries in out t_tableau_date, p_date date) is
  begin
 
    p_jours_feries.extend;
    p_jours_feries(p_jours_feries.last) := p_date;
 
  end ajouterJourFerie;
begin
 
  v_jours_feries := t_tableau_date();
 
  v_trunc_date_a_tester := trunc(p_date_a_tester);
 
  select extract(year from v_trunc_date_a_tester)
    into v_annee_a_tester
    from dual;
 
  v_paques := calculerPaques(v_annee_a_tester);
 
  -- Jour de l'an
  ajouterJourFerie(v_jours_feries, to_date('01/01/' || v_annee_a_tester, 'DD/MM/YYYY'));
  -- Pâques
  ajouterJourFerie(v_jours_feries, v_paques);
  -- Lundi de Pâques
  ajouterJourFerie(v_jours_feries, v_paques + 1);
  -- Ascension
  ajouterJourFerie(v_jours_feries, v_paques + 39);
  -- Pentecôte
  ajouterJourFerie(v_jours_feries, v_paques + 49);
  -- Lundi de Pentecôte
  ajouterJourFerie(v_jours_feries, v_paques + 50);
  -- Fête du Travail
  ajouterJourFerie(v_jours_feries, to_date('01/05/' || v_annee_a_tester, 'DD/MM/YYYY'));
  -- Fête de la Victoire
  ajouterJourFerie(v_jours_feries, to_date('08/05/' || v_annee_a_tester, 'DD/MM/YYYY'));
  -- Fête Nationale
  ajouterJourFerie(v_jours_feries, to_date('14/07/' || v_annee_a_tester, 'DD/MM/YYYY'));
  -- Assomption
  ajouterJourFerie(v_jours_feries, to_date('15/08/' || v_annee_a_tester, 'DD/MM/YYYY'));
  -- Toussaint
  ajouterJourFerie(v_jours_feries, to_date('01/11/' || v_annee_a_tester, 'DD/MM/YYYY'));
  -- Armistice de 1918
  ajouterJourFerie(v_jours_feries, to_date('11/11/' || v_annee_a_tester, 'DD/MM/YYYY'));
  -- Noël
  ajouterJourFerie(v_jours_feries, to_date('25/12/' || v_annee_a_tester, 'DD/MM/YYYY'));
 
  -- Parcours des jours fériés
  for i in v_jours_feries.first .. v_jours_feries.last loop
    if v_jours_feries(i) = v_trunc_date_a_tester then
      return true;
    end if;
  end loop;
 
  return false;
 
end;

Voici un exemple d'utilisation de cette fonction :

Code :

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

1
2
3
4
5
6
7
8
9
10
begin
 
  -- Test avec Pâques
  if estFerie(to_date('20/04/2014', 'DD/MM/YYYY')) then
    dbms_output.put_line('Férié');
  else
    dbms_output.put_line('Pas férié');
  end if;
 
end;