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 |
|
nhess80
Yak Posting Veteran
83 Posts |
 Posted - 2011-03-11 : 18:35:40
|
| Hi,I have several sql jobs setup that run every night. They are queries that update tables in my database. In these queries I have a set date range to update the data over. So lets say the date range is set to today and greaterExample: Where Date >= TodayI also have the Where clause to only update where there is a NULL valueThis works great for awhile until the Date get further away from the Today Date that I set. My question is, is there a way to dynamically update the sql job so that I can have the date range change? Is there a way to have the sql job grab the query it uses from a file rather than the static script that I type into it?Hope all of this makes senseThanks,Nick |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2011-03-11 : 20:15:35
|
quote:
Originally posted by tkizer
Yes and yes. Show us your query, sample, data, expected outcome so we can help.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Here is the query. It works just fine. Just need to figure out a way to change the date other than manually. Note that the date is in epoch time, The part in bold is what I am trying figure out. If there was a way that the sql job could grab this query from a file and if the content of the file were to change then the job would change. This query is static and the further the current date gets away from the set date the more the query slows down. Currently this query takes maybe 4 sec or so and it updates 150,000 records give or take. The Date range is roughly 10 days. I would like to keep it in that rangeUPDATE CDRSSET Revenue = CASE WHEN b.C_ANPI_PREM_npanxx = LEFT(RIGHT(LRN, 10), 6) THEN CASE WHEN InterIntra = 'Inter' THEN RoundDuration * b.C_ANPI_PREM_Inter WHEN InterIntra = 'Intra' THEN RoundDuration * b.C_ANPI_PREM_Intra WHEN InterIntra = 'Undfd' THEN RoundDuration * b.C_ANPI_PREM_Intra END ELSE RoundDuration * 0.035 ENDFROM CDRS AS a LEFT OUTER JOIN C_ANPI_PREM AS b ON b.C_ANPI_PREM_npanxx = LEFT(RIGHT(LRN, 10), 6)WHERE (DateTimeInt > 1298937600) AND (InterIntra IS NOT NULL) AND (Revenue IS NULL) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nhess80
Yak Posting Veteran
83 Posts |
|
|
nhess80
Yak Posting Veteran
83 Posts |
|
|
|
|
|
|
|
SQL Server Agent / Jobs