constructing table name in FROM stmt using sysdate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

constructing table name in FROM stmt using sysdate

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2011-05-25 : 16:35:14

I need to join two tables using SQL 2000 or 2008. Current month and a prior month. The table name for the prior month will always be the system date minus 2 months. For example, if the system date is May 25, 2011 then my prior month table name would need to be Monthly_Data_2011_03.dbo.deposits. Currently, I have to hard code this in the program and change it monthly. Is there away to automate this? I have listed my code below. My intentions are to place this eventually into a stored procedure Thank you for your time in advance.

select c.account,c.matdate as 'c.matdate', p.matdate as 'p.matdate',c.textdate,
DATEDIFF(dd,c.matdate,p.matdate)as 'date diff ' from Monthly_Data.dbo.deposits as c

Left Join Monthly_Data_2011_03.dbo.deposits as p
on c.account = p.account

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 19:51:41

I can't think of a way to do this other than to use dynamic SQL. If you do want to use dynamic SQL, it would be something like this:


declare @stmt nvarchar(255);

set @stmt = 
'
select c.account,c.matdate as ''c.matdate'', p.matdate as ''p.matdate'',c.textdate,
DATEDIFF(dd,c.matdate,p.matdate)as ''date diff '' from Monthly_Data.dbo.deposits as c

Left Join Monthly_Data_'
+ cast (year(dateadd(mm,datediff(mm,0,getdate())-2,0)) as nvarchar(31))
+ '_'
+ cast (month(dateadd(mm,datediff(mm,0,getdate())-2,0)) as nvarchar(31))
+ '.dbo.deposits as p
on c.account = p.account';

exec (@stmt);

I am not very familiar with SQL 2000, but you may even be able to use sp_executesql, which may have some advantages.

cirugio
Yak Posting Veteran

90 Posts

Posted - 2011-05-26 : 11:01:31

Thank you Suntibeck. This works beautifully, the only issue I have is the month calculation only returns a one character value for the months January thru Sept (i.e. 1 for Jan, 2 for feb, 3 for March, etc..). How can I force it to return two characters for the month instead of one (i.e. 01 for Jan, 02 for Feb, 03 for March, etc...) in the + cast (month(dateadd(mm,datediff(mm,0,getdate())-2,0)) as nvarchar(31)) statement? Thanks again for your time.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-26 : 12:22:47

Change

+ cast (month(dateadd(mm,datediff(mm,0,getdate())-2,0)) as nvarchar(31))

+ RIGHT('0'+cast (month(dateadd(mm,datediff(mm,0,getdate())-2,0)) as nvarchar(31)),2)

EDIT: I had it incorrectly as using the LEFT function.

cirugio
Yak Posting Veteran

90 Posts

Posted - 2011-05-27 : 11:18:39

Thank you this worked out beautifully.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-05-27 : 16:52:54

If you have the option of only 2005 and above (not available in 2000) - I would recommend using SYNONYMS. It appears that you have some process that creates a new monthly table.

In that process, I would add code to drop and recreate the synonyms to reference the correct tables. This will have to be dynamic code - but then your actual code would just reference the synonyms.

For example, you could have 3 synonyms:

CREATE SYNONYM dbo.Deposits_Current FOR Monthly_Data.dbo.Deposits_2011_05;
CREATE SYNONYM dbo.Deposits_Previous FOR Monthly_Data.dbo.Deposits_2011_04;
CREATE SYNONYM dbo.Deposits_Prior FOR Monthly_Data.dbo.Deposits_2011_03;

Once you have those built, then in your actual stored procedure/view/script you would just use them:

SELECT ...
FROM dbo.Deposits_Current c
JOIN dbo.Deposits_Previous c1 ON ...
WHERE ...

Now, another way to get the YYYY_MM with a bit less code is:

Select 'Monthly_Data.dbo.Deposits_' + stuff(convert(char(6), dateadd(month, -2, getdate()), 112), 5, 0, '_')

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources