Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 3 sur 3
  1. #1
    Modérateur

    Homme Profil pro Fabien
    Ingénieur d'études en décisionnel
    Inscrit en
    septembre 2008
    Messages
    6 736
    Détails du profil
    Informations personnelles :
    Nom : Homme Fabien
    Âge : 36
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études en décisionnel
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : septembre 2008
    Messages : 6 736
    Points : 14 935
    Points
    14 935

    Par défaut Calendrier avec jours fériés

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

  2. #2
    Nouveau Membre du Club
    Profil pro
    Administrateur de base de données
    Inscrit en
    mai 2003
    Messages
    109
    Détails du profil
    Informations personnelles :
    Âge : 33
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : mai 2003
    Messages : 109
    Points : 27
    Points
    27

    Par défaut liste jours fériés français

    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

  3. #3
    Expert Confirmé
    Inscrit en
    août 2008
    Messages
    2 091
    Détails du profil
    Informations forums :
    Inscription : août 2008
    Messages : 2 091
    Points : 3 644
    Points
    3 644

    Par défaut

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

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •