Results 1 to 14 of 14

Thread: Months string

  1. #1
    Join Date
    Sep 2003
    Posts
    212

    Unanswered: 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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  3. #3
    Join Date
    Sep 2003
    Posts
    212
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  5. #5
    Join Date
    Sep 2003
    Posts
    212
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  7. #7
    Join Date
    Sep 2003
    Posts
    212
    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.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Sep 2003
    Posts
    212
    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.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Sep 2003
    Posts
    212
    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

  12. #12
    Join Date
    Sep 2003
    Posts
    212
    I have setup linked servers, but how would you get the tables counts of all the tables on the linked server?

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Sep 2003
    Posts
    212
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •