Freezing the data week wise

    Hi all,

     

    Below query is used to snapshot the complete data from the database in weekly base. This is used to compare any change in existing data in database and compare the snapshot each and every week like sales, payment, etc.

    Hope it will help anyone.

     

    First time, it create new qvd later on, its concatenate the values with existing qvd.

     

    LET vFreezeDay =  'Fri'; // Freeze Day

    LET vFreezeTime = 16; // Freeze Hour (24 hours)

    LET QVDFreeze = 'D:\'; //Qvd path

     

    //Check the day and hrs

    IF WeekDay(ConvertToLocalTime(UTC(), 'GMT+08:00')) = '$(vFreezeDay)'  AND Hour(ConvertToLocalTime(UTC(), 'GMT+08:00')) = $(vFreezeTime) Then

     

      //Store the Table Name into a variable

      LET vFreezeTableName = 'WEEK_FREEZE';

     

      //Load only the current week data

      $(vFreezeTableName):

      LOAD

      *,

      $(vMinAPInvoiceCalendarWeekYear) AS FREEZE_CALENDAR_WEEK_YEAR,

      Floor(WeekName(Today())) AS  FREEZE_WEEK_NUMBER;

      SQL Select * from tableName;

     

      //Check the qvd is exists or not

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

     

      //If exists , concatenate with existing qvds

      If($(vListQVDExists)) then

      //Find the maximum week number

         MaxFreezeWeekDate:

         LOAD Max(FREEZE_WEEK_NUMBER) AS MaxFreezeWeekNumber Resident $(vFreezeTableName);

     

         //Get the maximum week_num

         Let vMaxFreezeWeekNum = Num(Peek('MaxFreezeWeekNumber',-1, 'MaxFreezeWeekDate'));

     

         //Drop the temporary table

         DROP Table MaxFreezeWeekDate;

        

      FinalTable:

      NoConcatenate

      LOAD * Resident $(vFreezeTableName);

      Concatenate

      LOAD * From

      $(QVDFreeze)$(vFreezeTableName).qvd(qvd)

      Where FREEZE_WEEK_NUMBER <  $(vMaxFreezeWeekNum);

      

      //Store freeze data into qvd

      STORE FinalTable into $(QVDFreeze)$(vFreezeTableName).qvd(qvd);

     

      //Drop the temporary table

      DROP Table FinalTable;

      

      ELSE

      //Store freeze data into qvd

      STORE $(vFreezeTableName) into $(QVDFreeze)$(vFreezeTableName).qvd(qvd);

      ENDIF;

     

    //Drop the table

      DROP Table $(vFreezeTableName);

     

    ENDIF;