Muhammad Nadeem Chaudhry's Blog


Time Dimension – SQL Query

Posted in Uncategorized by Muhammad Nadeem Chaudhry on January 30, 2014

This query generates a relational table storing the information needed to create an OLAP time dimension.

CREATE TABLE TIMES AS
SELECT
— DAY LEVEL
TIME_ID AS DAY_ID,
INITCAP(TO_CHAR(TIME_ID,’FMMONTH DD, YYYY’)) AS DAY_DESC,
INITCAP(TO_CHAR(TIME_ID, ‘FMDAY’)) AS DAY_NAME,
TO_NUMBER(TO_CHAR(TIME_ID – 1, ‘D’)) AS DAY_OF_WEEK,
TO_NUMBER(TO_CHAR(TIME_ID, ‘DD’)) AS DAY_OF_MONTH,
TO_NUMBER(TO_CHAR(TIME_ID, ‘DDD’)) AS DAY_OF_YEAR,
1 AS DAYS_IN_DAY,

— MONTH LEVEL
TO_CHAR(TIME_ID, ‘YYYY”-M”MM’) AS MONTH_ID,
TO_CHAR(TIME_ID, ‘FMMONTH YYYY’) AS MONTH_DESC,
DECODE(MOD(TO_NUMBER(TO_CHAR(TIME_ID, ‘MM’)), 4), 0, 4, MOD(TO_NUMBER(TO_CHAR(TIME_ID, ‘MM’)), 4)) AS MONTH_OF_QUARTER,
TO_NUMBER(TO_CHAR(TIME_ID, ‘MM’)) AS MONTH_OF_YEAR,
TO_CHAR(TIME_ID, ‘FMMONTH’) AS MONTH_NAME,
LAST_DAY(TIME_ID) AS END_OF_MONTH,
TO_CHAR(LAST_DAY(TIME_ID),’DD’) AS DAYS_IN_MONTH,

— QUARTER LEVEL
TO_CHAR(TIME_ID, ‘YYYY”-Q”Q’) AS QUARTER_ID,
INITCAP(TO_CHAR(TIME_ID, ‘FMQTH “QUARTER,” YYYY’)) AS QUARTER_DESC,
TO_NUMBER(TO_CHAR(TIME_ID, ‘Q’)) AS QUARTER_OF_YEAR,
TRUNC(ADD_MONTHS(TIME_ID,3), ‘Q’) – 1 AS END_OF_QUARTER,
(TRUNC(ADD_MONTHS(TIME_ID,3), ‘Q’) – 1) – (TRUNC(TIME_ID, ‘Q’) – 1) AS DAYS_IN_QUARTER,

— YEAR LEVEL
TO_NUMBER(TO_CHAR(TIME_ID, ‘YYYY’)) AS YEAR_ID,
(TRUNC(ADD_MONTHS(TIME_ID,12), ‘YYYY’) – 1) – (TRUNC(TIME_ID, ‘YYYY’) – 1) AS DAYS_IN_YEAR,
TRUNC(ADD_MONTHS(TIME_ID,12), ‘YYYY’) – 1 AS END_OF_YEAR,

— THIS IS THE SAME FOR ALL WEEKS
7 AS DAYS_IN_WEEK,

— CALENDAR WEEK LEVEL
TO_CHAR(TIME_ID, ‘IYYY’) || ‘-CW’ || TO_CHAR(TIME_ID, ‘IW’) AS CAL_WEEK_ID,
INITCAP(TO_CHAR(TIME_ID, ‘FMIWTH “WEEK OF” IYYY’)) || ‘, ENDING ‘ || TO_CHAR(TRUNC(TIME_ID + 7, ‘IW’) – 1, ‘FMMONTH DD, YYYY’) AS CAL_WEEK_DESC,
TO_NUMBER(TO_CHAR(TIME_ID, ‘IW’)) AS CAL_WEEK_OF_YEAR,
TRUNC(TIME_ID + 7, ‘IW’) – 1 AS END_OF_CAL_WEEK

FROM (SELECT TO_DATE(’01/01/2008′,’MM/DD/YYYY’) + ROWNUM – 1 AS TIME_ID
FROM ALL_OBJECTS
WHERE ROWNUM <= 1492)     — ADD ONE MORE DAY BY SUBTRACTING MIN-MAX DATES
ORDER BY TIME_ID;