Good evening everybody, I’m a new toad’s user. Actually I’m doing a BI solution for commercial activity of a company.
I already construct my datawarehouse with 3dimensions (customers, products and structure) now all I have to do is to add a dimension for date to make my events organised and to add a calendar in my qlikview sheets. I started with creating a table DIMTIME :
CREATE TABLE DimDate
(DateKey NUMBER(10) primary key, Datt date, FullDateTN CHAR(10), DayOfMonth VARCHAR2(2), DaySuffix VARCHAR2(4), DayName VARCHAR2(9), DayOfWeekTN CHAR(1), DayOfWeekInMonth VARCHAR2(3), DayOfWeekInYear VARCHAR2(2), DayOfQuarter VARCHAR2(3), DayOfYear VARCHAR2(3), WeekOfMonth VARCHAR2(1), WeekOfQuarter VARCHAR2(2), WeekOfYear VARCHAR2(2), Month VARCHAR2(2), MonthName VARCHAR2(9), MonthOfQuarter VARCHAR2(2), Quarter CHAR(1), QuarterName VARCHAR2(9), Year CHAR(4), YearName CHAR(7), MonthYear CHAR(10), MMYYYY CHAR(6), FirstDayOfMonth DATE, LastDayOfMonth DATE,
FirstDayOfQuarter DATE,
LastDayOfQuarter DATE,
FirstDayOfYear DATE,
LastDayOfYear DATE,
IsHolidayTN NUMBER(1),
IsWeekday NUMBER(1),
HolidayTN VARCHAR2(50));
Now I have to declare a new local variable and table to generate my table
My boss said that he wants the years in the calendar generated automatically, I found this script in t-sql (to populate my time dimension) I already converted it to plsql but when I execute it on toad it didn’t stop showing errors messages:
–Specify Start Date and End date here
–Value of Start Date Must be Less than Your End Date
v_StartDate TIMESTAMP(3) := ‘01/01/2013’ --Starting value of Date Range
v_EndDate TIMESTAMP(3) := ‘01/01/2015’ --End Value of Date Range
–Temporary Variables To Hold the Values During Processing of Each Date of Year
v_DayOfWeekInMonth NUMBER(10);
v_DayOfWeekInYear NUMBER(10);
v_DayOfQuarter NUMBER(10);
v_WeekOfMonth NUMBER(10);
v_CurrentYear NUMBER(10);
v_CurrentMonth NUMBER(10);
v_CurrentQuarter NUMBER(10)
/Table Data type to store the day of week count for the month and year/
DECLARE @DayOfWeek TABLE (DOW NUMBER(10), MonthCount NUMBER(10), QuarterCount NUMBER(10), YearCount NUMBER(10))
INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)
–Extract and assign various parts of Values from Current Date to Variable
v_CurrentDate TIMESTAMP(3) := v_StartDate
v_CurrentMonth := TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’))
v_CurrentYear := TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YY’))
v_CurrentQuarter := TO_NUMBER(TO_CHAR(v_CurrentDate, ‘QQ’))
/********************************************************************************************/
–Proceed only if Start Date(Current date ) is less than End date you specified above
WHILE v_CurrentDate < v_EndDate
LOOP
/Begin day of week logic/
/*Check for Change in Month of the Current date if Month changed then
Change variable value*/
IF v_CurrentMonth != TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’))
THEN
UPDATE @DayOfWeek
SET MonthCount = 0
v_CurrentMonth := TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’));
END IF;
/* Check for Change in Quarter of the Current date if Quarter changed then change
Variable value*/
IF v_CurrentQuarter != TO_NUMBER(TO_CHAR(v_CurrentDate, ‘QQ’))
THEN
UPDATE @DayOfWeek
SET QuarterCount = 0
v_CurrentQuarter := TO_NUMBER(TO_CHAR(v_CurrentDate, ‘QQ’));
END IF;
/* Check for Change in Year of the Current date if Year changed then change
Variable value*/
IF v_CurrentYear != TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YY’))
THEN
UPDATE @DayOfWeek
SET YearCount = 0
v_CurrentYear := TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YY’));
END IF;
– Set values in table data type created above from variables
UPDATE @DayOfWeek
SET
MonthCount = MonthCount + 1,
QuarterCount = QuarterCount + 1,
YearCount = YearCount + 1
WHERE DOW = TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DW’))
SELECT
MonthCount,
QuarterCount,
YearCount INTO v_DayOfWeekInMonth, v_DayOfQuarter, v_DayOfWeekInYear
FROM @DayOfWeek
WHERE DOW = TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DW’));
/End day of week logic/
/* Populate Your Dimension Table with values*/
INSERT INTO DimDate
SELECT
TO_CHAR (v_CurrentDate,112) as DateKey,
v_CurrentDate AS Date,
TO_CHAR (v_CurrentDate,103) as FullDateUK,
TO_CHAR (v_CurrentDate,101) as FullDateUSA,
TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’)) AS DayOfMonth,
–Apply Suffix values like 1st, 2nd 3rd etc…
CASE
WHEN TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’)) IN (11,12,13) _
THEN FROM dual TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’))(1)) || ‘th’
WHEN SUBSTR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’)), GREATEST(-LENGTH(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’,v_CurrentDate))),-1)) = 1 _
THEN TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’))(1)) || ‘st’
WHEN SUBSTR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’)), GREATEST(-LENGTH(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’,v_CurrentDate))),-1)) = 2 _
THEN TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’))(1)) || ‘nd’
WHEN SUBSTR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’)), GREATEST(-LENGTH(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’,v_CurrentDate))),-1)) = 3 _
THEN TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’))(1)) || ‘rd’
ELSE TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DD’))(1)) || ‘th’
END LOOP; AS DaySuffix,
TO_CHAR(v_CurrentDate, ‘DW’) AS DayName,
TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DW’)) AS DayOfWeekUSA,
– check for day of week as Per US and change it as per UK format
CASE TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DW’))
WHEN 1 THEN 7
WHEN 2 THEN 1
WHEN 3 THEN 2
WHEN 4 THEN 3
WHEN 5 THEN 4
WHEN 6 THEN 5
WHEN 7 THEN 6
END
AS DayOfWeekUK,
v_DayOfWeekInMonth AS DayOfWeekInMonth,
v_DayOfWeekInYear AS DayOfWeekInYear,
v_DayOfQuarter AS DayOfQuarter,
TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DY’)) AS DayOfYear,
TO_NUMBER(TO_CHAR(v_CurrentDate, ‘WW’)) + 1 - TO_NUMBER(TO_CHAR(TO_CHAR, ‘WW’ (_
TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’))) + ‘/1/’ || TO_CHAR (_
TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YY’)))) AS WeekOfMonth,
(_ - DATEDIFF * INTERVAL ‘1’ QQ(QQ, 0, v_CurrentDate) + 0 _
v_CurrentDate) / 7) + 1 AS WeekOfQuarter,
TO_NUMBER(TO_CHAR(v_CurrentDate, ‘WW’)) AS WeekOfYear,
TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’)) AS Month,
TO_CHAR(v_CurrentDate, ‘MM’) AS MonthName,
CASE
WHEN TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’)) IN (1, 4, 7, 10) THEN 1
WHEN TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’)) IN (2, 5, 8, 11) THEN 2
WHEN TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’)) IN (3, 6, 9, 12) THEN 3
END AS MonthOfQuarter,
TO_NUMBER(TO_CHAR(v_CurrentDate, ‘QQ’)) AS Quarter,
CASE TO_NUMBER(TO_CHAR(v_CurrentDate, ‘QQ’))
WHEN 1 THEN ‘First’
WHEN 2 THEN ‘Second’
WHEN 3 THEN ‘Third’
WHEN 4 THEN ‘Fourth’
END AS QuarterName,
TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YEAR’)) AS Year,
'CY ’ || TO_CHAR (TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YEAR’))) AS YearName,
SUBSTR(TO_CHAR(v_CurrentDate, ‘MM’), 1, 3) + ‘-’ || TO_CHAR (_
TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YY’))) AS MonthYear,
SUBSTR(‘0’ || TO_CHAR (TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’))), GREATEST(-LENGTH(‘0’ || TO_CHAR(VARCHAR, (TO_NUMBER(TO_CHAR(v_CurrentDate, ‘MM’, v_CurrentDate)))),-2)) + _
TO_CHAR (TO_NUMBER(TO_CHAR(v_CurrentDate, ‘YY’))) AS MMYYYY,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (TO_NUMBER(TO_CHAR(_, ‘DD’
v_CurrentDate) - 1), v_CurrentDate))) AS FirstDayOfMonth,
CONVERT(DATETIME, CONVERT(DATE, - * INTERVAL ‘1’ DD(5)(TO_NUMBER(TO_CHAR(_, ‘DD’
(INTERVAL ‘1’ MM + v_CurrentDate)))) + INTERVAL ‘1’ MM + _
v_CurrentDate)))) AS LastDayOfMonth,
DATEDIFF * INTERVAL ‘1’ QQ(QQ, 0, v_CurrentDate) + 0 AS FirstDayOfQuarter,
DATEDIFF * INTERVAL ‘1’ QQ(QQ, -1, v_CurrentDate) + -1 AS LastDayOfQuarter,
CONVERT(DATETIME, ‘01/01/’ || TO_CHAR (TO_NUMBER(TO_CHAR(_, ‘YY’
v_CurrentDate))) AS FirstDayOfYear,
CONVERT(DATETIME, ‘12/31/’ || TO_CHAR (TO_NUMBER(TO_CHAR(_, ‘YY’
v_CurrentDate))) AS LastDayOfYear,
NULL AS IsHolidayUSA,
CASE TO_NUMBER(TO_CHAR(v_CurrentDate, ‘DW’))
WHEN 1 THEN 0
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 1
WHEN 5 THEN 1
WHEN 6 THEN 1
WHEN 7 THEN 0
END AS IsWeekday,
NULL AS HolidayUSA, Null, Null
v_CurrentDate := INTERVAL ‘1’ DD(5) + v_CurrentDate;
END
/********************************************************************************************/
Step 3.
Update Values of Holiday as per UK Government Declaration for National Holiday.
/Update HOLIDAY fields of UK as per Govt. Declaration of National Holiday/
– Good Friday April 18
UPDATE DimDate
SET HolidayUK = ‘Good Friday’
WHERE Month = 4 AND DayOfMonth = 18
– Easter Monday April 21
UPDATE DimDate
SET HolidayUK = ‘Easter Monday’
WHERE Month = 4 AND DayOfMonth = 21
– Early May Bank Holiday May 5
UPDATE DimDate
SET HolidayUK = ‘Early May Bank Holiday’
WHERE Month = 5 AND DayOfMonth = 5
– Spring Bank Holiday May 26
UPDATE DimDate
SET HolidayUK = ‘Spring Bank Holiday’
WHERE Month = 5 AND DayOfMonth = 26
– Summer Bank Holiday August 25
UPDATE DimDate
SET HolidayUK = ‘Summer Bank Holiday’
WHERE Month = 8 AND DayOfMonth = 25
– Boxing Day December 26
UPDATE DimDate
SET HolidayUK = ‘Boxing Day’
WHERE Month = 12 AND DayOfMonth = 26
–CHRISTMAS
UPDATE DimDate
SET HolidayUK = ‘Christmas Day’
WHERE Month = 12 AND DayOfMonth = 25
–New Years Day
UPDATE DimDate
SET HolidayUK = ‘New Year’‘s Day’
WHERE Month = 1 AND DayOfMonth = 1
–Update flag for UK Holidays 1= Holiday, 0=No Holiday
UPDATE DimDate
SET IsHolidayUK = CASE WHEN HolidayUK IS NULL _
THEN 0 WHEN HolidayUK IS NOT NULL THEN 1 END
Step 4.
Update Values of Holiday as per USA Govt. Declaration for National Holiday.
/Update HOLIDAY Field of USA In dimension/
/THANKSGIVING - Fourth THURSDAY in November/
UPDATE DimDate
SET HolidayUSA = ‘Thanksgiving Day’
WHERE
Month = 11
AND DayOfWeekUSA = ‘Thursday’
AND DayOfWeekInMonth = 4
/CHRISTMAS/
UPDATE DimDate
SET HolidayUSA = ‘Christmas Day’
WHERE Month = 12 AND DayOfMonth = 25
/4th of July/
UPDATE DimDate
SET HolidayUSA = ‘Independance Day’
WHERE Month = 7 AND DayOfMonth = 4
/New Years Day/
UPDATE DimDate
SET HolidayUSA = ‘New Year’‘s Day’
WHERE Month = 1 AND DayOfMonth = 1
/Memorial Day - Last Monday in May/
UPDATE DimDate
SET HolidayUSA = ‘Memorial Day’
FROM dbo.DimDate
WHERE DateKey IN
(
SELECT
MAX(DateKey)
FROM dbo.DimDate
WHERE
MonthName = ‘May’
AND DayOfWeekUSA = ‘Monday’
GROUP BY
Year,
Month
)
/Labor Day - First Monday in September/
UPDATE DimDate
SET HolidayUSA = ‘Labor Day’
FROM dbo.DimDate
WHERE DateKey IN
(
SELECT
MIN(DateKey)
FROM dbo.DimDate
WHERE
MonthName = ‘September’
AND DayOfWeekUSA = ‘Monday’
GROUP BY
Year,
Month
)
/Valentine’s Day/
UPDATE DimDate
SET HolidayUSA = ‘Valentine’‘s Day’
WHERE
Month = 2
AND DayOfMonth = 14
/Saint Patrick’s Day/
UPDATE DimDate
SET HolidayUSA = ‘Saint Patrick’‘s Day’
WHERE
Month = 3
AND DayOfMonth = 17
/Martin Luthor King Day - Third Monday in January starting in 1983/
UPDATE DimDate
SET HolidayUSA = ‘Martin Luthor King Jr Day’
WHERE
Month = 1
AND DayOfWeekUSA = ‘Monday’
AND Year >= 1983
AND DayOfWeekInMonth = 3
/President’s Day - Third Monday in February/
UPDATE DimDate
SET HolidayUSA = ‘President’‘s Day’
WHERE
Month = 2
AND DayOfWeekUSA = ‘Monday’
AND DayOfWeekInMonth = 3
/Mother’s Day - Second Sunday of May/
UPDATE DimDate
SET HolidayUSA = ‘Mother’‘s Day’
WHERE
Month = 5
AND DayOfWeekUSA = ‘Sunday’
AND DayOfWeekInMonth = 2
/Father’s Day - Third Sunday of June/
UPDATE DimDate
SET HolidayUSA = ‘Father’‘s Day’
WHERE
Month = 6
AND DayOfWeekUSA = ‘Sunday’
AND DayOfWeekInMonth = 3
/Halloween 10/31/
UPDATE DimDate
SET HolidayUSA = ‘Halloween’
WHERE
Month = 10
AND DayOfMonth = 31
/Election Day - The first Tuesday after the first Monday in November/
BEGIN
DECLARE @Holidays TABLE (ID NUMBER(10) , _
DateID int, Week TINYINT, YEAR CHR(4), DAY CHR(2))
INSERT INTO @Holidays(DateID, Year,Day)
SELECT
DateKey,
Year,
DayOfMonth
FROM DimDate
WHERE
Month = 11
AND DayOfWeekUSA = ‘Monday’
ORDER BY
YEAR,
DayOfMonth
v_CNTR NUMBER(10); v_POS NUMBER(10); v_STARTYEAR NUMBER(10); v_ENDYEAR NUMBER(10); v_MINDAY NUMBER(10);
SELECT
MIN(Year)
, MIN(Year)
, MAX(Year) INTO v_CurrentYear, v_STARTYEAR, v_ENDYEAR
FROM @Holidays
WHILE; v_CurrentYear <= v_ENDYEAR
BEGIN
SELECT COUNT([Year]) INTO v_CNTR
FROM @Holidays
WHERE Year = v_CurrentYear;
v_POS := 1;
WHILE v_POS <= v_CNTR
LOOP
SELECT MIN(DAY) INTO v_MINDAY
FROM @Holidays
WHERE
Year = v_CurrentYear
AND Week IS NULL;
UPDATE @Holidays
SET Week = v_POS
WHERE
Year = v_CurrentYear
AND Day = v_MINDAY
v_POS := v_POS + 1 FROM dual;
END LOOP;
v_CurrentYear := v_CurrentYear + 1 FROM dual;
END
UPDATE DimDate
SET HolidayUSA = ‘Election Day’
FROM dbo.DimDate DT
JOIN @Holidays HL ON (HL.DateID + 1) = DT.DateKey
WHERE
Week = 1
END
–set flag for USA holidays in Dimension
UPDATE DimDate
SET IsHolidayUSA = CASE WHEN HolidayUSA IS NULL THEN 0 WHEN HolidayUSA IS NOT NULL THEN 1 END
/*****************************************************************************************
Does anyone know how to :
-
create and run a script file on toad
-
The syntax of creating a new local and global variables and tables
Thanks