Je devais charger un calendrier sur environ deux siècles pour les immos dans FA.
Cette méthode peut paraître longue à première vue mais s'est révélée à la très simple, plus rapide et n'a généré aucune erreur.
Le chargement ligne à ligne est long et fastidueux. Le mieux est de saisir la première ligne manuellement dans le panneau puis de se servir de cette ligne pour en créer des nouvelles. Il y a pas mal de requête mais ça va très vite à charger.
Il faudra tenir compte du nombre variable de jours dans le mois et des années bisextiles. Attention, l'année 2000 (dernière année du XXème siècle) est bien bisextile car divisible par 400. 1900 n'était pas bisextile.
Structure de la table :
desc FA_CALENDAR_PERIODS
Nom NULL Type
----------------- -------- ------------
CALENDAR_TYPE NOT NULL VARCHAR2(15)
START_DATE NOT NULL DATE
END_DATE NOT NULL DATE
PERIOD_NUM NOT NULL NUMBER(3)
PERIOD_NAME NOT NULL VARCHAR2(15)
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER(15)
CREATED_BY NUMBER(15)
CREATION_DATE DATE
LAST_UPDATE_LOGIN NUMBER(15)
Contraintes d'unicité :
Ou sinon pour insérer la première ligne faire un insert avec des valeurs :
INSERT INTO FA_CALENDAR_PERIODS
(CALENDAR_TYPE,START_DATE,END_DATE
,PERIOD_NUM,PERIOD_NAME
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN)
VALUES
('JOUR',TO_DATE('01011900','DDMMRRRR'),TO_DATE('01011900','DDMMRRRR')
,1,'J-1-1900'
,SYSDATE,1,1,SYSDATE,1);
1 - On génère une seconde ligne à partir de la première
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,START_DATE+1,END_DATE+1
,PERIOD_NUM+1,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+1)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE = TO_DATE('01011900','DDMMYYYY')
A chaque fois il suffit d'adapter les conditions et les calculs aux lignes que l'on veut insérer.
2 - Puis on génère deux lignes à partir des deux premières
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,START_DATE+2,END_DATE+2
,PERIOD_NUM+2
,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+2)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('02011900','DDMMYYYY');
Ainsi de suite pour faire le premier mois.
3 - INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,START_DATE+4,END_DATE+4
,PERIOD_NUM+4,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+4)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('04011900','DDMMYYYY');
4 - INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,START_DATE+8,END_DATE+8
,PERIOD_NUM+8,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+8)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('08011900','DDMMYYYY');
Pour finir le mois :
5 - INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,START_DATE+16,END_DATE+16
,PERIOD_NUM+16,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+16)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('15011900','DDMMYYYY');
Il faut toujours aller dans le panneau vérifier que les données sont correctement insérées et ne posent pas de problème.
------------------------
Après Janvier, on fait février, 1900 (dernière année du XVIIIème siècle) n'est pas bisextile.
Disgression : il n'y a pas d'an 0, on passe directement du 31/12/-1 (1 av. JC) au 01/01/1 (1 ap. JC). Du coup dans les calculs de date à date il faut bien penser à enlever une année. Entre par exemple le 06/06/-5 et le 06/06/5, il s'est passé (4,5+4,5) neuf ans et non dix comme beaucoup font l'erreur.
Le mois de février se fait directement :
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,1),ADD_MONTHS(END_DATE,1)
,PERIOD_NUM+31,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+31)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('28011900','DDMMYYYY');
Puis mars (le premier mois de l'année chez les romains), on adapte bien à chaque fois le nombre de jour du mois correspondant. On y va mois après mois pour ne faire d'erreur au niveau des nombres de jour. Après on fera des années puis des groupes d'années.
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,2),ADD_MONTHS(END_DATE,2)
,PERIOD_NUM+59,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+59)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('31011900','DDMMYYYY');
Avril
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,3),ADD_MONTHS(END_DATE,3)
,PERIOD_NUM+90,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+90)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('30011900','DDMMYYYY');
Mai
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,4),ADD_MONTHS(END_DATE,4)
,PERIOD_NUM+120,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+120)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('31011900','DDMMYYYY');
Juin
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,5),ADD_MONTHS(END_DATE,5)
,PERIOD_NUM+151,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+151)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('30011900','DDMMYYYY');
Juillet
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,6),ADD_MONTHS(END_DATE,6)
,PERIOD_NUM+181,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+181)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('31011900','DDMMYYYY');
Août
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,7),ADD_MONTHS(END_DATE,7)
,PERIOD_NUM+212,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+212)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('31011900','DDMMYYYY');
Septembre
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,8),ADD_MONTHS(END_DATE,8)
,PERIOD_NUM+243,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+243)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('30011900','DDMMYYYY');
Octobre
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,9),ADD_MONTHS(END_DATE,9)
,PERIOD_NUM+273,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+273)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('31011900','DDMMYYYY');
Novembre
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,10),ADD_MONTHS(END_DATE,10)
,PERIOD_NUM+304,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+304)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('30011900','DDMMYYYY');
Décembre
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,11),ADD_MONTHS(END_DATE,11)
,PERIOD_NUM+334,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+334)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('31011900','DDMMYYYY');
Et voilà les 365 premières lignes insérées.
------------------------
Pour faire l'année suivante, même méthode
1901
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,12),ADD_MONTHS(END_DATE,12)
,PERIOD_NUM,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM)||'-'||TO_CHAR(ADD_MONTHS(START_DATE,12),'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011900','DDMMYYYY') AND TO_DATE('31121900','DDMMYYYY');
On charge les années suivantes. On fera attention à 1904 (année bisextile) . Puis on pourra copier des groupes de 4 (1901=>1905; 1902=>1906; 1903=>1907; 1904=>1908), 8, 16 années et avancer rapidement dans le temps. Pas de problème pour 2000 qui est bisextile.
1902
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,12),ADD_MONTHS(END_DATE,12)
,PERIOD_NUM,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM)||'-'||TO_CHAR(ADD_MONTHS(START_DATE,12),'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011901','DDMMYYYY') AND TO_DATE('31121901','DDMMYYYY');
1903
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,12),ADD_MONTHS(END_DATE,12)
,PERIOD_NUM,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM)||'-'||TO_CHAR(ADD_MONTHS(START_DATE,12),'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011902','DDMMYYYY') AND TO_DATE('31121902','DDMMYYYY');
1904
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,12),ADD_MONTHS(END_DATE,12)
,PERIOD_NUM,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM)||'-'||TO_CHAR(ADD_MONTHS(START_DATE,12),'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011903','DDMMYYYY') AND TO_DATE(31011903','DDMMYYYY');
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,1),ADD_MONTHS(END_DATE,1)
,PERIOD_NUM+31,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+31)||'-'||TO_CHAR(START_DATE,'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011904','DDMMYYYY') AND TO_DATE('29011904','DDMMYYYY');
-- Prise en compte du J+1
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,12),ADD_MONTHS(END_DATE,12)
,PERIOD_NUM+1,SUBSTR(PERIOD_NAME,1,2)||TO_CHAR(PERIOD_NUM+1)||'-'||TO_CHAR(ADD_MONTHS(START_DATE,12),'YYYY')
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01031903','DDMMYYYY') AND TO_DATE(31121903','DDMMYYYY');
------------------------
1905-1908 à l'image de 1901-1904
INSERT INTO FA_CALENDAR_PERIODS
SELECT
CALENDAR_TYPE,ADD_MONTHS(START_DATE,48),ADD_MONTHS(END_DATE,48)
,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN
FROM FA_CALENDAR_PERIODS
WHERE CALENDAR_TYPE = 'JOUR'
AND START_DATE BETWEEN TO_DATE('01011901','DDMMYYYY') AND TO_DATE(31121904','DDMMYYYY');
1 commentaire:
SUPER MERCI ! :)
Enregistrer un commentaire