Dynamic Table Selection

[mrliam69]

Before we start this is not my database....

We have a Time and Attendance System that stores all the clockings in seperate tables depending on the year and month i.e.

DT102006 = Oct 2006
DT112006 = Nov 2006

What I am trying to achieve is to extract out of the tables certain information and import it into another database to do with employment law so we can analyis absences....

so

month = 11
year = 2006

select * from DT+month+year


Did that make sense ?

 

[RyanEK]

Gday,

Use exec to execute your SQL.

exec('select * from DT'+@month+@year)

 

[mrliam69]

Cheers for that....

You learn something new every day

Figured the select statement out but now my prequery thing is bugging me..

I need to generate DT092006 but I return DT92006

here's my test code

declare @mnth int, @yr int, @curmnth int, @curyr int

set @curmnth = month(GETDATE())
set @curyr = year(GETDATE())
set @mnth = @curmnth-2
set @yr = 2006

print @mnth 
print @yr
print @curmnth 
print @curyr

I was thinking along the lines of
if its less than 10 then prefix = DT0
else it = DT
but thats where I am struggling with the code...