Skip to Content

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

November 24, 2014 | 335 Views |
 mtdqtdsqlscript proceduretimedatawtdytd
Follow

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)

);

Table.JPG

–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

Table-Content.JPG

Enjoy HANA


Regards

Nagababu Tubati


To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  3. Former Member

    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

    (0) 
    1. Former Member Post author

      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

      (0) 

Leave a Reply