La gestion du temps et la manipulation des données temporelles sont les éléments les plus ardus des développements. Pour mettre toutes les chances de votre côté et passer d'un problème complexe à une solution presque enfantine, je vous propose d'étudier cette méthode basée sur la modélisation d'un planning de dates...
La difficulté des calculs portant sur des dates ou des horaires (et parfois les deux) est lié à la codification même de la mesure du temps ainsi qu'au fait que le temps est une entropie (disons le, même une "isentroprie"), c'est à dire qu'il s'écoule de manière uniforme (iso) et dans un seul sens irréversible (vers le futur).
La mesure du temps n'obéit pas à des règles conventionnelles, comme le comptage décimal ou l'origine zéro. En effet :
Les années comptent tantôt 365 tantôt 366 jours en fait 365 jours 5 heures 48 min. 45,97 sec...
Les mois de 29 à 31 jours
Il n'y a pas d'année 0, mais il y a des années négatives (avant JC !)
Les siècles et millénaires commencent par une année unitaire (1, 1901, 2001...)
Les jours comptent 24 heures et il y a une heure zéro !
Les heures comptent 60 minutes et les minutes 60 secondes,
Il n'y a pas recouvrement éxact des semaines par rapport aux mois...
Les heures changent par rapport aux différents fuseaux horaires de la planète !
certaines opérations sur les dates sont impossible (par exemples rajouter exactement 3 mois à une date)...
le 10 octobre 1582 n'a jamais existé !!!
Tous ces éléments font que les calculs, notamment de comptage du temps obéissent à des algorithmes complexes.
De ce fait, la norme SQL 2, propose une solution générale assez intelligente. Mais elle est rarement implantée. Nous étudierons donc la norme, ce que propose les éditeurs et finalement une solution basée sur des relations entre tables avec une table principale stockant toutes les dates.
NOTA : il est dommage que le calendrier mis en place lors de la révolution française n'ai pas subsisté. L'année y étais divisé en 12 mois de 30 jours. Chaque semaine faisait 10 jours. Les 5 ou 6 jours restant étaient placés à la fin de l'année et constituait des vancances... En quelques sortes, les révolutionnaires étaient en avance sur les congés payés de 1936 et sur les 35 heures de la mère Aubry !
ATTENTION : La numérotation des semaines est standardisée depuis 1976 par l'ISO, avec les règles suivantes :
Le lundi est considéré comme le premier jour de la semaine.
Les semaines d'une même année sont numérotées de 01 à 52 (parfois 53).
La semaine qui porte le numéro 01 est celle qui contient le premier jeudi de janvier.
Il peut exister une semaine n°53 (années communes finissant un jeudi, bissextiles finissant un jeudi ou un vendredi).
La numérotation des semaines aux USA, comme la numérotation des jours diffère totalement de cette norme !
1. La solution normative
Elle se compose de trois éléments : des types spécifiques, une algèbre et des opérateurs particuliers.
1.1. Les types temporels SQL 2
La norme propose les types :
DATE
TIME [WITH TIME ZONE]
TIMESTAMP [WITH TIME ZONE]
INTERVAL
La précision de TIME ZONE permet de définir le décalage du fuseau horaire par rapport à l'heure universelle (UTC).
La norme SQL 2 impose en outre la représentation des dates et heures suivant le masque :
HEURE
DATE
DATE et HEURE
hh:mm:ss.nnn
AAAA-MM-JJ
AAAA-MM-JJ hh:mm:ss.nnn
Avec des dates allant du premier janvier de l'an 1 au 31 décembre de l'an 9999.
NOTA : le typage rapide est permis et même conseillé dans les expression temporelles. En effet comment savoir si 21:16 fait référence à 21h16 ou 21 minutes et 16 secondes ? Pour lever le doute, on peut, plutôt que d'utiliser la fonction CAST, préfixer la donnée :
Exemple :
TIME '00:21:16', DATE '2002-04-05'
Le type INTERVAL permet de stocker des durées. Sa syntaxe est de la forme :
mesure_temps2 et toutes les mesures intermédiaires entre mesure_temps1 et mesure_temps2 ne peuvent "déborder".
Exemples :
VALIDE
NON VALIDE
Pourquoi ?
DUREE1 INTERVAL DAY
DUREE2 INTERVAL DAY TO DAY
DAY n'est pas compris dans DAY
DUREE3 INTERVAL YEAR TO DAY
DUREE4 INTERVAL MINUTE TO HOUR
HOUR n'est pas compris dans MINUTE
DUREE5 INTERVAL HOUR TO MINUTE
DUREE6 INTERVAL DAY TO MONTH
MONTH n'est pas compris dans DAY
CAST ('300:5:20' AS
INTERVAL HOUR TO SECOND)
CAST('10:300:20' AS
INTERVAL HOUR TO SECOND)
300 minutes déborde vers heure
CAST ('2002-09-04 21:16'
INTERVAL YEAR TO MINUTE)
CAST ('4/9/2002 21h16'
INTERVAL YEAR TO MINUTE)
Format ISO non respecté
1.2. Mathématique normative des calculs temporels
1.2.1. Fonctions
Les fonctions CURRENT_DATE, CURRENT_TIME,CURRENT_TIMESTAMP permettent de récupérer respectivement la date, l'heure et le combiné date/heure courantes depuis le système. Attention, ce sont des fonctions non déterministes, c'est à dire que ré exécutées plusieurs fois de suite, elles peuvent ne pas donner un résultat identique...
La fonction EXTRACT permet d'extraire une partie temporelle sous forme numérique d'une donnée de type temporel. La syntaxe de le fonction EXTRACT est la suivante :
EXTRACT( {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } FROM donnée)
Exemples :
EXTRACT (MONTH FROM '2002-04-13')
4
EXTRACT (MINUTE FROM '2002-04-13 21:16:11.050')
16
1.2.2. Le prédicat OVERLAPS
Un outil puissant nous est fournit pas SQL avec le prédicat OVERLAPS. Il permet de préciser si une période en recouvre (au moins partiellement) une autre. C'est très pratique si vous voulez gérer un diagramme de GANTT par exemple, ou il convient que certaines tâches ne démarrent pas avant la fin d'autres tâches.
La syntaxe du prédicat OVERLAPS est la suivante :
(période1) OVERLAPS (période2)
période ::
borne_debut, borne_fin
borne_debut ::
DATE | TIME | TIMESTAMP
borne_fin ::
DATE | TIME | TIMESTAMP | INTERVAL
Avec la contrainte suivante :
borne_début et borne_fin doivent être de même type dans les périodes sauf si borne_fin est de type INTERVAL.
Exemples :
VALIDE
NON VALIDE
Pourquoi ?
(TIME '08:11:25', TIME '09:25:11')
OVERLAPS
(TIME '09:11:25', TIME '09:11:25')
TIME '08:11:25', DATE '2002-01-01')
OVERLAPS
(TIME '09:11:25', TIME '09:11:25')
(TIMESTAMP '2002-01-01 08:11:25',
TIMESTAMP '2002-02-01 21:25:11')
OVERLAPS
(TIMESTAMP '2002-01-10 09:11:25',
INTERVAL '1 01:01:30' DAY TO SECOND)
(TIMESTAMP '2002-01-01 08:11:25',
TIMESTAMP '2002-02-01 21:25:11')
OVERLAPS
(INTERVAL '1 01:01:30' DAY TO SECOND,
TIMESTAMP '2002-01-10 09:11:25')
Utilisation de type INTERVAL illicite en borne_debut
Maintenant penchons nous sur le résultat. OVERLAPS étant un prédicat il ne peut fournir que... 3 valeurs : TRUE, FALSE et UNKNOWN (du fait de la présence possible de marqueur NULL dans les données).
Le prédicat OVERLAPS est vrai si :
((période1.borne_debut > période2.borne_debut
et (période1.borne_debut < période2.borne_debut ou période1.borne_fin < période2.borne_fin)) ou
(période2.borne_debut > période1.borne_debut
et (période2.borne_debut < période1.borne_fin ou période2.borne_fin < période1.borne_fin)) ou
(période1.borne_debut = période2.borne_debut et (période1.borne_fin NON NUL et période2.borne_fin NON NUL)
Ce qui peut se traduire en bon français par : la période P2 recouvre la période P1, si tout ou partie de la période P2 est inclus dans la période P1. ATTENTION : un effet de bord due à l'asymétrie du prédicat est à remarquer...
Un bon exemple valant mieux qu'un long discours, voici quelques données permettant de mieux comprendre l'intérêt de ce prédicat. Le jeu d'essais suivant va nous permettre de mieux comprendre...
Les périodes dont définies comme suit :
a1 va de 8h à 9h et a2 de 8h30 à 9h30
b1 va de 8h à 9h et b2 de 9h à 9h30
c1 va de 9h à 9h30 et c2 de 9h à 9h
d1 va de 9h à 9h30 et d2 de 9h30 à 9h30
e1 va de 8h à 8h30 et e2 de 9h à 9h30
f1 va de 8h30 à 9h et f2 de 8h à 8h30
g1 va de 8h30 à 10h et g2 de 8h à 9h
Détails des intervalles de temps c2 et d
On peut les modéliser ainsi :
CREATETABLE PERIODE
(CODE CHAR(1),
P1_DEBUT TIME,
P1_FIN TIME,
P2_DEBUT TIME,
P2_FIN TIME)
SELECT *
FROM PERIODE
WHERE P1_DEBUT, P1_FIN OVERLAPS P2_DEBUT, P2_FIN
CODE P1_DEBUT P1_FIN P2_DEBUT P2_FIN
---- -------- -------- -------- ---------
a 08:00:00 09:00:00 08:30:00 09:30:00
c 09:00:00 09:30:00 09:00:00 09:00:00
g 08:30:00 10:00:00 08:00:00 09:00:00
Seules, les périodes des exemples a, c et g répondent à l'opérateur OVERLAPS.
Les périodes recouvrantes sont sur fond blanc, celle non recouvrantes sur fond gris.
L'explication est extraite de mon ouvrage, "SQL", la référence, collection développement, Campus Press Editeur Paris 2001.
" Par analogie, avec les données que nous venons de voir, il s'agit de considérer des spectateurs qui seraient entrés dans une salle de cinéma pendant la projection d'un film. Le film se serait déroulé en période P1 et chaque spectateur aurait séjourné dans la salle en période P2. Dès lors tout devient clair : le prédicat OVERLAPS permet de savoir qui a vu le film, au moins en partie ! On constate au passage que quelqu'un qui ne reste qu'un temps infiniment court, c'est à dire que la période est caractérisé par le fait que le début est égal à la fin, a vu le film, si cet instant est inclus dans la limite [début, fin[ de la période de référence. En effet s'il arrive au début il est considéré comme ayant vu le film tandis que s'il arrive à la fin, il est considéré comme n'ayant pas vu le film. Ceci est logique, mais va à l'encontre de l'intuition... C'est logique parce que le temps s'écoule de manière uniforme dans un seul sens ! On ne peut revenir en arrière et la mesure du temps ne peut qu'augmenter puisqu'allant toujours dans le sens du vieillissement On peut dire que l'écoulement du temps est de nature isentropique, c'est à dire à entropie constante. En fait si on se livre à un calcul mathématique aux limites on peut toujours partir d'un intervalle donné que l'on restreint petit à petit pour arriver à une durée nulle. Si cet instant de durée infime démarre au début d'un autre instant il reste toujours inclus dedans, tandis que s'il démarre à la fin, il ne sera jamais inclus dedans ! Là ou l'affaire se complique, c'est quand une valeur au moins est nulle... "
Bien entendu en l'absence d'un tel prédicat, vous pouvez le fabriquer de toutes pièces par une construction SQL. En effet reformulé sous SQL, notre prédicat OVERLAPS est :
SELECT *
FROM PERIODE
WHERE (P1_DEBUT > P2_DEBUT AND (P1_DEBUT < P2_FIN OR P1_FIN < P2_FIN)) OR
(P2_DEBUT > P1_DEBUT AND (P2_DEBUT < P1_FIN OR P2_FIN < P1_FIN)) OR
(P1_DEBUT = P2_DEBUT AND (P1_FIN ISNOTNULLAND P2_FIN ISNOTNULL))
Et donne, bien évidemment le même résultat que précédemment.
1.2.3. Algèbre temporelle
Il est possible d'utiliser les opérations algébriques + - x et / avec quelques restrictions :
premier opérande
opérateur
second opérande
résultat
TIMESTAMP | DATE | TIME
-
TIMESTAMP | DATE | TIME
INTERVAL
TIMESTAMP | DATE | TIME
+
INTERVAL
TIMESTAMP | DATE | TIME
TIMESTAMP | DATE | TIME
-
INTERVAL
TIMESTAMP | DATE | TIME
INTERVAL
+
TIMESTAMP | DATE | TIME
TIMESTAMP | DATE | TIME
INTERVAL
+
INTERVAL
INTERVAL
INTERVAL
-
INTERVAL
INTERVAL
INTERVAL
*
nombre
INTERVAL
INTERVAL
/
nombre
INTERVAL
nombre
*
INTERVAL
INTERVAL
Exemples :
VALIDE
NON VALIDE
Pourquoi ?
DATE '2002-01-01'
-
DATE '2001-12-24'
:: INTERVAL '8' DAY
DATE '2002-01-01'
-
TIMESTAMP '2001-12-24 16:12:30'
:: ???
différence de type
TIMESTAMP '2001-12-24 16:12:30'
-
INTERVAL '8' DAY
:: '2001-12-16 16:12:30'
DATE '2001-01-01'
-
DATE '2002-12-24'
:: ???
addition de date impossible
INTERVAL '7' DAY
/
2
:: INTERVAL '3 12:00:00' DAY TO SECOND
DATE '2001-01-01'
*
INTERVAL '2' DAY
pas de sens
1.2.4. Logique temporelle
Bien entendu la comparaison entre des types temporels est possible, mais dans certaines limites. Voici les différentes combinaisons possibles :
DATE
[NOT] < <= = >= > <>
DATE
TIME
[NOT] < <= = >= > <>
TIME
TIMESTAMP
[NOT] < <= = >= > <>
TIMESTAMP
INTERVAL YEAR
[NOT] < <= = >= > <>
INTERVAL YEAR
INTERVAL MONTH
[NOT] < <= = >= > <>
INTERVAL MONTH
INTERVAL YEAR TO MONTH
[NOT] < <= = >= > <>
INTERVAL YEAR TO MONTH
INTERVAL DAY
[NOT] < <= = >= > <>
INTERVAL DAY
INTERVAL DAY TO HOUR
[NOT] < <= = >= > <>
INTERVAL DAY TO HOUR
INTERVAL DAY TO MINUTE
[NOT] < <= = >= > <>
INTERVAL DAY TO MINUTE
INTERVAL DAY TO SECOND
[NOT] < <= = >= > <>
INTERVAL DAY TO SECOND
On pourra noter que tout interval comprenant le mois autrement qu'en borne de fin ne peut faire l'objet d'une comparaion. Ceci est du au fait que le nombre de jours d'un mois différe d'un mois à l'autre ce qui rend impossible la comparaison d'intervalle basé sur des durée de mois...
2. Le discours des éditeurs
La complexité de la logique temporelle et du calcul de date ont fait que peu d'éditeurs de SGBDR ont implémenté la norme de manière drastique. Certains même se contentent de ne fournir que le type TIMESTAMP.
Quels sont alors les trucs qu'ils utilisent pour donner satisfaction à leurs clients.
SQL Server de Microsoft, n'inclut que le type DATETIME (équivalent du normatif TIMESTAMP) et propose en sus, les cinq fonctions suivantes : CURRENT_TIMESTAMP, DATEPART, DATEADD, DATEDIFF et DATENAME (nous passerons sous silence les DAY, MONTH, YEAR qui sont virtuellement inclus dans DATEPART).
CURRENT_TIMESTAMP
Date et heure courante
DATEPART
Equivalent du EXTRACT de la norme
DATEADD
Ajout de durée dans date
DATEDIFF
Retrait de durée dans date
DATENAME
Nom d'une partie de date
Intéressons nous aux fonctions DATEADD et DATEDIFF. Leurs syntaxe est :
DATEADD | DATEDIFF (partie_de_date, nombre, date)
Exemple :
SELECT DATEADD(MONTH, 1, CAST('2002-01-31' AS DATETIME))
2002-02-28 00:00:00.000
SELECT DATEADD(MONTH, -1,
DATEADD(MONTH, 1, CAST('2002-01-31' AS DATETIME)))
2002-01-28 00:00:00.000
L'ajout d'un mois au 31 janvier 2002 ne provoque pas un saut à mars, car l'algorithme reprend bien la fin du mois suivant, soit le 28 février. Bravo SQL Server. En revanche la seconde requête est une abération... en effet l'ajout et le retrait imbriqué d'un mois, donne une date décalée de 3 jours. C'est une catastrophe... Au secours SQL Server !!!
Néanmoins la solution SQL Server permet des calculs de base sur les données temporelles pour peu que l'on prenne quelques précautions.
3. La solution intelligente
Elle consiste tout simplement à modéliser un planning avec non seulement une continuité des dates exploitées dans la base mais un ensemble de tables "satellites" ayant chacune un découpage du temps. Autrement dit, autour de la table des dates, une table des mois, des jours de semaine (de 1 lundi à 7 dimanche), des jours du mois (de 1 à 28, 29, 30 ou 31) une table des années, des semestres, des trimestres, des semaines...
3.1. Le modèle de données
Voici un tel modèle MERISE :
On trouvera dans T_PLN_JOUR_PJR une entité composée d'une colonne clef représentant les dates, une colonne rang numérotant les dates dans leur ordre chronologique à l'aide d'une valeur discrète (entier), mais aussi les semaines, les mois les trimestres les semestres et les années avec des valeurs continues (décimaux) et une colonne alea dont je vous expliquerai un jour l'utilité !
J'y ai rajouté une petite entité "INTERVALLE_TRAVAIL" permettant de définir des plages horaires d'ouverture de l'entreprise. Par exemple de 8h30 à 12h30 et de 14h à 18h du lundi au jeudi et de 9h à 12h et de 14h à 16h le vendredi (cette entité étant liée avec les jours de semaine).
Ce modèle aboutit à la représentation physique suivante :
Où l'on voit que la table T_PLN_JOUR_PJR est garnie de 8 clefs étrangères...
Le secret de ce modèle et de son utilité, ce sont les colonnes PJR_RANG_... de la table des dates. Nous allons voir ce qu'on y met dedans, mais surtout, comment on s'en sert...
3.2. Les données du référentiel
Bien entendu, pour pouvoir fonctionner, un tel modèle doit être garni, c'est à dire que toutes les tables doivent être populées. En annexe vous trouverez les ordres SQL pour créer cette base et la peupler.
Ce script et les deux procédures stockées (les procédures ont été écrites pour SQL Server mais sont facilement transposable dans le langage procédurale de votre SGBDR ou dans un langage comme DELPHI) permettent de peupler les tables référentielles, TR_* mais aussi une partie de la table principale (T_PLN_JOUR_PJR), notamment les colonnes PJR_DATE (date du jour), PAN_ID (année), PMS_ID (mois), PJM_ID (jour du mois de 1 à 31), PSM_ID (semaine de l'année de 1 à 52 ou 53), PJS_ID (jour de la semaine de 1 à 7), PJA_ID (jour de l'année de 1 à 365 ou 366) PTR_ID (trimestre de l'année) et PST_ID (semestre de l'année).
3.3. Les mesures temporelles
Intéressons nous maintenant aux colonnes PJR_RANG_*.
Notre table est composée de lignes possédant chacune une valeur discrète : celle d'un jour, d'une date dans la continuité du temps. La colonne PJR_RANG_JOUR est donc numéroté en continue de façon a ce que chaque lendemain soit incrémenté d'une unité. Autrement dit, PJR_RANG_JOUR + 1 équivaut à ajouter un jour... Le type de données sous jacent à PJR_RANG_JOUR est donc un entier. Mais il faut trouver une origine de numérotation. On peut la fixer arbitrairement, par exemple au 14 octobre 1967 (c'est fou ce qu'on trouve à une date aussi arbitraire que celle là lorqu'on cherche sur un moteur du web comme Yahoo [1]) mais l'habitude est de se caler sur le premier janvier 1900 qui constitue le point d'origine de l'axe des dates et par conséquent le jour 0.
Dès lors nous allons avoir la numérotation suivante :
Là où notre affaire se complique, c'est pour numéroter chaque jour en fraction de mois, semaine, année...
En fait ce n'est pas très compliqué. Le premier jour d'une année, par exemple le jour de l'an de 1930 :
le rang de l'année est 30,0000
le rang du mois est 30*12 + 1 = 361,0000
le rang de la semaine est 1571, 000
le rang du trimestre est 30 * 4 + 1 = 121
le rang du semestre est 30 * 2 + 1 = 61
Pour le rang de la semaine, c'est un plus difficile car il faut compter le nombre de semaines de chaque année, cela pouvant varier entre 52 et 53... Au passage notez les décimales !
Le lendemain de ce jour, au 2 janvier 1930, les rang sont les suivants :
année : 30 + 1/365 (365, nombre de jours de l'année) = 30,0027
mois : 361 + 1/31 (31, nombre de jours du mois) = 361,0323
semaine : 1571 + 1/7 (7, nombre de jours de la semaine) = 1571,1429
trimestre : 121 + 1/90 (90, nombre de jour du trimestre) = 121,0111
semestre : 61 + 1/181 (181, nombre de jours du semestre) = 61,0055
Etc...
Voici une deux des requêtes pour calculer ces nouvelles données. Elle concerne la mise à jour du rang de l'année dans la table T_PLN_JOUR_PJR :
-- calcul du rang ANNEESELECT PJR_DATE,
CAST(
(CAST(PAN_ID AS FLOAT) - 1899.0)
+ (CAST(PJR_RANG_JOUR AS FLOAT)
- (SELECT CAST(PJR2.PJR_RANG_JOUR AS FLOAT)
FROM T_PLN_JOUR_PJR PJR2
WHERE PJR2.PJM_ID = 1
AND PJR2.PMS_ID = 1
AND PJR2.PAN_ID = PJR.PAN_ID +1))
/ (SELECT CAST(COUNT(*) AS FLOAT)
FROM T_PLN_JOUR_PJR PJR3
WHERE PJR3.PAN_ID = PJR.PAN_ID)
AS DECIMAL(10,4)) AS PJR_RANG_AN
FROM T_PLN_JOUR_PJR PJR
=> le rang de l'année [1900 = 0]
=> plus ( le rang du jour
=> moins le nombre de jours
pour aller à l'année
suivante
=> ) divisé par le nombre
de jours écoulés dans l'année
-- calcul du rang MOISSELECT PJR_DATE,
CAST(
(CAST(PAN_ID AS FLOAT) - 1900.0) * 12 + PMS_ID + 1
+ (CAST(PJR_RANG_JOUR AS FLOAT)
- (SELECT CAST(PJR2.PJR_RANG_JOUR AS FLOAT)
FROM T_PLN_JOUR_PJR PJR2
WHERE PJR2.PJM_ID = 1
AND PJR2.PMS_ID = (PJR.PMS_ID % 12) + 1
AND PJR2.PAN_ID =
CASE
WHEN PJR.PMS_ID + 1 = 13
THEN PJR.PAN_ID +1
ELSE PJR.PAN_ID
END))
/ (SELECT CAST(COUNT(*) AS FLOAT)
FROM T_PLN_JOUR_PJR PJR3
WHERE PJR3.PMS_ID = PJR.PMS_ID
AND PJR3.PAN_ID = PJR.PAN_ID)
AS DECIMAL(10,4)) AS PJR_RANG_MOIS
FROM T_PLN_JOUR_PJR PJR
=> le rang du mois [12 mois par an]
=> plus ( le rang du jour
=> moins le nombre de jours
pour aller au début du
mois suivant [attention
au passage de l'année
suivante]
=> ) divisé par le nombre
de jours dans le mois
NOTA : j'ai utilisé ici la fonction %
qui fait le modulo.
-- calcul du rang SEMAINESELECT PJR_DATE,
CAST(
CAST(PSM_ID AS FLOAT) + 51
+ (SELECTSUM(MAX_PSM_ID)
FROM (SELECTMAX(PSM_ID) AS MAX_PSM_ID
FROM T_PLN_JOUR_PJR
WHERE PAN_ID < PJR.PAN_ID
GROUP BY PAN_ID)
T )
+ (CAST(PJS_ID AS FLOAT) - 1) / 7
AS DECIMAL(10,4)) AS PJR_RANG_SEMAINE
FROM T_PLN_JOUR_PJR PJR
On utilise ici une astuce vu que l'on a toujours
le même nombre de jour dans une semaine.
Il suffit donc de rajouter 1/7 à chaque jour,
les jours étant numéroté de 1 à 7. On a ainsi :
(1 [pour lundi] - 1 ) / 7
(2 [pour mardi] - 1 ) / 7
etc...
Ceci suppose quand même que l'on a bien
numéroté les semaines depuis l'origine
c'est à dire le premier janvier 1900,
sinon il faut y rajouter le nombre de semaines
cumulées que l'on trouve en annexe
-- calcul du rang TRIMESTRESELECT PJR_DATE,
CAST((CAST(PAN_ID AS FLOAT) - 1900) * 4 + PTR_ID
+ (CAST((SELECTCOUNT(*)
FROM T_PLN_JOUR_PJR PJR2
WHERE PJR2.PTR_ID = PJR.PTR_ID
AND PJR2.PAN_ID = PJR.PAN_ID
AND PJR2.PJR_DATE <= PJR.PJR_DATE)
AS FLOAT) - 1)
/ (CAST((SELECTCOUNT(*)
FROM T_PLN_JOUR_PJR PJR3
WHERE PJR3.PTR_ID = PJR.PTR_ID
AND PJR3.PAN_ID = PJR.PAN_ID)
AS FLOAT))
AS DECIMAL(10,4)) AS RANG_TIMESTRE
FROM T_PLN_JOUR_PJR PJR
=> nombre de trimestre
=> plus ( nombre de jours écoulé
depuis le début du trimestre
=> ) divisé par le nombre de jours
du trimestre
-- calcul du rang SEMESTRESELECT PJR_DATE,
CAST((CAST(PAN_ID AS FLOAT) - 1900) * 2 + PST_ID
-- + nombre de jours écoulé
+ (CAST((SELECTCOUNT(*)
FROM T_PLN_JOUR_PJR PJR2
WHERE PJR2.PST_ID = PJR.PST_ID
AND PJR2.PAN_ID = PJR.PAN_ID
AND PJR2.PJR_DATE <= PJR.PJR_DATE)
AS FLOAT) - 1)
-- divisé par le total de jours
/ (CAST((SELECTCOUNT(*)
FROM T_PLN_JOUR_PJR PJR3
WHERE PJR3.PST_ID = PJR.PST_ID
AND PJR3.PAN_ID = PJR.PAN_ID) AS FLOAT))
AS DECIMAL(10,4)) AS RANG_SEMESTRE
FROM T_PLN_JOUR_PJR PJR
Pour les semestre le principe est le même
que pour celui des trimestres.
Le 4 est changé en deux, et la requête
porte sur la colonne PST_ID au lieu de PTR_ID
Bien évidemment ces requêtes peuvent être transformées en requêtes de mise à jour dont on trouvera une version en annexe...
Une fois ces données saisie dans notre table, les calculs sur les mesures temporelles deviennent triviaux. Pour nous aider à voir comment cela fonctionne, nous allons ajouter une table de test qui servira pour nos calculs.
3.3.1. Le nombre de ... jour, mois, annéé, trimestre, semestre
Comment donc obtenir le nombre de... jour, mois, annéé, trimestre, semestre entre deux dates ?
Le nombre de jours s'obtient par sous straction du rang jour. Mais pour faire cette soustraction il faut deux valeurs de rang jour, donc, deux fois la table T_PLN_JOUR_PJR dans la requête :
SELECT DATE_DEBUT, DATE_FIN,
PJR2.PJR_RANG_JOUR - PJR1.PJR_RANG_JOUR AS NOMBRE_JOUR
FROM TEST_PLN TPN
INNERJOIN T_PLN_JOUR_PJR PJR1
ON TPN.DATE_DEBUT = PJR1.PJR_DATE
INNERJOIN T_PLN_JOUR_PJR PJR2
ON TPN.DATE_FIN = PJR2.PJR_DATE
3.3.2. Comment ajouter exactement un deux ou trois mois, et pouvoir retomber sur nos date en les y retranchant ?
L'ajout et la soustraction sont presque aussi simple :
-- ajout de 10 jours à DATE DEBUTSELECT DATE_DEBUT, PJR2.PJR_DATE
FROM TEST_PLN TPN
INNERJOIN T_PLN_JOUR_PJR PJR1
ON TPN.DATE_DEBUT = PJR1.PJR_DATE
INNERJOIN T_PLN_JOUR_PJR PJR2
ON PJR2.PJR_RANG_JOUR = PJR1.PJR_RANG_JOUR + 10
-- ajout de 5 moisSELECT DATE_DEBUT, MIN(PJR2.PJR_DATE) AS DATE_PLUS_5_MOIS
FROM TEST_PLN TPN
INNERJOIN T_PLN_JOUR_PJR PJR1
ON TPN.DATE_DEBUT = PJR1.PJR_DATE
INNERJOIN T_PLN_JOUR_PJR PJR2
ON PJR2.PJR_RANG_MOIS >= PJR1.PJR_RANG_MOIS + 5
GROUP BY TPN.DATE_DEBUT
-- retrait de 3 trimestresSELECT DATE_DEBUT, MAX(PJR2.PJR_DATE) AS DATE_MOINS_3_TRIMESTRE
FROM TEST_PLN TPN
INNERJOIN T_PLN_JOUR_PJR PJR1
ON TPN.DATE_DEBUT = PJR1.PJR_DATE
INNERJOIN T_PLN_JOUR_PJR PJR2
ON PJR2.PJR_RANG_TRIMESTRE < PJR1.PJR_RANG_TRIMESTRE - 3
GROUP BY TPN.DATE_DEBUT
DATE_DEBUT DATE_MOINS_3_TRIMESTRE
----------- -----------------------
2001-01-15 2000-04-15
2002-11-11 2002-02-10
2003-01-15 2002-04-15
2004-12-24 2004-03-24
REMARQUE : la différence du jour du mois
dans les dates s'explique par le fait
que les trimestres n'ont pas tous le même
nombre de jours...
NOTEZ la différence entre l'ajout de jours, toujours exact parce que sur des entiers, et l'ajout de mois, année, trimestre, etc... qui, opérant sur des nombres réels doit faire l'objet d'une inéquation pour laquelle on recherche le minimum.
Attention donc au signe de cet inéquation et à l'utilisation de l'agrégat :
AJOUT an, mois, semaine, trimestre, semestre
condition >= dans la jointure
condition >= dans la jointure
RETRAIT an, mois, semaine, trimestre, semestre
agrégat MAX dans le SELECT
condition < dans la jointure
Dernier essais, reprenons l'exemple vu avec SQL Server et ses limites de calculs temporels...
INSERTINTO TEST_PLN VALUES ('2002-01-31', NULL)
-- ajout d'un moisSELECT DATE_DEBUT, MIN(PJR2.PJR_DATE) AS DATE_PLUS_1_MOIS
FROM TEST_PLN TPN
INNERJOIN T_PLN_JOUR_PJR PJR1
ON TPN.DATE_DEBUT = PJR1.PJR_DATE
INNERJOIN T_PLN_JOUR_PJR PJR2
ON PJR2.PJR_RANG_MOIS >= PJR1.PJR_RANG_MOIS + 1
GROUP BY TPN.DATE_DEBUT
DATE_DEBUT DATE_PLUS_1_MOIS
----------- -----------------
2002-01-31 2002-03-01
REMARQUE : surprise, le mois de février
semble ignoré et l'ajout d'un mois au 31
janvier passe au 1er mars...
-- retrait d'un moisSELECT DATE_DEBUT, MAX(PJR2.PJR_DATE) AS DATE_MOINS_1_MOIS
FROM TEST_PLN TPN
INNERJOIN T_PLN_JOUR_PJR PJR1
ON TPN.DATE_DEBUT = PJR1.PJR_DATE
INNERJOIN T_PLN_JOUR_PJR PJR2
ON PJR2.PJR_RANG_MOIS < PJR1.PJR_RANG_MOIS - 1
GROUP BY TPN.DATE_DEBUT
DATE_DEBUT DATE_MOINS_1_MOIS
----------- ------------------
2002-03-01 2002-01-31
SURPRISE : nous retombons sur nos pattes !
3.3.3. Toutes les ... date, mois, années ... entre deux dates
Autre demande qui revient souvent, connaître toutes les dates, les mois, les années entre deux dates :
SELECTDISTINCT DATE_DEBUT, PAN_ID, PMS_ID, DATE_FIN
FROM TEST_PLN TPN
INNERJOIN T_PLN_JOUR_PJR PJR
ON PJR.PJR_DATE BETWEEN TPN.DATE_DEBUT AND TPN.DATE_FIN
ORDERBY DATE_DEBUT, PAN_ID, PMS_ID
Cela peut paraître inutile... c'est souvent indispensable. Voici un exemple qui illustre l'absolu nécessité d'une table de dates. Notre service SAV fait des interventions en principe tous les jours. La table et les données associées sont les suivantes et concerne la semaine allant du lundi 4 mars au vendredi 8 mars :
Surprise ! La semaine ne compterai que 4 jours du lundi au vendredi ??? Ou est passé le jeudi 7 ? Il n'existe pas dans la table car aucune intervention n'a eût lieu ce jour. Il aurait fallut quand même obtenir cette date avec une valeur 0 pour le nombre d'interventions... Quelque chose comme :
NOMBRE SAV_DATE_INTERV
----------- ----------
2 2002-03-04
1 2002-03-05
2 2002-03-06
0 2002-03-07 <= ligne manquante...
3 2002-03-08
Afin de remédier à cette anomalie, il suffit de faire une jointure avec la table des dates :
SELECTCOUNT(SAV_ID) AS NOMBRE,
PJR_DATE
FROM T_SAV
RIGHTOUTERJOIN T_PLN_JOUR_PJR
ON PJR_DATE = SAV_DATE_INTERV
WHERE PJR_DATE BETWEEN ( SELECTMIN(SAV_DATE_INTERV) FROM T_SAV )
AND ( SELECTMAX(SAV_DATE_INTERV) FROM T_SAV )
GROUP BY PJR_DATE
ORDERBY PJR_DATE
L'erreur est encore plus criante si l'on tente de mesurer la moyenne du nombre d'intervention par jour :
SELECTAVG(CAST(NOMBRE AS FLOAT)) AS MOYENNE_JOUR
FROM (SELECTCOUNT(*) AS NOMBRE, SAV_DATE_INTERV
FROM T_SAV
GROUP BY SAV_DATE_INTERV) T
MOYENNE_JOUR
------------
2.0
Or 8 interventions sur 5 jours, cela représente 1,6 intervention par jour et non 2 ! Une statistique faussée par ce "trou". Encore une fois la solution nous est fournit par la jointure sur la table des dates :
SELECTAVG(CAST(NOMBRE AS FLOAT))
FROM (SELECTCOUNT(SAV_ID) AS NOMBRE,
PJR_DATE
FROM T_SAV
RIGHTOUTERJOIN T_PLN_JOUR_PJR
ON PJR_DATE = SAV_DATE_INTERV
WHERE PJR_DATE BETWEEN ( SELECTMIN(SAV_DATE_INTERV) FROM T_SAV )
AND ( SELECTMAX(SAV_DATE_INTERV) FROM T_SAV )
GROUP BY PJR_DATE) T
Il arrive que l'on doive stocker des dates dont certaines parties sont inconnues ou imprécises. Plusieurs solutions sont envisageables : l'utilisation de données séparées pour les éléments composant les dates ou bien l'utilisation d'une fourchette de dates.
4.1. Dates composites
Cette solution consiste à représenter les dates sous la forme de 3 colonnes : AN, MOIS et JOUR. L'important est de spécifier que ces colonnes peuvent être vide... Autrement dit il ne faut pas construire ces colonnes avec l'option NOT NULL.
A l'insertion comme lors des mises à jour on veillera a ne stocker que les éléments connus des dates.
Voici maintenant comment les requêtes doivent se présenter dans différents cas de figure :
Rechercher les zigzornifles datés du 4/5/1990 :
SELECT ...
FROM ...
WHERE DPT_JOUR = 4
AND DPT_MOIS = 5
AND DPT_AN = 1990
Les zigzornifles datant de mai 1990 :
SELECT ...
FROM ...
WHERE DPT_MOIS = 5
AND DPT_AN = 1990
Les zigzornifles de l'an 1990 :
SELECT ...
FROM ...
WHERE DPT_AN = 1990
Les zigzornifles entre le 4/5/1990 et le 8/7/1990 :
SELECT ...
FROM ...
WHERE (COALESCE(DPT_JOUR, 0),
COALESCE(DPT_MOIS, 0),
COALESCE(DPT_AN, 0)) >= (4, 5, 1990)
AND (COALESCE(DPT_JOUR, 0),
COALESCE(DPT_MOIS, 0),
COALESCE(DPT_AN, 0)) <= (8, 7, 1990)
Mais si votre SGBDR ne supporte pas le constructeur de lignes valuées, alors il faut écrire un équivalent SQL.
SELECT ...
FROM ...
WHERE (COALESCE(DPT_JOUR, 0) >= 4
OR (COALESCE(DPT_JOUR, 0) = 4
AND COALESCE(DPT_MOIS, 0) >= 5)
OR (COALESCE(DPT_JOUR, 0) = 4
AND COALESCE(DPT_MOIS, 0) = 5
AND COALESCE(DPT_AN, 0)) >= 1990))
AND (COALESCE(DPT_JOUR, 0) <= 8
OR (COALESCE(DPT_JOUR, 0) = 8
AND COALESCE(DPT_MOIS, 0) <= 7)
OR (COALESCE(DPT_JOUR, 0) = 8
AND COALESCE(DPT_MOIS, 0) = 7
AND COALESCE(DPT_AN, 0)) <= 1990))
Si la date est complète, on veillera à la recopier dans les deux colonnes. Pour cela on eut s'aider d'un trigger pour qu'en cas de présence du NULL dans la seconde date on reprenne la valeur de la première date.
A l'insertion, comme à la mise à jour, la partie de date qui est inconnue doit prendre la plage maximale des valeurs. Par exemple si nous voulons insérer un brandouillon daté de juin 1990 sans que nous en connaissions le jour, il convient d'insérer de la manière suivante :
Voici maintenant comment les requêtes doivent se présenter dans différents cas de figure :
Rechercher les brandouillons datés du 4/5/1990 :
SELECT ...
FROM ...
WHERE DPI_DATE_MIN = '1990-05-04'
AND DPT_DATE_MIN = DATE_MAX
SELECT ...
FROM ...
WHERE DPI_DATE_MAX = '1990-05-04'
AND DPT_DATE_MIN = DATE_MAX
Les deux requêtes devant donner les mêmes résultats.
Les brandouillons datant de mai 1990 :
SELECT ...
FROM ...
WHERE DPI_DATE_MIN >= '1990-05-01'
AND DPI_DATE_MAX <= '1990-05-31'
Les brandouillons de l'an 1990 :
SELECT ...
FROM ...
WHERE DPI_DATE_MIN >= '1990-01-01'
AND DPI_DATE_MAX <= '1990-12-31'
Les brandouillons entre le 4/5/1990 et le 8/7/1990 :
SELECT ...
FROM ...
WHERE DPI_DATE_MIN >= '1990-05-04'
AND DPI_DATE_MAX <= '1990-07-08'
4.3. Affinage
Ces deux modèles possèdent néanmoins un inconvénient... En effet, si notre utilisateur veut reprendre dans sa requête les bidules situées entre le 4/5/1990 et le 8/7/1990, il convient de se demander si un zirzornifle ou un brandouillon daté de mai 1990 sans précision de jour doit figurer dans le résultat ! Car l'abscence de la connaissance du jour, ne signifie nullement qu'il est obligatoirement inférieur au 4 mai 1990...
Dans ce cas, que l'on pourrait apeller "critère externe", lever cette ambiguité peut être fait de différentes manières...
Dans la première représentation (dates composites) une simple modification de la requête suffit :
SELECT ...
FROM ...
WHERE (COALESCE(DPT_JOUR, 31),
COALESCE(DPT_MOIS, 5),
COALESCE(DPT_AN, 0)) >= (4, 5, 1990)
AND (COALESCE(DPT_JOUR, 1),
COALESCE(DPT_MOIS, 7),
COALESCE(DPT_AN, 0)) <= (8, 7, 1990)
Les valeurs du coalesce sont, dans le premier prédicat de filtrage le dernier jour du mois et le mois de la borne basse du filtre, dans le second prédicat le premier jour du mois et le mois de la borne haute du filtre.
Le problème est plus complexe concernat la seconde représentation. En effet l'indication de l'imprécision de la date nous est donné par le fait que DTI_DATE_MIN est différent de DTI_DATE_MAX. Il faut alors réaliser une requête plus complexe :
SELECT ...
FROM ...
-- date exacteWHERE (DPI_DATE_MIN >= '1990-05-04'
AND DPI_DATE_MAX <= '1990-07-08'
AND DPI_DATE_MIN = DPI_DATE_MAX)
-- date partielleOR (DPI_DATE_MIN >= '1990-05-01'
AND DPI_DATE_MAX <= '1990-07-31'
AND DPI_DATE_MIN <> DPI_DATE_MAX)
Dans le second prédicat, on reprend les dates de début de mois de la borne basse et de fin de mois de la borne haute si les dates min et max sont différentes.
5. Conclusion
Voici un ensemble de données et ses règles qui marchent de manière irréprochable afin de traiter tous les cas de figure de manipulation portant sur des dates. Bien entendu vous pouvez transformer ces requêtes en procédures stockées voire en fonction utilisateurs si votre SGBDR en est doté.
Mais, peut être le volume de données à manipuler vous effraye t-il ? A titre d'indication, sous SQL server v7 le volume des données pour 30 années de dates (2000 à 2030), soit plus de 11 300 lignes, représente : 6 Mo index compris... Pour 130 années, c'est à dire les dates de 1900 à 2030 le volume des données est de 15Mo... (plus de 47 500 lignes). Quand son sait que la clef de cette table est une date et se trouve organisée généralement en cluster, on ne manipule jamais plus de 1 Mo de données si l'on ne fait pas de calculs sur une plage de dates de plus de 5 à 6 ans.
6. ANNEXE - les scripts de création du modèle de planning...
/****************************************************************************
SCRIPTS TRANSACT SQL DES PROCÉDURES STOCKÉES POUR ALIMENTATION DES DONNÉES
****************************************************************************/
CREATE PROCEDURE SP_PLN_CREATE_ONE_YEAR @AN INTEGERAS
/*****************************************************************************
TITRE : PROCEDURE STOCKÉE MS SQL Server "SP_PLN_CREATE_ONE_YEAR"
AUTEUR : Frédéric BROUARD - 2002-09-06
ARGUMENT : @AN INTEGER entier représentant l'année du calendrier dont les
dates sont à stocker
APPELÉE : par la procédure stockée SP_PLN_CREATE_YEAR qui vérifie les
années à créer
******************************************************************************/
-- NOTA les dates sont calculées pour une seule année en débordant-- éventuellement de quelques jours pour stocker des semaines entièresDECLARE @JOUR DATETIME
DECLARE @JOUR_DE_LAN DATETIME
DECLARE @JOUR_DEBUT DATETIME
DECLARE @JOUR_FIN DATETIME
DECLARE @i integerDECLARE @JOUR_FIN_SEMAINE DATETIME
DECLARE @leapYear bit
DECLARE @ALEA INTEGERSETNOCOUNTON-- indique de commencer la numérotation des jours de semaine à lundiSET DATEFIRST 1
-- foamt ISO des datesSET DATEFORMAT YMD
SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSACTION TRAN_INS_DATES
-- création des dates SET @JOUR_DE_LAN = CAST(CAST(@AN ASVARCHAR(4)) + '-01-01' AS DATETIME)
SET @JOUR_DEBUT = DATEADD(DAY, -6, @JOUR_DE_LAN) -- soit le 26 décembre an - 1SET @JOUR_FIN = CAST(CAST(@AN + 1 ASVARCHAR(4)) + '-01-01' AS DATETIME)
SET @JOUR_FIN = DATEADD(DAY, 5, @JOUR_FIN) -- soit le 6 janvier an + 1SET @JOUR = @JOUR_DEBUT
-- vérification de présence des années pour débordement des dates-- l'année avant @AN existe t-elle ?SET @i = DATEPART(YEAR, @JOUR_DEBUT)
IFNOTEXISTS(SELECT *
FROM TR_PLN_ANNEE_PAN
WHERE PAN_ID = @i)
BEGINIF @i % 4 = 0
SET @leapYear = 1
ELSESET @leapYear = 0
INSERTINTO TR_PLN_ANNEE_PAN
VALUES (@i, @leapYear)
IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN END
END
-- l'année après @AN existe t-elle ? SET @i = DATEPART(YEAR, @JOUR_FIN)
IFNOTEXISTS(SELECT *
FROM TR_PLN_ANNEE_PAN
WHERE PAN_ID = @i)
BEGINIF @i % 4 = 0
SET @leapYear = 1
ELSESET @leapYear = 0
INSERTINTO TR_PLN_ANNEE_PAN
VALUES (@i, @leapYear)
IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN END
END
-- création de l'année @ANIFNOTEXISTS(SELECT *
FROM TR_PLN_ANNEE_PAN
WHERE PAN_ID = @AN)
BEGINIF @AN % 4 = 0
SET @leapYear = 1
ELSESET @leapYear = 0
INSERTINTO TR_PLN_ANNEE_PAN
VALUES (@AN, @leapYear)
IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN END
END
WHILE @JOUR < @JOUR_FIN
BEGINSET @ALEA = RAND() * 100000
IFNOTEXISTS (SELECT *
FROM T_PLN_JOUR_PJR
WHERE PJR_DATE = @JOUR)
BEGININSERTINTO T_PLN_JOUR_PJR (PJR_DATE, PAN_ID, PMS_ID, PJM_ID, PJS_ID, PJA_ID, PJR_RANG_JOUR, PJR_ALEA)
VALUES (@JOUR,
DATEPART(YEAR, @JOUR),
DATEPART(MONTH, @JOUR),
DATEPART(DAY, @JOUR),
DATEPART(WEEKDAY, @JOUR),
DATEPART(DAYOFYEAR, @JOUR),
CAST(@JOUR ASINTEGER),
@ALEA)
IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN ENDENDSET @JOUR = DATEADD(DAY, 1, @JOUR)
END
/* Update des trimestres et semestres */
UPDATE T_PLN_JOUR_PJR
SET PTR_ID = 1, PST_ID = 1
WHERE DATEPART(MONTH, PJR_DATE) BETWEEN 1 AND 3
AND PAN_ID = @AN AND PTR_ID ISNULLIF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN ENDUPDATE T_PLN_JOUR_PJR
SET PTR_ID = 2, PST_ID = 1
WHERE DATEPART(MONTH, PJR_DATE) BETWEEN 4 AND 6
AND PAN_ID = @AN AND PTR_ID ISNULLIF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN ENDUPDATE T_PLN_JOUR_PJR
SET PTR_ID = 3, PST_ID = 2
WHERE DATEPART(MONTH, PJR_DATE) BETWEEN 7 AND 9
AND PAN_ID = @AN AND PTR_ID ISNULLIF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN ENDUPDATE T_PLN_JOUR_PJR
SET PTR_ID = 4, PST_ID = 2
WHERE DATEPART(MONTH, PJR_DATE) BETWEEN 10 AND 12
AND PAN_ID = @AN AND PTR_ID ISNULLIF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN END-- création des semaines de l'année -- calage premier jour de semaineSET @JOUR = @JOUR_DE_LAN
IF DATEPART(WEEKDAY, @JOUR) > 4
BEGIN
WHILE DATEPART(WEEKDAY, @JOUR) <> 1
SET @JOUR = DATEADD(DAY, +1, @JOUR)
ENDELSEBEGIN
WHILE DATEPART(WEEKDAY, @JOUR) <> 1
SET @JOUR = DATEADD(DAY, -1, @JOUR)
ENDSET @i = 1
WHILE DATEADD(DAY, 7, @JOUR) < @JOUR_FIN
BEGINUPDATE T_PLN_JOUR_PJR
SET PSM_ID = @i
WHERE PJR_DATE BETWEEN @JOUR AND DATEADD(DAY, 6, @JOUR)
IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN ENDSET @JOUR = DATEADD(DAY, 7, @JOUR)
SET @i = @i + 1
ENDSETNOCOUNTOFFCOMMIT TRANSACTION TRAN_INS_DATES
RETURN
LBL_ERROR:
ROLLBACK TRANSACTION TRAN_INS_DATES
GO
/******************************************************************************
TITRE : PROCEDURE STOCKÉE MS SQL Server "SP_PLN_CREATE_YEAR"
AUTEUR : Frédéric BROUARD - 2002-09-06
ARGUMENT : @AN INTEGER entier dans les limtes 1900 / 200 représentant
l'année du calendrier dont les dates sont à stocker
APPELS : la procédure stockée SP_PLN_CREATE_ONE_YEAR qui créé une année
de date
******************************************************************************/
CREATE PROCEDURE SP_PLN_CREATE_YEAR @AN INTEGERAS-- alimente les tables :-- TR_PLN_JOUR_ANNEE_PJA -- TR_PLN_ANNEE_PAN-- TR_PLN_JOUR_PJR-- TR_PLN_SEMAINE_PSM-- vérifaction des limites d'utilisation-- limite de calcul : année comprise entre 1601 et 2399IF @AN < 1901 OR @AN > 2099
BEGINDECLARE @TXT_ERROR VARCHAR(300)
SET @TXT_ERROR = 'Il n''est pas possible de calculer le calendrier pour des années hors de la plage 1900 / 2100.'
+' En l''occurence vous avez tené de calculer les dates de l''année %d.'
RAISERROR (@TXT_ERROR, 16, 1, @AN)
RETURN
END--DECLARE @JOUR_DE_LAN DATETIMEDECLARE @MIN_AN INTEGERDECLARE @MAX_AN INTEGERDECLARE @i integer--DECLARE @JOUR_FIN_SEMAINE DATETIME--DECLARE @leapYear bitSETNOCOUNTONSET DATEFORMAT YMD
--SET @JOUR_DE_LAN = CAST(CAST(@AN AS VARCHAR(4)) + '-01-01' AS DATETIME)SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSACTION
-- création des jours de l'année de 1 à 366 si cela n'est pas déjà fait.IFNOTEXISTS(SELECT 1
FROM TR_PLN_JOUR_ANNEE_PJA
HAVINGCOUNT(*) = 366)
BEGINSET @i = 1
WHILE @i <= 366
BEGININSERTINTO TR_PLN_JOUR_ANNEE_PJA
VALUES (@i)
IF @@ERROR <> 0 BEGINROLLBACK RETURN ENDSET @i = @i + 1
END
END
-- création des numéro de semaine de 1 à 53 si cela n'est pas déjà fait.IFNOTEXISTS(SELECT 1
FROM TR_PLN_SEMAINE_PSM
HAVINGCOUNT(*) = 53)
BEGINSET @i = 1
WHILE @i <= 53
BEGININSERTINTO TR_PLN_SEMAINE_PSM
VALUES (@i)
IF @@ERROR <> 0 BEGINROLLBACK RETURN ENDSET @i = @i + 1
END
END
COMMIT TRANSACTION
-- l'année à créer a t-elle déjà été créée ?IFEXISTS (SELECT 1
FROM T_PLN_JOUR_PJR
GROUP BY PJR_DATE
HAVINGCOUNT(*) >= 365)
-- oui, alors retour
RETURN
-- non, alors on regarde si d'autres années ont été crééesSELECT @MIN_AN = MIN(PAN_ID), @MAX_AN = MAX(PAN_ID)
FROM T_PLN_JOUR_PJR
GROUP BY PAN_ID
HAVINGCOUNT(*) >= 365
-- le calendrier est vide : une seule année est à créerIF @MIN_AN ISNULLAND @MAX_AN ISNULLBEGIN
EXEC SP_PLN_CREATE_ONE_YEAR @AN
RETURN
END-- le calendrier est déjà rempli et l'année à créer si situe-- avant les années déjà crééesIF @AN < @MIN_AN
BEGIN
WHILE @AN < @MIN_AN
BEGIN
EXEC SP_PLN_CREATE_ONE_YEAR @AN
SET @AN = @AN + 1
END
RETURN
END-- le calendrier est déjà rempli et l'année à créer si situe-- après les années déjà crééesIF @AN > @MAX_AN
BEGIN
WHILE @AN > @MIN_AN
BEGIN
EXEC SP_PLN_CREATE_ONE_YEAR @AN
SET @AN = @AN - 1
END
RETURN
ENDGO
Exemple d'appel du script :
SP_PLN_CREATE_YEAR 2000 => création des dates de l'année 2000 du 26/12/1999 au 6/1/2001
SP_PLN_CREATE_YEAR 2010 => création des dates de toutes les années entre 2001 et 2010
REQUÊTE DE MISE A JOUR DES RANGS
********************************
-- maj du rang des annéesUPDATE T_PLN_JOUR_PJR
SET PJR_RANG_AN =
CAST(
(CAST(PAN_ID AS FLOAT) - 1899.0)
+ (CAST(PJR_RANG_JOUR AS FLOAT)
- (SELECT CAST(PJR2.PJR_RANG_JOUR AS FLOAT)
FROM T_PLN_JOUR_PJR PJR2
WHERE PJR2.PJM_ID = 1
AND PJR2.PMS_ID = 1
AND PJR2.PAN_ID = PJR.PAN_ID +1))
/ (SELECT CAST(COUNT(*) AS FLOAT)
FROM T_PLN_JOUR_PJR PJR3
WHERE PJR3.PAN_ID = PJR.PAN_ID)
AS DECIMAL(10,4))
FROM T_PLN_JOUR_PJR PJR
-- maj du rang des moisUPDATE T_PLN_JOUR_PJR
SET PJR_RANG_MOIS =
CAST(
(CAST(PAN_ID AS FLOAT) - 1900.0) * 12 + PMS_ID + 1
+ (CAST(PJR_RANG_JOUR AS FLOAT)
- (SELECT CAST(PJR2.PJR_RANG_JOUR AS FLOAT)
FROM T_PLN_JOUR_PJR PJR2
WHERE PJR2.PJM_ID = 1
AND PJR2.PMS_ID = (PJR.PMS_ID % 12) + 1
AND PJR2.PAN_ID =
CASE
WHEN PJR.PMS_ID + 1 = 13
THEN PJR.PAN_ID +1
ELSE PJR.PAN_ID
END))
/ (SELECT CAST(COUNT(*) AS FLOAT)
FROM T_PLN_JOUR_PJR PJR3
WHERE PJR3.PMS_ID = PJR.PMS_ID
AND PJR3.PAN_ID = PJR.PAN_ID)
AS DECIMAL(10,4))
FROM T_PLN_JOUR_PJR PJR
-- maj du rang des semainesUPDATE T_PLN_JOUR_PJR
SET PJR_RANG_SEMAINE =
CAST(
CAST(PSM_ID AS FLOAT) + 51
+ (SELECTSUM(MAX_PSM_ID)
FROM (SELECTMAX(PSM_ID) AS MAX_PSM_ID
FROM T_PLN_JOUR_PJR
WHERE PAN_ID < PJR.PAN_ID
GROUP BY PAN_ID)
T )
+ (CAST(PJS_ID AS FLOAT) - 1) / 7 )
AS DECIMAL(10,4))
FROM T_PLN_JOUR_PJR PJR
-- maj du rang des trimestresUPDATE T_PLN_JOUR_PJR
SET PJR_RANG_TRIMESTRE =
CAST((CAST(PAN_ID AS FLOAT) - 1900) * 4 + PTR_ID
-- + nombre de jours écoulé
+ (CAST((SELECTCOUNT(*)
FROM T_PLN_JOUR_PJR PJR2
WHERE PJR2.PTR_ID = PJR.PTR_ID
AND PJR2.PAN_ID = PJR.PAN_ID
AND PJR2.PJR_DATE <= PJR.PJR_DATE) AS FLOAT) - 1)
-- divisé par le total de jours
/ (CAST((SELECTCOUNT(*)
FROM T_PLN_JOUR_PJR PJR3
WHERE PJR3.PTR_ID = PJR.PTR_ID
AND PJR3.PAN_ID = PJR.PAN_ID) AS FLOAT))
AS DECIMAL(10,4))
FROM T_PLN_JOUR_PJR PJR
-- maj du rang des semestresUPDATE T_PLN_JOUR_PJR
SET PJR_RANG_SEMESTRE =
CAST((CAST(PAN_ID AS FLOAT) - 1900) * 2 + PST_ID
-- + nombre de jours écoulé
+ (CAST((SELECTCOUNT(*)
FROM T_PLN_JOUR_PJR PJR2
WHERE PJR2.PST_ID = PJR.PST_ID
AND PJR2.PAN_ID = PJR.PAN_ID
AND PJR2.PJR_DATE <= PJR.PJR_DATE) AS FLOAT) - 1)
-- divisé par le total de jours
/ (CAST((SELECTCOUNT(*)
FROM T_PLN_JOUR_PJR PJR3
WHERE PJR3.PST_ID = PJR.PST_ID
AND PJR3.PAN_ID = PJR.PAN_ID) AS FLOAT))
AS DECIMAL(10,4))
FROM T_PLN_JOUR_PJR PJR
En prime, voici comment calculer les dates fériées mobiles des fêtes chrétiennes, le programme est une procédure écrite pour Transact SQL :
******************************************************************************
calcul des dates fériées chrétiennes dans le calendrier grégorien
******************************************************************************
Algorithme conçu par Claus Tøndering .
Version 2.0 - 11 Nov 1998
Copyright and disclaimer
------------------------
This document is Copyright (C) 1998 by Claus Tondering.
E-mail: claus@tondering.dk.
The document may be freely distributed, provided this
copyright notice is included and no money is charged for
the document.
This document is provided "as is". No warranties are made as
to its correctness.
This algorithm is based in part on the algorithm of Oudin (1940) as
quoted in "Explanatory Supplement to the Astronomical Almanac",
P. Kenneth Seidelmann, editor.
People who want to dig into the workings of this algorithm, may be
interested to know that
G is the Golden Number-1
H is 23-Epact (modulo 30)
I is the number of days from 21 March to the Paschal full moon
J is the weekday for the Paschal full moon (0=Sunday, 1=Monday,
etc.)
L is the number of days from 21 March to the Sunday on or before
the Paschal full moon (a number between -6 and 28
******************************************************************************
Le lundi de Pâques est déterminé par :
Dimanche de Pâques + 1 jour
Le Jeudi de l'ascension est déterminé par :
Dimanche de Pâques + 39 jours
Le Lundi de pentecôte est déterminé par :
Dimanche de Pâques + 50 jours
CREATE PROCEDURE SP_PLN_ADD_JOUR_FERIE_MOBILE_CHRETIEN @AN INT
ASSET DATEFORMAT YMD
DECLARE @G INT
DECLARE @I INT
DECLARE @J INT
DECLARE @C INT
DECLARE @H INT
DECLARE @L INT
DECLARE @JourPaque INT
DECLARE @MoisPaque INT
DECLARE @DimPaque DATETIME
DECLARE @LunPaque DATETIME
DECLARE @JeuAscension DATETIME
DECLARE @LunPentecote DATETIME
SET @G = @AN % 19
SET @C = @AN / 100
SET @H = (@C - @C / 4 - (8 * @C + 13) / 25 + 19 * @G + 15) % 30
SET @I = @H - (@H / 28) * (1 - (@H / 28) * (29 / (@H + 1)) * ((21 - @G) / 11))
SET @J = (@AN + @AN / 4 + @I + 2 - @C + @C / 4) % 7
SET @L = @I - @J
SET @MoisPaque = 3 + (@L + 40) / 44
SET @JourPaque = @L + 28 - 31 * (@MoisPaque / 4)
SET DATEFORMAT YMD
SET @DimPaque = CAST(CAST(@AN ASVARCHAR(4)) + '-'
+ CAST(@MoisPaque ASVARCHAR(2)) + '-'
+ CAST(@JourPaque ASVARCHAR(2)) AS DATETIME)
SET @LunPaque = DATEADD(DAY, 1, @DimPaque)
SET @JeuAscension = DATEADD(DAY, 39, @DimPaque)
SET @LunPentecote = DATEADD(DAY, 50, @DimPaque)
SELECT 'Dimanche' AS JOUR, 'Pâques' AS FETE, @DimPaque AS DATE_FETE
UNION
SELECT 'Lundi' AS JOUR, 'Pâques' AS FETE, @LunPaque AS DATE_FETE
UNION
SELECT 'Jeudi' AS JOUR, 'Ascension' AS FETE, @JeuAscension AS DATE_FETE
UNION
SELECT 'Lundi' AS JOUR, 'Pentecôte AS FETE, @LunPentecote AS DATE_FETE
GO
7. Pour en savoir plus sur le sujet
LIVRES :
Developping Time-Oriented Database Applications in SQL - Ricahrds T. Snodgrass - Morgan Kauffmann - 2000 Temporal Data and the Relational Model - C. J. Date, Hugh Darwen, Nikos A. Lorentzos - Morgan Kauffmann - 2000
A cette date arbitraire, on trouve : - discours de Pompidou à Aurillac - naissance de Gérald Gardrinier dit De Palmas, chanteur - naissance d'Alain Roche footballeur - publication au JO du décret n° 67-896 du 6 octobre 1967 - fondation du judo club de Nangis - décès de Marcel Aymé - création de la Ligue du Limousin de Voile - grand prix moto du Japon vainqueur Mike HAILWOOD sur 350 cc (HONDA)
