-
Notifications
You must be signed in to change notification settings - Fork 0
/
TimeDimension.txt
445 lines (444 loc) · 21.9 KB
/
TimeDimension.txt
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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
Steps: 1 Creating the base table for correcting the facts.
--------------------------------
CREATE TABLE ATG_DAY_IDNT (DAY_DT DATE, DAY_IDNT_2011 NUMBER(7),DAY_IDNT_2012
NUMBER(7));
insert into ATG_DAY_IDNT
SELECT A.DAY_DT,A.DAY_IDNT DAY_IDNT_2011,B.DAY_IDNT DAY_IDNT_2012 FROM
ATG_TIME_DAY_DM A, RDW13DM.TIME_DAY_DM B
WHERE A.DAY_DT=B.DAY_DT AND A.DAY_DT>='29-JAN-2012' ORDER BY A.DAY_DT;
SELECT * FROM ATG_DAY_IDNT;
Weeks -
CREATE TABLE ATG_WK_IDNT (WK_START_DT DATE, WK_IDNT_2011 NUMBER(6),WK_IDNT_2012
NUMBER(6));
INSERT INTO ATG_WK_IDNT
SELECT MIN(C.DAY_DT) WEEK_START_DATE,A.WK_IDNT_2011,A.WK_IDNT_2012 FROM
(SELECT
DISTINCT(A.WK_IDNT) WK_IDNT_2011,
B.WK_IDNT WK_IDNT_2012
FROM ATG_TIME_DAY_DM A, RDW13DM.TIME_DAY_DM B
WHERE A.DAY_DT=B.DAY_DT AND A.DAY_DT>='29-JAN-2012')A,
ATG_TIME_DAY_DM C
WHERE C.WK_IDNT=A.WK_IDNT_2011
GROUP BY A.WK_IDNT_2011, A.WK_IDNT_2012 ORDER BY 1;
SELECT * FROM ATG_WK_IDNT;
------------------------------------------------------------
Step :- 2 Take the back up all calendar table before starting the activity using below query..
CREATE TABLE RDW13DM.ATG_TIME_DAY_DM AS SELECT * FROM RDW13DM.TIME_DAY_DM;
CREATE TABLE RDW13DM.ATG_TIME_WK_DM AS SELECT * FROM RDW13DM.TIME_WK_DM ;
CREATE TABLE RDW13DM.ATG_TIME_MTH_DM AS SELECT * FROM RDW13DM.TIME_MTH_DM ;
CREATE TABLE RDW13DM.ATG_TIME_HALF_DM AS SELECT * FROM RDW13DM.TIME_HALF_DM ;
CREATE TABLE RDW13DM.ATG_TIME_YR_DM AS SELECT * FROM RDW13DM.TIME_YR_DM;
CREATE TABLE RDW13DM.ATG_TIME_QTR_DM AS SELECT * FROM RDW13DM.TIME_QTR_DM;
CREATE TABLE RDW13DM.ATG_TIME_WKDAY_DM AS SELECT * FROM
RDW13DM.TIME_WKDAY_DM;
CREATE TABLE RDW13DM.ATG_TIME_G_YR_DM AS SELECT * FROM RDW13DM.TIME_G_YR_DM;
CREATE TABLE RDW13DM.ATG_TIME_G_HALF_DM AS SELECT * FROM
RDW13DM.TIME_G_HALF_DM;
CREATE TABLE RDW13DM.ATG_TIME_G_MTH_DM AS SELECT * FROM RDW13DM.TIME_G_MTH_DM;
CREATE TABLE RDW13DM.ATG_TIME_G_QTR_DM AS SELECT * FROM RDW13DM.TIME_G_QTR_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_BY_DAY_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_BY_DAY_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_BY_WK_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_BY_WK_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_BY_MTH_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_BY_MTH_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_BY_QTR_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_BY_QTR_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_BY_HALF_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_BY_HALF_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_TD_DAY_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_TD_DAY_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_TD_WK_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_TD_WK_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_TD_MTH_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_TD_MTH_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_TD_QTR_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_TD_QTR_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_TD_HALF_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_TD_HALF_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_G_BY_DAY_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_G_BY_DAY_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_G_BY_MTH_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_G_BY_MTH_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_G_BY_QTR_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_G_BY_QTR_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_G_BY_HALF_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_G_BY_HALF_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_G_TD_DAY_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_G_TD_DAY_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_G_TD_MTH_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_G_TD_MTH_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_G_TD_QTR_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_G_TD_QTR_DM;
CREATE TABLE RDW13DM.ATG_TIME_TRNSFRM_G_TD_HALF_DM AS SELECT * FROM
RDW13DM.TIME_TRNSFRM_G_TD_HALF_DM;
CREATE TABLE RDW13DM.ATG_TIME_LAST_YR_BY_DAY_LFL_DM AS SELECT * FROM
RDW13DM.TIME_LAST_YR_BY_DAY_LFL_DM;
CREATE TABLE RDW13DM.ATG_TIME_LAST_YR_BY_WK_LFL_DM AS SELECT * FROM
RDW13DM.TIME_LAST_YR_BY_WK_LFL_DM;
----------------------------
Step 3:- Please use the below query to delete dimension and verify the same.
----------------------RDW13DM.TIME_DAY_DM------------------------------------
Select * from RDW13DM.TIME_DAY_DM where DAY_DT>='29-JAN-2012' order by DAY_IDNT;
SELECT COUNT(*) FROM RDW13DM.TIME_DAY_DM WHERE DAY_DT>='29-JAN-2012' ORDER BY
DAY_IDNT; --6944
--DELETE FROM RDW13DM.TIME_DAY_DM WHERE DAY_DT>='29-JAN-2012'; -- 6944 rows deleted.
SELECT COUNT(*) FROM RDW13DM.ATG_TIME_DAY_DM WHERE DAY_DT>='29-JAN-2012' ORDER BY
DAY_IDNT; -- 1071
---------------------RDW13DM.TIME_WK_DM-----------------------------------------
SELECT COUNT(*) FROM RDW13DM.TIME_WK_DM A WHERE
EXISTS (SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.WK_IDNT=B.WK_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 992
/*
DELETE FROM RDW13DM.TIME_WK_DM A WHERE
EXISTS (SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.WK_IDNT=B.WK_IDNT AND
B.DAY_DT>='29-JAN-2012'); --992 rows deleted.
*/
SELECT COUNT(*) FROM RDW13DM.TIME_WK_DM A WHERE
EXISTS (SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.WK_IDNT=B.WK_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 153
-------------------------------------------------------------
RDW13DM.TIME_MTH_DM
--UPDATE RDW13DM.TIME_MTH_DM SET MTH_END_DT='28-JAN-12' WHERE MTH_IDNT=201112;
select * from RDW13DM.TIME_MTH_DM a where
EXISTS (SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.MTH_IDNT=B.MTH_IDNT AND
B.DAY_DT>='29-JAN-2012') AND MTH_IDNT>=201201; -- 228
/*
DELETE FROM RDW13DM.TIME_MTH_DM A WHERE
EXISTS (SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.MTH_IDNT=B.MTH_IDNT AND
B.DAY_DT>='29-JAN-2012') AND MTH_IDNT>=201201; -- 228 rows deleted
*/
SELECT * FROM RDW13DM.TIME_MTH_DM A WHERE
EXISTS (SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.MTH_IDNT=B.MTH_IDNT AND
B.DAY_DT>='29-JAN-2012') AND MTH_IDNT>=201201; -- 35
---------------------------------------------------------------------------
RDW13DM.TIME_QTR_DM
select COUNT(*) from RDW13DM.TIME_QTR_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.QTR_IDNT=B.QTR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND QTR_IDNT>=20121; -- 76
/*
DELETE FROM RDW13DM.TIME_QTR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.QTR_IDNT=B.QTR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND QTR_IDNT>=20121; --76 rows deleted
*/
SELECT * FROM RDW13DM.TIME_QTR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.QTR_IDNT=B.QTR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND QTR_IDNT>=20121; --12
-----------------------------------------------------------------------
RDW13DM.TIME_HALF_DM
select * from RDW13DM.TIME_HALF_DM a where
EXISTS (SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.HALF_IDNT=B.HALF_IDNT
AND B.DAY_DT>='29-JAN-2012') AND HALF_IDNT>=20121; -- 38
/*
DELETE FROM RDW13DM.TIME_HALF_DM A WHERE
EXISTS (SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.HALF_IDNT=B.HALF_IDNT
AND B.DAY_DT>='29-JAN-2012') AND HALF_IDNT>=20121; -- 38
*/
SELECT * FROM RDW13DM.TIME_HALF_DM A WHERE
EXISTS (SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.HALF_IDNT=B.HALF_IDNT AND
B.DAY_DT>='29-JAN-2012') AND HALF_IDNT>=20121; --6
-----------------------------------------------------------------------------
RDW13DM.TIME_YR_DM
--UPDATE RDW13DM.TIME_YR_DM SET YR_END_DT = '28-JAN-2012' WHERE YR_IDNT=2011;
select COUNT(*) from RDW13DM.TIME_YR_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.YR_IDNT=B.YR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND YR_IDNT!=2011; -- 19
/*
DELETE FROM RDW13DM.TIME_YR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.YR_IDNT=B.YR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND YR_IDNT!=2011; -- 19
*/
SELECT COUNT(*) FROM RDW13DM.TIME_YR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.YR_IDNT=B.YR_IDNT AND B.DAY_DT>='29-
JAN-2012') AND YR_IDNT!=2011; --3
---------------------------------------------------------------------------
RDW13DM.TIME_WKDAY_DM
SELECT * FROM RDW13DM.TIME_WKDAY_DM; -- 7
--DELETE FROM RDW13DM.TIME_WKDAY_DM; -- 7 rows deleted
SELECT * FROM RDW13DM.TIME_WKDAY_DM; -- 0
-------------------------------------------------------------------------
RDW13DM.TIME_G_YR_DM
select COUNT(*) from RDW13DM.TIME_G_YR_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_YR_IDNT=B.G_YR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND G_YR_IDNT!=2011; --20
/*
DELETE FROM RDW13DM.TIME_G_YR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_YR_IDNT=B.G_YR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND G_YR_IDNT!=2011; --20
*/
SELECT * FROM RDW13DM.TIME_G_YR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.G_YR_IDNT=B.G_YR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND G_YR_IDNT!=2011; -4
-----------------------------------------------------
RDW13DM.TIME_G_HALF_DM
select COUNT(*) from RDW13DM.TIME_G_HALF_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_HALF_IDNT=B.G_HALF_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 40
/*
DELETE FROM RDW13DM.TIME_G_HALF_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_HALF_IDNT=B.G_HALF_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 40
*/
SELECT COUNT(*) FROM RDW13DM.TIME_G_HALF_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.G_HALF_IDNT=B.G_HALF_IDNT AND
B.DAY_DT>='29-JAN-2012'); --8
-----------------------------------------------------------------------------
RDW13DM.TIME_G_MTH_DM
select COUNT(*) from RDW13DM.TIME_G_MTH_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_MTH_IDNT=B.G_MTH_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 230
/*
DELETE FROM RDW13DM.TIME_G_MTH_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_MTH_IDNT=B.G_MTH_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 230
*/
SELECT COUNT(*) FROM RDW13DM.TIME_G_MTH_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.G_MTH_IDNT=B.G_MTH_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 37
---------------------------------------------------------------------
RDW13DM.TIME_G_QTR_DM
select COUNT(*) from RDW13DM.TIME_G_QTR_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_QTR_IDNT=B.G_QTR_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 78
/*
DELETE FROM RDW13DM.TIME_G_QTR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_QTR_IDNT=B.G_QTR_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 78
*/
SELECT COUNT(*) FROM RDW13DM.TIME_G_QTR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.G_QTR_IDNT=B.G_QTR_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 14
--------------------------------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_BY_DAY_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_BY_DAY_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.DAY_IDNT=B.DAY_IDNT AND
B.DAY_DT>='29-JAN-2012'); --6944
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_BY_DAY_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.DAY_IDNT=B.DAY_IDNT AND
B.DAY_DT>='29-JAN-2012'); --6944
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_BY_DAY_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.DAY_IDNT=B.DAY_IDNT AND
B.DAY_DT>='29-JAN-2012'); --1071
-----------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_BY_WK_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_BY_WK_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.WK_IDNT=B.WK_IDNT AND
B.DAY_DT>='29-JAN-2012'); --992
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_BY_WK_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.WK_IDNT=B.WK_IDNT AND
B.DAY_DT>='29-JAN-2012'); --992
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_BY_WK_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.WK_IDNT=B.WK_IDNT AND B.DAY_DT>='29-
JAN-2012'); --153
-----------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_BY_MTH_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_BY_MTH_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.MTH_IDNT=B.MTH_IDNT AND
B.DAY_DT>='29-JAN-2012') AND MTH_IDNT>=201201; --228
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_BY_MTH_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.MTH_IDNT=B.MTH_IDNT AND
B.DAY_DT>='29-JAN-2012') AND MTH_IDNT>=201201; --228
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_BY_MTH_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.MTH_IDNT=B.MTH_IDNT AND
B.DAY_DT>='29-JAN-2012') AND MTH_IDNT>=201201; --35
----------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_BY_QTR_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_BY_QTR_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.QTR_IDNT=B.QTR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND QTR_IDNT>=20121; --76
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_BY_QTR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.QTR_IDNT=B.QTR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND QTR_IDNT>=20121; --76
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_BY_QTR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.QTR_IDNT=B.QTR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND QTR_IDNT>=20121; --12
------------------------------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_BY_HALF_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_BY_HALF_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.HALF_IDNT=B.HALF_IDNT AND
B.DAY_DT>='29-JAN-2012') AND HALF_IDNT>=20121; -- 38
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_BY_HALF_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.HALF_IDNT=B.HALF_IDNT AND
B.DAY_DT>='29-JAN-2012') AND HALF_IDNT>=20121; -- 38
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_BY_HALF_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.HALF_IDNT=B.HALF_IDNT AND
B.DAY_DT>='29-JAN-2012') AND HALF_IDNT>=20121; -- 6
-------------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_TD_DAY_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_TD_DAY_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.DAY_IDNT=B.DAY_IDNT AND
B.DAY_DT>='29-JAN-2012'); --1272474
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_TD_DAY_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.DAY_IDNT=B.DAY_IDNT AND
B.DAY_DT>='29-JAN-2012'); --1272474
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_TD_DAY_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.DAY_IDNT=B.DAY_IDNT AND
B.DAY_DT>='29-JAN-2012'); --192052
---------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_TD_WK_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_TD_WK_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.WK_IDNT=B.WK_IDNT AND
B.DAY_DT>='29-JAN-2012'); --26394
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_TD_WK_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.WK_IDNT=B.WK_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 26394
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_TD_WK_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.WK_IDNT=B.WK_IDNT AND B.DAY_DT>='29-
JAN-2012'); --3985
--------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_TD_MTH_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_TD_MTH_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.MTH_IDNT=B.MTH_IDNT AND
B.DAY_DT>='29-JAN-2012') AND MTH_IDNT>=201201; --1482
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_TD_MTH_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.MTH_IDNT=B.MTH_IDNT AND
B.DAY_DT>='29-JAN-2012') AND MTH_IDNT>=201201 ; --1482
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_TD_MTH_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.MTH_IDNT=B.MTH_IDNT AND
B.DAY_DT>='29-JAN-2012') AND MTH_IDNT>=201201; --222
--------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_TD_QTR_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_TD_QTR_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.QTR_IDNT=B.QTR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND QTR_IDNT>=20121; --190
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_TD_QTR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.QTR_IDNT=B.QTR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND QTR_IDNT>=20121; --190
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_TD_QTR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.QTR_IDNT=B.QTR_IDNT AND
B.DAY_DT>='29-JAN-2012') AND QTR_IDNT>=20121; --30
--------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_TD_HALF_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_TD_HALF_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.HALF_IDNT=B.HALF_IDNT AND
B.DAY_DT>='29-JAN-2012') AND HALF_IDNT >= 20121; --57
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_TD_HALF_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.HALF_IDNT=B.HALF_IDNT AND
B.DAY_DT>='29-JAN-2012') AND HALF_IDNT >= 20121; --57
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_TD_HALF_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.HALF_IDNT=B.HALF_IDNT AND
B.DAY_DT>='29-JAN-2012') AND HALF_IDNT >= 20121; --9
-----------------------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_G_BY_DAY_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_G_BY_DAY_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.DAY_IDNT=B.DAY_IDNT AND
B.DAY_DT>='29-JAN-2012') AND DAY_IDNT>=2011365; --6944
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_G_BY_DAY_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.DAY_IDNT=B.DAY_IDNT AND
B.DAY_DT>='29-JAN-2012') AND DAY_IDNT>=2011365; --6944
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_G_BY_DAY_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.DAY_IDNT=B.DAY_IDNT AND
B.DAY_DT>='29-JAN-2012') AND DAY_IDNT>=2011365; --1071
-------------------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_G_BY_MTH_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_G_BY_MTH_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_MTH_IDNT=B.G_MTH_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 230
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_G_BY_MTH_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_MTH_IDNT=B.G_MTH_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 230
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_G_BY_MTH_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.G_MTH_IDNT=B.G_MTH_IDNT AND
B.DAY_DT>='29-JAN-2012'); -- 37
-------------------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_G_BY_QTR_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_G_BY_QTR_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_QTR_IDNT=B.G_QTR_IDNT AND
B.DAY_DT>='29-JAN-2012'); --78
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_G_BY_QTR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_QTR_IDNT=B.G_QTR_IDNT AND
B.DAY_DT>='29-JAN-2012'); --78
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_G_BY_QTR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.G_QTR_IDNT=B.G_QTR_IDNT AND
B.DAY_DT>='29-JAN-2012'); --14
----------------------------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_G_BY_HALF_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_G_BY_HALF_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_HALF_IDNT=B.G_HALF_IDNT AND
B.DAY_DT>='29-JAN-2012'); --40
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_G_BY_HALF_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_HALF_IDNT=B.G_HALF_IDNT AND
B.DAY_DT>='29-JAN-2012'); --40
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_G_BY_HALF_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.G_HALF_IDNT=B.G_HALF_IDNT AND
B.DAY_DT>='29-JAN-2012'); --8
-------------------------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_G_TD_DAY_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_G_TD_DAY_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.DAY_IDNT=B.DAY_IDNT AND
B.DAY_DT>='29-JAN-2012'); --1198143
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_G_TD_DAY_DM A WHERE EXISTS (SELECT '1' FROM
RDW13DM.ATG_TIME_DAY_DM B WHERE A.DAY_IDNT=B.DAY_IDNT AND B.DAY_DT>='29-JAN-2012');
--1198143 rows deleted.
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_G_TD_DAY_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.DAY_IDNT=B.DAY_IDNT AND
B.DAY_DT>='29-JAN-2012'); --189882
------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_G_TD_MTH_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_G_TD_MTH_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_MTH_IDNT=B.G_MTH_IDNT AND
B.DAY_DT>='29-JAN-2012'); --1495
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_G_TD_MTH_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_MTH_IDNT=B.G_MTH_IDNT AND
B.DAY_DT>='29-JAN-2012'); --1495
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_G_TD_MTH_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.G_MTH_IDNT=B.G_MTH_IDNT AND
B.DAY_DT>='29-JAN-2012'); --246
---------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_G_TD_QTR_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_G_TD_QTR_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_QTR_IDNT=B.G_QTR_IDNT AND
B.DAY_DT>='29-JAN-2012'); --195
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_G_TD_QTR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_QTR_IDNT=B.G_QTR_IDNT AND
B.DAY_DT>='29-JAN-2012'); --195
*/
SELECT COUNT(*) FROM RDW13DM.TIME_TRNSFRM_G_TD_QTR_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.G_QTR_IDNT=B.G_QTR_IDNT AND
B.DAY_DT>='29-JAN-2012'); --38
------------------------------------------------------------------------------
RDW13DM.TIME_TRNSFRM_G_TD_HALF_DM
select COUNT(*) from RDW13DM.TIME_TRNSFRM_G_TD_HALF_DM a where exists
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_HALF_IDNT=B.G_HALF_IDNT AND
B.DAY_DT>='29-JAN-2012'); --60
/*
DELETE FROM RDW13DM.TIME_TRNSFRM_G_TD_HALF_DM A WHERE EXISTS
(SELECT '1' FROM RDW13DM.ATG_TIME_DAY_DM B WHERE A.G_HALF_IDNT=B.G_HALF_IDNT AND
B.DAY_DT>='29-JAN-2012'); --60
*/
select COUNT(*) from RDW13DM.TIME_TRNSFRM_G_TD_HALF_DM a where exists
(SELECT '1' FROM RDW13DM.TIME_DAY_DM B WHERE A.G_HALF_IDNT=B.G_HALF_IDNT AND
B.DAY_DT>='29-JAN-2012'); --13