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.
| Author |
Topic |
|
whatcom
Starting Member
2 Posts |
 Posted - 2009-12-16 : 14:02:20
|
| Hello. I am trying to create a scheduled DTS package that will export some of my data into a text file. The data I want to export includes specific building details (such as permit-number, issue-date, etc). Doing this as a one-time job, or doing it manually is no problem at all. The hiccup is that I need to create an automated weekly DTS job. This weekly DTS job will need to only include the records where the issued date is within the timeframe since the last weekly DTS job was ran. If I was doing this manually, and it was Janurary 7th, the query would be something like:SELECT *FROM tablexWHERE issue-date between '01-01-2010' and '01-07-2010'Then, on January 14th, I would run another DTS job to look something like:SELECT *FROM tablexWHERE issue-date between '01-08-2010' and '01-14-2010'Based on the above query, if I want to automate this process, it appears that I need to somehow populate the date fields, but in a way where the DTS job can run weekly without human interaction.The only idea I have is to create some time of datestamp, which occurs when a job is ran. Then, when the next job is ready to run, somehow reference the datestamp (as the first date in the WHERE clause), and then add 6 days (for the second date in the WHERE clause).I'm not even sure if this would work, or the best way to do this. Does anyone have any opinions or ideas or the best way to solve this? Thanks. |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-12-16 : 14:31:24
|
| [code]SELECT *FROM tablexWHERE issue-date between dateadd(d, datediff(d, 0, dateadd(dd,-7,getdate())), 0) and dateadd(d, datediff(d, 0, getdate()), 0)[/code]Will give you records issued last week. You can schedule this to run weekly..It will pick the current date when you are running the report as the dates are not hardcoded. |
 |
|
|
whatcom
Starting Member
2 Posts |
Posted - 2009-12-16 : 16:12:51
|
| Wow - that worked like a charm. Thanks so much. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-12-16 : 16:33:29
|
| You're welcome |
|
|
|
|
|
|
|
|
Date variable for a DTS job