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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
CREATE OR REPLACE PROCEDURE SP_CREATE_ONE_YEAR (AN INTEGER)
IS
DECLARE JOUR DATE
DECLARE JOUR_DE_LAN DATE
DECLARE JOUR_DEBUT DATE
DECLARE JOUR_FIN DATE
DECLARE i integer
DECLARE JOUR_FIN_SEMAINE DATE
DECLARE leapYear bit
DECLARE ALEA INTEGER
SET NOCOUNT ON
SET DATEFIRST 1
--SET DATEFORMAT YMD
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION TRAN_INS_DATES
SET JOUR_DE_LAN = to_date(to_char(AN) + '-01-01')
SET JOUR_DEBUT = JOUR_DE_LAN - 6 DAY
SET JOUR_FIN = to_date(to_char(AN + 1) + '-01-01')
SET JOUR_FIN = adddate(JOUR_FIN, INTERVAL 5 DAY)
SET JOUR = JOUR_DEBUT
SET i = to_char(JOUR_DEBUT, "yyyy")
IF NOT EXISTS(SELECT * FROM TR_BISSEXTILE WHERE PAN_ID = i)
BEGIN
IF i % 4 = 0
SET leapYear = 1
ELSE
SET leapYear = 0
INSERT INTO TR_BISSEXTILE
VALUES (i, leapYear)
--IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN END
END
SET i = to_char( JOUR_FIN, "yyyy")
IF NOT EXISTS(SELECT * FROM TR_BISSEXTILE WHERE PAN_ID = i)
BEGIN
IF i % 4 = 0
SET leapYear = 1
ELSE
SET leapYear = 0
INSERT INTO TR_BISSEXTILE VALUES (i, leapYear)
IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN END
END
IF NOT EXISTS(SELECT * FROM TR_BISSEXTILE WHERE PAN_ID = AN)
BEGIN
IF AN % 4 = 0
SET leapYear = 1
ELSE SET leapYear = 0
INSERT INTO TR_BISSEXTILE
VALUES (AN, leapYear)
--IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN END
END
WHILE JOUR < JOUR_FIN
BEGIN
SET ALEA = RAND() * 100000
IF NOT EXISTS (SELECT * FROM DIM_TEMPS WHERE PJR_DATE = JOUR)
BEGIN
INSERT INTO DIM_TEMPS (PJR_DATE, PAN_ID, PMS_ID, PJM_ID, PJS_ID, PJA_ID, PJR_ALEA)
VALUES (JOUR,to_char( JOUR, "yyyy"),to_char( JOUR, "mm"),to_char( JOUR, "dd"), TO_NUMBER(TO_CHAR(JOUR, 'D'), TO_NUMBER ( TO_CHAR ( JOUR , 'DDD' ) ) ALEA)
--IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN END
END
SET JOUR = adddate(JOUR, INTERVAL 1 DAY)
END
UPDATE DIM_TEMPS
SET PTR_ID = 1, PST_ID = 1
WHERE to_char(PJR_DATE, "mm") BETWEEN 1 AND 3
AND PAN_ID = AN AND PTR_ID IS NULL
--IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN END
UPDATE DIM_TEMPS
SET PTR_ID = 2, PST_ID = 1
WHERE to_char(PJR_DATE, "mm") BETWEEN 4 AND 6
AND PAN_ID = AN AND PTR_ID IS NULL
--IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN END
UPDATE DIM_TEMPS
SET PTR_ID = 3, PST_ID = 2
WHERE to_char(PJR_DATE, "mm") BETWEEN 7 AND 9
AND PAN_ID = AN AND PTR_ID IS NULL
--IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN END
UPDATE DIM_TEMPS
SET PTR_ID = 4, PST_ID = 2
WHERE to_char(PJR_DATE, "mm") BETWEEN 10 AND 12
AND PAN_ID = AN AND PTR_ID IS NULL
--IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN END
SET JOUR = JOUR_DE_LAN
IF TO_NUMBER(TO_CHAR(JOUR, 'D') > 4
BEGIN
WHILE TO_NUMBER(TO_CHAR(JOUR, 'D') <> 1
SET JOUR = adddate(JOUR, INTERVAL 1 DAY)
END
ELSE
BEGIN
WHILE TO_NUMBER(TO_CHAR(JOUR, 'D') <> 1
SET JOUR = DATEADD(DAY, -1, JOUR)
SET JOUR = JOUR - 1 DAY
END
SET i = 1
WHILE adddate(JOUR, INTERVAL 7 DAY) < JOUR_FIN
BEGIN
UPDATE DIM_TEMPS
SET PSM_ID = i
WHERE PJR_DATE BETWEEN JOUR AND adddate(JOUR, INTERVAL 6 DAY)
--IF @@ERROR <> 0 BEGIN GOTO LBL_ERROR RETURN END
SET JOUR = (JOUR, INTERVAL 7 DAY)
SET i = i + 1
END
SET NOCOUNT OFF
COMMIT TRANSACTION TRAN_INS_DATES
RETURN
LBL_ERROR:
ROLLBACK TRANSACTION TRAN_INS_DATES
/ |
