Return data before current month

问题:

The following code returns data from 11 tables. Each table contains the year and month. I need to make use of an enddate parameter to check that only tables todate are pulled out. So if the user wants data from 201505 it should pull out all tables only till 201509 as from 201510 tables do not exists assuming we in the month of 201510. Code below:

{

declare 
@enddate varchar(6),
@FirstTableMonth int =201505,
@Table_Name sysname,
@TableMonth int,
@end int,
@CurrentMonth int = 0,
@NextYearMonth int = 1

set @enddate = 201611

WHILE @CurrentMonth < 11
BEGIN

    SELECT  @TableMonth =   CASE WHEN (@FirstTableMonth + @CurrentMonth) % 100 < 13 THEN 
                                @FirstTableMonth + @CurrentMonth 
                            ELSE
                                @FirstTableMonth + 100 - (@FirstTableMonth % 100) + @NextYearMonth
                            END,
                    @NextYearMonth =    CASE WHEN (@FirstTableMonth + @CurrentMonth) % 100 < 13 THEN 
                                    @NextYearMonth
                                ELSE
                                    @NextYearMonth + 1
                                END,
                    @end = case when @enddate 
                    @Table_Name =  'xx_'+CAST(@TableMonth as varchar)+'_T' 

                    SET @CurrentMonth = @CurrentMonth + 1
                    print @Table_Name;
END

}

回答1:

You get all table names from INFORMATION_SCHEMA.TABLES. So just apply your criteria to get them:

select table_name
from information_schema.tables
where table_type = 'BASE TABLE'
and table_name like 'xx\_______\_T' escape '\'  
and table_name >= 'xx_' + CAST(@FirstTableMonth as varchar) + '_T';

SQL fiddle: http://www.sqlfiddle.com/#!6/7b4f5/2

回答2:

I've posted an answer explaining how to retrieve the table names.

But it would really be better to change the design instead. Make it one table and create views for the months (named as the tables are named now). Provide the views with instead-of insert triggers, so you can use select, update, insert and delete just as before, but you'd also have a proper table to work with:

select * from xx_all_t where month >= @FirstTableMonth;