DYNAMIC UNION TABLES

Question

I am trying to Create a Dynamic Table where I don't Have to update the Table name each and every month. since i am working with the same query (no changes but the table name in the form (dbo.tablename_201705) to (dbo.tablename_201706) and so on. so i created this

DECLARE @TABLE_NAME VARCHAR(20) 
SET @TABLE_NAME ='dbo.GTI_'+left (convert(varchar,dateadd(mm,-3, getdate()-7), 112) ,6)

DECLARE @GTI_SQL_REPORT VARCHAR(255) 
SET @GTI_SQL_REPORT = 'select*from '+@TABLE_NAME.

And from here I am lost I don't know the next step and here below is my query

Select 
   [CountryID], 
   COUNT([Document name]) as [inputcount], 
   MONTH([Creation Date]) AS [Creation Month], 
   Year([Creation Date]) as [Creation Year]
from 
   dbo.GTI_201705 
WHERE  
   [doc_type_product_type] not in ('CUST')
   and [Document_Source] in ('FAX','Original')  
group by 
   [CountryID],
   MONTH([Creation Date]) ,
   Year([Creation Date]) 
order by 
   [CountryID];

so the idea is to create a dynamic table so that this query will run at the beginning of each month. thank you in advance

DYNAMIC UNION TABLES

1 Answers