4 Replies Latest reply: Feb 3, 2016 2:40 AM by Hasvine Dhurmea RSS

    Dymanic loop

    Hasvine Dhurmea

      Hello All,

       

      I have table "dbo.ACTION" which is found in my database "HN_PRODUCTION"

       

      However, we have just created a new database "HN_BACKUP" but now the table are as follows:

       

      >HN_BACKUP

           >Tables

                >dbo.ACTION_2015_06

                >dbo.ACTION_2015_07

                >dbo.ACTION_2015_08

                >dbo.ACTION_2015_09

                >dbo.ACTION_2015_10

                >dbo.ACTION_2015_11

                >dbo.ACTION_2015_12

                >dbo.ACTION_2016_01

       

      The issue is that I have to load only the last three months.

       

      To resume I have to load the below tables:

      >dbo.ACTION which is HN_PRODUCTION

      >dbo.ACTION_2015_11

      >dbo.ACTION_2015_12

      >dbo.ACTION_2016_01

       

       

      I managed to load the table using the script below:

       

      Action:

      LOAD ActionLocalTime,

          Date(Floor(ActionLocalTime)) AS Date,

          AgentId as FirstAgent,

          Campaign as CampaignId,

          Duration as DurationPre,

          State

      SQL SELECT *

      FROM "HN_PRODUCTION".dbo.ACTION where NextState='96';

       

      FOR Each a in '2015_12','2015_11','2015_10','2016_01'

       

      Action:

      LOAD ActionLocalTime,

          Date(Floor(ActionLocalTime)) AS Date,

          AgentId as FirstAgent,

          Campaign as CampaignId,

          Duration as DurationPre,

          State

      SQL SELECT *

      FROM "HN_BACKUP".dbo.ACTION_$(b) where NextState='96';

       

      HOWEVER, for next month a new table will be created in "HN_BACKUP" as "dbo.ACTION_2016_02" and I will need to load the data of the tables below:

       

      >dbo.ACTION which is HN_PRODUCTION

      >dbo.ACTION_2015_12

      >dbo.ACTION_2016_01

      >dbo.ACTION_2016_02

       

      I have around 35 qvw using these table.

       

      Any idea on how to I can do it.

       

      Kind Regards,

       

      Hasvine