dynamic table select

[monk73]

I have monthly revenue tables; with a new one created every month.

SELECT * FROM sampledb.revenue_201106;
SELECT * FROM sampledb.revenue_201105;
SELECT * FROM sampledb.revenue_201104;

I'd like to query only the last two month's tables. Is there a "smart" way to select these tables only?

i.e. SELECT * FROM sampledb.revenue_2011[MM];

Thanks,

 

[bborissov]

Yes, just use ONE table where you have additional fields for year and month.
Do not use several tables.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[simian336]

Somthing like.... You will just need to do it twice.

declare @mymonth char(2)
declare @sql as varchar(100)

set @mymonth= cast(month(DATEADD(m, -1, getdate())) as char(2))

if len(@mymonth)=1
set @mymonth='0' + @mymonth

print @mymonth

set @sql ='SELECT * FROM sampledb.revenue_2011' +@mymonth

print @sql

Execute (@sql)

Simi

 

[gmmastros]

I assume all the tables have identical structure. As such, you could create a view that combines the 2 tables and then update the view once a month. Your query for the last 2 months would use the view instead of the actual tables. Something like...

Create View dbo.Revenue_Last2
AS
SELECT * FROM sampledb.revenue_201106
Union All
SELECT * FROM sampledb.revenue_201105

Then, when you want to query the last 2 months

[tt][blue]
Select * From Revenue_Last2[/blue][/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom