SQLScript Procedure for YTD, QTD, MTD and WTD Time Data Generation #1

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