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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
8 | |
5 | |
5 | |
4 | |
4 | |
4 | |
4 | |
4 | |
3 | |
3 |