Hello Everyone,
here is HANA SQLScript Procedure that can generate Time data for every day based upon the user input Years
Calculations are based on Calendar year ( Jan-Dec ) only
–Database table for data Persistence
CREATE COLUMN TABLE ABCD.TIME_DATA(
CALDAY DATE PRIMARy KEY,
CALYEAR VARCHAR(4),
CALQUARTER VARCHAR(5),
CAlMONTH VARCHAR(6),
CALWEEK VARCHAR(6)
);
–Create a Read/Write Procedure to Insert Time data for Every day
— of Years between the given limits of years
— ( Start year and End year must be supplied as parameters for the Procedure )
CREATE PROCEDURE ABCD.GENERATE_TIME_DATA( IN P_START_YEAR Integer,
IN P_END_YEAR Integer
)
LANGUAGE SQLSCRIPT
AS
BEGIN
DECLARE LV_DATE DATE;
DECLARE LV_WEEK VARCHAR(6);
DECLARE LV_QTR VARCHAR(5);
DECLARE LV_YEAR VARCHAR(4);
DECLARE LV_MONTH VARCHAR(6);
DECLARE LV_END_DATE DATE;
LV_DATE := TO_CHAR(:P_START_YEAR) || ‘0101’;
LV_END_DATE := TO_CHAR(:P_END_YEAR) || ‘1231’;
WHILE :LV_DATE <= :LV_END_DATE DO
LV_WEEK := TO_CHAR(YEAR(:LV_DATE)) || RIGHT(‘0’ || TO_CHAR( WEEK(:LV_DATE)), 2);
LV_QTR := TO_CHAR(YEAR(:LV_DATE)) || RIGHT(QUARTER(:LV_DATE,1),1);
LV_YEAR := TO_CHAR(YEAR(:LV_DATE));
LV_MONTH := TO_CHAR(YEAR(:LV_DATE)) || RIGHT(‘0’ || TO_CHAR( MONTH(:LV_DATE)), 2);
INSERT INTO ABCD.TIME_DATA VALUES( :LV_DATE, :LV_YEAR, :LV_QTR, :LV_MONTH, :LV_WEEK);
LV_DATE := ADD_DAYS(:LV_DATE,1);
END WHILE;
END;
–Generate time data for year 2005 to 2008
CALL ABCD.GENERATE_TIME_DATA( 2005,2008);
–finally, here is the data generated
Enjoy HANA
Regards
Nagababu Tubati
Nice Document..Keep posting more SQL Script HANA examples. Thanks
good document and its very clear with the example
Hello Nagababu,
I am just wondering why would want to re-invent the wheel.
There is already a option in SAP Hana Modeler called "Generate Time Data" where one can specify parameters to cater the same need and generate time data easily unless you want to load this in tables of your own.
Let me know your thoughts.
Regards,
Nihal
Hi Nihal,
please look at various coding features used in the example. Not the final outcome. I am just evaluating how different features of HANA DB are working.
Anyways, thanks for liking my post