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

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 Logged on to comment or reply to a post.

mahesh kumar January 12, 2016 at 6:17 am

Nice Document..Keep posting more SQL Script HANA examples. Thanks

like (0)
Veena R January 12, 2016 at 7:06 am

good document and its very clear with the example

like (0)
Nihal Ahmed January 12, 2016 at 3:01 pm

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

like (0)
1. Nagababu Tubati Post authorJanuary 16, 2016 at 5:15 pm
  
  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
  
  like (0)