Month Freeze Document

    Hi Folks,

     

    If any one interest to freeze all the data by month wise from sql and append it in the qvd to verify the changes per each month. It will help it.

     

    Let vQVDPath = 'D:\';

    Let vFreezeTableName = 'MONTH_FREEZE';

    LET vNow = Now();

    //GFIR

    $(vFreezeTableName):

    LOAD *,

      '$(vNow)' AS MonthFreezeTime,

      Floor(Today()) AS MonthFreezeDate,

      Floor(MonthEnd(Today(), -1)) AS MonthFreezeDataUptoDate;

    SQL Select * from tablename;

     

    //Check the qvd is exists or not

      LET vListQVDExists = not isnull(QVDCreateTime('$(vQVDPath)\$(vFreezeTableName).qvd'));

      //If exists , concatenate with existing qvds

      If($(vListQVDExists)) then

      //Find the maximum date

         MaxFreezeDate:

         LOAD Max(MonthFreezeDate) AS MaxFreezeDateNum Resident $(vFreezeTableName);

         //Get the maximum date_num

         Let vMaxFreezeNum = Num(Peek('MaxFreezeDateNum',-1, 'MaxFreezeDate'));

         //Drop the temporary table

         DROP Table MaxFreezeDate;

         

      Concatenate($(vFreezeTableName))

      LOAD * From

      $(vQVDPath)\$(vFreezeTableName).qvd(qvd)

      Where MonthFreezeDate <  $(vMaxFreezeNum);

      

      ENDIF;

     

    //Store freeze data into qvd

    STORE $(vFreezeTableName) into $(vQVDPath)\$(vFreezeTableName).qvd(qvd);

     

     

    //Drop the temporary table

    DROP Table $(vFreezeTableName);

     

     

    //Exit script

    EXIT Script;