create table d_time_date as
SELECT to_number(TO_CHAR(TRUNC(sd + rn), 'YYYYMMDD')) day_id,
TRUNC(sd + rn) day_date,
to_char(sd + rn, 'YYYY-MM-DD') DAY_CN_DESC,
to_char(sd + rn, 'YYYY-MM-DD') DAY_EN_DESC,
TO_number(TO_CHAR(sd + rn, 'DD')) DAY_OF_MONTH,
TO_number(TO_CHAR(sd + rn, 'DDD')) DAY_OF_YEAR,
--to_number(TO_CHAR(TRUNC(sd + rn - 1), 'YYYYMMDD')) pre_day_id,
/*to_number(TO_CHAR(add_months(sd + rn, -1), 'YYYYMMDD')) LM_DAY_ID,
to_number(TO_CHAR(add_months(sd + rn, -3), 'YYYYMMDD')) LQ_DAY_ID,
to_number(TO_CHAR(add_months(sd + rn, -12), 'YYYYMMDD')) LY_DAY_ID,
to_number(TO_CHAR(add_months((TRUNC(sd + rn, 'MM')), -1), 'YYYYMMDD')) LM_START_DAY_ID,
to_number(TO_CHAR(add_months((TRUNC(sd + rn, 'Q')), -3), 'YYYYMMDD')) LQ_START_DAY_ID,
to_number(TO_CHAR(add_months((TRUNC(sd + rn, 'YEAR')), -12), 'YYYYMMDD')) LY_START_DAY_ID,
to_number(TO_CHAR((TRUNC(sd + rn, 'MM')), 'YYYYMMDD')) CM_START_DAY_ID,
to_number(TO_CHAR((TRUNC(sd + rn, 'Q')), 'YYYYMMDD')) CQ_START_DAY_ID,
to_number(TO_CHAR((TRUNC(sd + rn, 'YEAR')), 'YYYYMMDD')) CY_START_DAY_ID,
to_number(TO_CHAR(TRUNC(sd + rn), 'YYYYMM')) * 10 +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
6,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 5 + 1)) FIVEDAY_ID,
TRUNC(sd + rn, 'MM') +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
25,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 5) * 5) FIVEDAY_START_DATE,
decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
6,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 5 + 1)),
6,
last_day(sd + rn),
TRUNC(sd + rn, 'MM') + 4 +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
25,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 5) * 5)) FIVEDAY_END_DATE,
TO_CHAR(sd + rn, 'YYYY') || '-' || TO_CHAR(sd + rn, 'MM') || '-' ||
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
6,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 5 + 1)) || '(' ||
to_char(TRUNC(sd + rn, 'MM') +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
25,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 5) * 5),
'DD',
'nls_date_language = AMERICAN') || '-' ||
to_char(decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
6,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 5 + 1)),
6,
last_day(sd + rn),
TRUNC(sd + rn, 'MM') + 4 +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
25,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 5) * 5)),
'DD',
'nls_date_language = AMERICAN') || ')' FIVEDAY_CN_DESC,
decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
6,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 5 + 1)),
1,
'1st',
2,
'2nd',
3,
'3rd',
4,
'4th',
5,
'5th',
6,
'6th') || ' fiveday of ' ||
to_char(sd + rn, 'MON,YYYY', 'nls_date_language = AMERICAN') FIVEDAY_EN_DESC,
(decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
6,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 5 + 1)),
6,
last_day(sd + rn),
TRUNC(sd + rn, 'MM') + 4 +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
25,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 5) * 5))) -
(TRUNC(sd + rn, 'MM') +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
25,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 5) * 5)) + 1 FIVEDAY_DURATION,
to_number(TO_CHAR(TRUNC(sd + rn), 'YYYYMM')) * 10 +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)) TENDAY_ID,
TRUNC(sd + rn, 'MM') +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
20,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10) * 10) TENDAY_START_DATE,
decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)),
3,
last_day(sd + rn),
TRUNC(sd + rn, 'MM') + 9 +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
20,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10) * 10)) TENDAY_END_DATE,
TO_CHAR(sd + rn, 'YYYY-MM') ||
decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)),
1,
'上旬',
2,
'中旬',
3,
'下旬') TENDAY_CN_DESC,
decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)),
1,
'1st',
2,
'2nd',
3,
'3rd') || ' tenday of ' ||
to_char(sd + rn, 'MON,YYYY', 'nls_date_language = AMERICAN') TENDAY_EN_DESC,
(decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)),
3,
last_day(sd + rn),
TRUNC(sd + rn, 'MM') + 9 +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
20,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10) * 10))) -
(TRUNC(sd + rn, 'MM') +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
20,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10) * 10)) + 1 TENDAY_DURATION,
decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)),
1,
to_number(TO_CHAR(TRUNC(add_months(sd + rn, -1)), 'YYYYMM')) * 10 + 3,
to_number(TO_CHAR(TRUNC(sd + rn), 'YYYYMM')) * 10 +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)) - 1) PREV_TENDAY_ID,
to_number(TO_CHAR(TRUNC(add_months(sd + rn, -1)), 'YYYYMM')) * 10 +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)) LM_TENDAY_ID,
to_number(TO_CHAR(TRUNC(add_months(sd + rn, -3)), 'YYYYMM')) * 10 +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)) LQ_TENDAY_ID,
to_number(TO_CHAR(TRUNC(add_months(sd + rn, -12)), 'YYYYMM')) * 10 +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)) LY_TENDAY_ID,*/
to_number(TO_CHAR(TRUNC(sd + rn), 'YYYYMM')) month_id,
TRUNC(sd + rn, 'MM') MONTH_START_DATE,
--to_date(TO_CHAR(TRUNC(sd + rn), 'YYYYMM') || '01', 'YYYY/MM/DD') MONTH_START_DATE,
last_day(sd + rn) MONTH_END_DATE,
to_char(sd + rn, 'YYYY-MM') MONTH_CN_DESC,
to_char(sd + rn, 'MON YYYY', 'nls_date_language = AMERICAN') MONTH_EN_DESC,
to_number(TO_CHAR(sd + rn, 'MM')) MONTH_OF_YEAR,
last_day(sd + rn) -
to_date(TO_CHAR(TRUNC(sd + rn), 'YYYYMM') || '01', 'YYYY/MM/DD') + 1 MONTH_DURATION,
to_number(TO_CHAR(add_months(sd + rn, -1), 'YYYYMM')) PREV_MONTH_ID,
to_number(TO_CHAR(add_months(sd + rn, -3), 'YYYYMM')) LQ_MONTH_ID,
to_number(TO_CHAR(add_months(sd + rn, -12), 'YYYYMM')) LY_MONTH_ID,
to_number(TO_CHAR(sd + rn, 'YYYYQ')) QTR_ID,
to_number(TO_CHAR(sd + rn, 'Q')) QTR_OF_YEAR,
TRUNC(sd + rn, 'Q') QTR_START_DATE,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
TO_DATE('03/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
TO_DATE('06/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
TO_DATE('09/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
END) QTR_END_DATE,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
to_char(sd + rn, 'YYYY') || '-1季度'
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
to_char(sd + rn, 'YYYY') || '-2季度'
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
to_char(sd + rn, 'YYYY') || '-3季度'
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
to_char(sd + rn, 'YYYY') || '-4季度'
END) QTR_CN_DESC,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
'QTR 1,' || to_char(sd + rn, 'YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
'QTR 2,' || to_char(sd + rn, 'YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
'QTR 3,' || to_char(sd + rn, 'YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
'QTR 4,' || to_char(sd + rn, 'YYYY')
END) QTR_EN_DESC,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
TO_DATE('03/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
TO_DATE('06/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
TO_DATE('09/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
END) - TRUNC(sd + rn, 'Q') + 1 QTR_DURATION,
to_number(TO_CHAR(add_months(sd + rn, -3), 'YYYYQ')) PREV_QTR_ID,
to_number(TO_CHAR(add_months(sd + rn, -12), 'YYYYQ')) LY_QTR_ID,
/*(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
to_number(to_char(sd + rn, 'YYYY') || 1)
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
to_number(to_char(sd + rn, 'YYYY') || 1)
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
to_number(to_char(sd + rn, 'YYYY') || 2)
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
to_number(to_char(sd + rn, 'YYYY') || 2)
END) HFYR_ID,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
TO_DATE('01/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
TO_DATE('01/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
TO_DATE('07/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
TO_DATE('07/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
END) HFYR_START_DATE,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
TO_DATE('06/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
TO_DATE('06/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
END) HFYR_END_DATE,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
to_char(sd + rn, 'YYYY') || '上半年'
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
to_char(sd + rn, 'YYYY') || '上半年'
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
to_char(sd + rn, 'YYYY') || '下半年'
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
to_char(sd + rn, 'YYYY') || '下半年'
END) HFYR_CN_DESC,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
'1st Half Year of ' || to_char(sd + rn, 'YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
'1st Half Year of ' || to_char(sd + rn, 'YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
'2st Half Year of ' || to_char(sd + rn, 'YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
'2st Half Year of ' || to_char(sd + rn, 'YYYY')
END) HFYR_EN_DESC,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
TO_DATE('06/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
TO_DATE('06/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
END) - (CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
TO_DATE('01/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
TO_DATE('01/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
TO_DATE('07/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
TO_DATE('07/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
END) + 1 HFYR_DURATION,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
to_number(to_char(add_months(sd + rn, -6), 'YYYY') || 2)
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
to_number(to_char(add_months(sd + rn, -6), 'YYYY') || 2)
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
to_number(to_char(add_months(sd + rn, -6), 'YYYY') || 1)
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
to_number(to_char(add_months(sd + rn, -6), 'YYYY') || 1)
END) PREV_HFYR_ID,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
to_number(to_char(add_months(sd + rn, -12), 'YYYY') || 1)
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
to_number(to_char(add_months(sd + rn, -12), 'YYYY') || 1)
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
to_number(to_char(add_months(sd + rn, -12), 'YYYY') || 2)
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
to_number(to_char(add_months(sd + rn, -12), 'YYYY') || 2)
END) LY_HFYR_ID,*/
to_number(TO_CHAR(TRUNC(sd + rn), 'YYYY')) YEAR_ID,
TRUNC(sd + rn, 'YEAR') YEAR_START_DATE,
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY') YEAR_END_DATE,
TO_CHAR(TRUNC(sd + rn), 'YYYY') YEAR_CN_DESC,
TO_CHAR(TRUNC(sd + rn), 'YYYY') YEAR_EN_DESC,
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY') -
TRUNC(sd + rn, 'YEAR') + 1 YEAR_DURATION,
to_number(to_char(add_months(sd + rn, -12), 'YYYY')) PREV_YEAR_ID,
to_number(TO_CHAR(TRUNC(sd + rn), 'YYYY'))||'年'||to_number(TO_CHAR(sd + rn, 'MM'))||'月' MONTH_DESC,
to_number(TO_CHAR(TRUNC(sd + rn), 'YYYY'))||'年' YEAR_ONLY,
to_number(TO_CHAR(sd + rn, 'MM'))||'月' MONTH_ONLY,
to_number(TO_CHAR(sd + rn, 'W')) week_of_month,
to_number(TO_CHAR(sd + rn, 'WW')) week_of_year,
to_number(TO_CHAR(TRUNC(sd + rn), 'YYYY'))||'年第'||to_number(TO_CHAR(sd + rn, 'WW'))||'周' week_of_year_desc,
'TOTAL' total
FROM (SELECT TO_DATE('12/31/2011', 'MM/DD/YYYY') sd, rownum rn
FROM dual
CONNECT BY level <= 11323)
