Post reply

Extract 3 months worth of data

  • February 8, 2011 at 2:45 pm

    #143247

    Greetings again dearest helpers.

    I need some huge favor.

    I am asked to extract 3 months worth of data from sql server into a pipe-delimited .txt file with text qualifiers (quotes around the text fields) and with column headers.

    I could do this using dts.

    My big issue is that they want 3 months worth and how to construct that is problematic.

    Can I please, please ask for your assistance?

    Any example with any 2 or 3 column headers will be fantastic.

    I can add additional headers later. I am just interested in how the 3 months code will be constructed.

    As always, many thanks for all your help.

  • February 8, 2011 at 4:48 pm

    #1284131

    Look up the DATEADD function in Books On Line (BOL).. Now this is a vastly over simplified example"

    SELECT DATEADD(mm,-3,GETDATE()) AS '3 months ago', GETDATE() AS 'Current date'

    Returns:

    3 months ago Current date

    2010-11-08 18:45:11.8102011-02-08 18:45:11.810

    If you post your table definition(s), some sample data and required results following the format of the article for doing so, which you can access by clicking on the first link in my signature block, it is more than likely that someone will provide you with a tested SQL procedure to do what you require.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 8, 2011 at 9:37 pm

    #1284188

    Thanks a lot Ron.

    This is the sample data as requested by users.

    doc_id|"docket_entry_id"|"document_nm"|"document_descr"|"document_type_id"|"document_perm_rem_flg"|"document_chkd_in_flg"

    10|10|"Final Order"|"Orders: Final Order"|1|"N"|"N"

    11|11|"Final Order"|"Orders: Final Order"|1|"N"|"N"

    12|12|"Final Order"|"Orders: Final Order"|1|"N"|"N"

    13|17|"Final Order"|"Orders: Final Order"|1|"N"|"N"

    14|19|"Final Order"|"Orders: Final Order 19"|1|"N"|"N"

    15|20|"Final Order"|"Orders: Final Order 20"|1|"N"|"N"

    16|21|"Final Order"|"Orders: Final Order 21"|1|"N"|"N"

    17|22|"Final Order"|"Orders: Final Order"|1|"N"|"N"

    42|47|"Summons"|"Process/Service: Summons to James Manson"|1|"N"|"N"

    43|48|"Final Order"|"Orders: Final Order"|1|"N"|"N"

  • February 9, 2011 at 6:22 am

    #1284391

    I am asked to extract 3 months worth of data from sql server into a pipe-delimited .txt file with text qualifiers (quotes around the text fields) and with column headers.

    Pardon me for being somewhat confused does the following pertain to the quote directly above?

    I could do this using dts.

    My big issue is that they want 3 months worth and how to construct that is problematic.

    Is the above quote refiring to the composing of the T-SQL statement to select the required data, and no more than that required the problem?

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 9, 2011 at 6:41 am

    #1284408

    Hi Ron,

    I am confused myself about your question.

    I am not sure I understand the question.

    I said I could dump the data that they asked for by using dts.

    However, they want *not* all the data but only 3 months worth of data and I cannot do this using dts; at least I don't know if that's possible.

    Is that what you are referring to in your questions?

    Or if you are referring to the word quotes, what I was saying is that they would want us to dump the data into a pipe (|) delimited txt file and with quotes (") around the fields and their values.

    I don't know if I answered your question.

    Thanks alot for your interest to help.

  • February 9, 2011 at 7:34 am

    #1284458

    simflex-897410 (2/8/2011)

    Greetings again dearest helpers.

    I need some huge favor.

    I am asked to extract 3 months worth of data from sql server into a pipe-delimited .txt file with text qualifiers (quotes around the text fields) and with column headers.

    I could do this using dts.

    My big issue is that they want 3 months worth and how to construct that is problematic.

    Can I please, please ask for your assistance?

    Any example with any 2 or 3 column headers will be fantastic.

    I can add additional headers later. I am just interested in how the 3 months code will be constructed.

    As always, many thanks for all your help.

    Take a look at the first link in my signature line below. Yes, it tells how to post data but within the code in that article, it also tells how to do what you ask.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 9, 2011 at 12:40 pm

    #1284779

    simflex-897410

    1. To do what you need, you must be able to edit the DTS package. This editing of the existing package can not be performed in SQL 2005 without some special downloads. These downloads are readily available from Microsoft.

    To do this read the SQL SERVER 2005 help file, known as Books On Line or (BOL). Here is a quote from a pertinent page, in regards to what you need to do.

    Installing Design-time Support for SQL Server 2000 DTS Packages

    SQL Server 2005 does not install the DTS package designer for editing DTS packages. However, you can download and install an updated version of the DTS package designer, and use this updated version to modify DTS packages. To download the updated DTS package designer, visit the Microsoft Web page, Feature Pack for Microsoft SQL Server 2005, and then download the Microsoft SQL Server 2000 DTS Designer Components. After installing this download, you can view and edit DTS packages from Management Studio, or from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio.

    The link for the download is:

    http://www.microsoft.com/downloads/en/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

    Once you have completed the installation of the feature pack, and if you have any additional problems, come back and post those problems to a new forum, and I am sure someone will be able to assist you.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 10, 2011 at 7:06 am

    #1285153

    Hello,

    I dont know much about DTS but i assume you will be writing the query to select the data....however i have noticed a problem from the table exert you posted....there is no date column or identifier....without this you cannot select the last 3 months worth of data unless the document ID or some other field can be used.

    How would you decide what is older than 3 months and what is newer?? Then you can construct a query.

    Regards,

    Samuel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply