I have a problem statement:
Write a Stored Procedure that populates a table with certain date attributes. The data would be populated for 1 year. For example the date 14-07-2020 is passed as an input parameter, then the stored procedure will populate those attributes for all the dates present within the year 2020. The primary key for this table would be date column.
Constraint: More than one insert statement cannot be used
|
The structure of table is given in the image.
My implementation of snowflake code for the problem statement is:
create or replace table DateTimeDimensions (SKDate varchar(50), KeyDate varchar(50), Date varchar(50), CalendarDay int, CalendarMonth int, CalendarQuarter int, CalendarYear int, DayNameLog varchar(50), DayNameShort varchar(50), DayNumOfWeek int, DayNumofYear int, DaySuffix varchar(10), FiscalWeek int, FiscalPeriod int, FiscalQuarter int, FiscalYear int, FiscalYear_Period int);
select * from DateTimeDimensions
truncate table DateTimeDimensions
drop table DateTimeDimensions
CREATE OR REPLACE PROCEDURE uspPopulateDateTimeTable(startDate TIMESTAMP)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
{
var currentDate = startDate;
var endDate = DATEADD('YEAR', 1, startDate);
while (currentDate < endDate) {
var SKDate = currentDate.format('YYYYMMDD');
var KeyDate = currentDate.format('MM/DD/YYYY');
var Date = currentDate.format('MM/DD/YYYY');
var CalendarDay = currentDate.getDate();
var CalendarMonth = currentDate.getMonth() + 1;
var CalendarQuarter = Math.ceil(CalendarMonth / 3);
var CalendarYear = currentDate.getFullYear();
var DayNameLong = currentDate.toLocaleString('en-US', { weekday: 'long' });
var DayNameShort = currentDate.toLocaleString('en-US', { weekday: 'short' });
var DayNumberOfWeek = currentDate.getDay() + 1;
var DayNumberOfYear = Math.ceil((currentDate - new Date(currentDate.getFullYear(), 0, 1)) / 86400000);
var DaySuffix = CalendarDay + (CalendarDay % 10 === 1 && CalendarDay !== 11 ? 'st' : (CalendarDay % 10 === 2 && CalendarDay !== 12 ? 'nd' : (CalendarDay % 10 === 3 && CalendarDay !== 13 ? 'rd' : 'th')));
var FiscalWeek = currentDate.getWeek(5);
var FiscalPeriod = CalendarMonth;
var FiscalQuarter = CalendarQuarter;
var FiscalYear = CalendarYear;
var FiscalYear_Period = CalendarYear + padLeft(CalendarMonth.toString(), 2, '0');
var sqlStatement = 'INSERT INTO DateTimeDimensions (SKDate, KeyDate, Date, CalendarDay, CalendarMonth, CalendarQuarter, CalendarYear, DayNameLong, DayNameShort, DayNumberOfWeek, DayNumberOfYear, DaySuffix, FiscalWeek, FiscalPeriod, FiscalQuarter, FiscalYear, FiscalYear_Period) ' +
'VALUES ('' + SKDate + '', '' + KeyDate + '', '' + Date + '', ' + CalendarDay + ', ' + CalendarMonth + ', ' + CalendarQuarter + ', ' + CalendarYear + ', '' + DayNameLong + '', '' + DayNameShort + '', ' + DayNumberOfWeek + ', ' + DayNumberOfYear + ', '' + DaySuffix + '', ' + FiscalWeek + ', ' + FiscalPeriod + ', ' + FiscalQuarter + ', ' + FiscalYear + ', '' + FiscalYear_Period + '');';
snowflake.execute({ sqlText: sqlStatement });
currentDate = DATEADD('DAY', 1, currentDate);
}
return 'DateTimeDimensions table populated successfully.';
}
$$;
CALL uspPopulateDateTimeTable('2020-07-14 00:00:00');
But it is giving me error as:
JavaScript execution error: Uncaught ReferenceError: startDate is not defined in USPPOPULATEDATETIMETABLE at ' var currentDate = startDate;' position 22 stackstrace: USPPOPULATEDATETIMETABLE line: 3
Basically the error says that
var currentDate = startDate;
is not working because startDate is not defined.
I have searched online and read the documentation properly but can’t find out the solution for this error. Help me in solving this.
I tried to search thoroughly through the Snowflake documentation for writing stored procedure using Javascript and SQL but it was of no help.
Again and Again same error was occuring.
I tried to implement using SQL sripting instead of Javascript but it gave even more error for the same logic.
I have not tried it using CTE because I think it will be more complicated.
