Repeating code over time period

  • Hi Guys,

    I have a code snippet to run every month and have to take a union of current data and previous 11 months data and put in excel to prepare a report. I have to create a table with current month suffix and there are changes to be made to date variables.

    Any suggestion how I can avoid making changes to the code and generate reports based on the current month,(it should automatically create a table based on the current month and take a union with the previous 12 months data only)?

    Your help is appreciated!

    Regards,

    Prasanna

  • Use SSIS to build a package. Base a query on dateadd to get the previous 11 months in your query.

  • I agree with steve,

    SSIS would be your best bet if you have the knowledge to use it.

    Alternative if you don't have SSIS knowledge you can use a SP on the database level and SSRS as frontend witch can also be sheduled to run on certain timestamps and provide the data as needed.

    Wkr,

    Eddy

  • A partitioned view might be what you're looking for. Take a look at these links:

    Using Partitioned Views

    Modifying Data in Partitioned Views

     

  • Have you considered just exectuing the query in Excel to begin with? You can create formulas in cells adjacent to the data set for formatting or other conditioning. I do this for several reports to get the 'Week Of' as the Monday date of that week and one for 'Day of Week' as well. These formula based values work very nicely in pivot tables and charts. Simply refresh the table and pivot data. from within the Excel report.

Viewing 5 posts - 1 through 4 (of 4 total)

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