Months string

can sum one help with this:
I want 3 strings what represent the last three months from today's date inclusive.
for eg: if today is november 19th, 2003.
How would i get 3 strings as '2003_11', '2003_10', '2003_09'
Thanks a million.declare @.DateValue datetime
set @.DateValue = getdate()

select replace(convert(varchar(7), @.DateValue, 120), '-', '_')
select replace(convert(varchar(7), dateadd(m, -1, @.DateValue), 120), '-', '_')
select replace(convert(varchar(7), dateadd(m, -2, @.DateValue), 120), '-', '_')

You can ignore the REPLACE functions if you are picky about the format returned, but SQL Server does not have a datetime format using underscore characters.

blindman|||Thanks for the reply appreciate it.

Well this is what i'm doing with these strings:

I have monthly tables named as 'Tablename_yyyy_mm' etc.
I want to make a view that will capture the current months table and the last 3 months data.
for eg: if today is november 19th, 2003.
The view should capture 'Tablename_2003_11', 'Tablename_2003_10', 'Tablename_2003_09' tables
if today is jan 01,2003
The view should capture 'Tablename_2003_01', 'Tablename_2002_12', 'Tablename_2002_11' tables

Thanks a lot.|||9 times out of 10 this is a bad database design. Unless you are dealing with terabytes of data, it is better to create a single table with 1 extra column indicating the appropriate month. Easier to code, and generally more efficient.

blindman|||Originally posted by blindman
9 times out of 10 this is a bad database design. Unless you are dealing with terabytes of data, it is better to create a single table with 1 extra column indicating the appropriate month. Easier to code, and generally more efficient.

blindman

Well the original table was getting unmanagable, ie, it had about 160 million records and was growing fast. That why archiving the table into monthly tables was adopted. Now the original table will be split into monthyl tables and views are created for say each quarter, annual etc.|||Had you fully exhausted all the other possibilities for performance improvement?

Drives.
Processors.
Memory.
Indexing.
Normalization.
Pre-aggregation.
Query optimization.

Magic 8 Ball says outlook not good.

blindman|||Yes, indexing would take alot of space itself (almost the same as the table). The table is being used for reporting purposes so most of the time previous years data is not even touched, but still it's there in one big table. The whole process became slow due to the size of the table. Memory is not an issue. The current year's data is being used most often so monthly tables would give us more flexibility in viewing data.|||Did you try a 12 month partitioned view before this dynamic creation thing?

Or how about 13...make luck 13 an archive of infrequently used data, and set up an archive method...|||yes the 12 month archiving was thought of too.
for reporting etc. last 6 months data could be required. that that would make the 12 or 13 month archival method bogus. the monthly tables would be most flexible.

Another question: Is there a way to get the records counts for all the tables on (server a) and save those counts on a table in server B. And we r running this script or proc from server b.

server a and server b or not on a trusted connection but a password and userid are given.|||The 13th "month" would all previous yeard data...then you create a partitioned view to see all of the data

As for counts

Did you set up a linked server?|||Well the original table has data from jan 2001 till present.

for counts

i'm using openrowset(...) to get all the table names on server a. Would linked server be better? if so how would u setup linked servers? ne sample code would be helpful

thx|||I have setup linked servers, but how would you get the tables counts of all the tables on the linked server?|||Do you mean like SELECT COUNT(*) FROM myTable99?

Theres alos sp_spaceused, and a system table contains the info...

All of which (except SELECT COUNT(*)) need to statistics run to make sure the current..|||I cant do the select count(*) becuase in the sysservers tabe the linked i added has a null value in the srvnetname column. But all the sp_columns_ex and sp_tables_ex command work.