August 4, 2023 at 7:16 pm
@Jonathan:
This method of determining Tuesday:
DATEPART(weekday, @CurrentDate) = 3
requires a specific DATEFIRST setting to work correctly. Many business have locations around the world, so it's safer to use a method that will always work correctly, under any and all DATEFIRST settings, such as:
DATEDIFF(DAY, 0, @CurrentDate) % 7 = 1 /*Tuesday*/ The possibility of having to allow for multiple holidays in the future is one reason I used the method I did rather than schedule the job to start on multiple days. What if Mon, Tue and Wed are holidays? Now you have to sched Mon thru Thu, etc.. Also, you have the (slight) overhead of invoking the job many more times than it's really needed if you have it run multiple days.
Thank you for your feedback. I see your point about the DATEFIRST setting and the potential issues it could cause in a global business context. I'll revise my code to incorporate your method of detecting Tuesday:
DATEDIFF(DAY, 0, @CurrentDate) % 7 = 1 /* Tuesday */
As for the overhead of scheduling a job multiple times, I understand your concern. However, in this specific use case, the job in question is relatively lightweight and doesn't consume significant resources. Therefore, I believe the overhead might not be as substantial as it might seem at first glance. Nonetheless, I appreciate your perspective and will keep it in mind for future, more resource-intensive tasks.
August 4, 2023 at 7:36 pm
ScottPletcher wrote:@Jonathan:
This method of determining Tuesday:
DATEPART(weekday, @CurrentDate) = 3
requires a specific DATEFIRST setting to work correctly. Many business have locations around the world, so it's safer to use a method that will always work correctly, under any and all DATEFIRST settings, such as:
DATEDIFF(DAY, 0, @CurrentDate) % 7 = 1 /*Tuesday*/ The possibility of having to allow for multiple holidays in the future is one reason I used the method I did rather than schedule the job to start on multiple days. What if Mon, Tue and Wed are holidays? Now you have to sched Mon thru Thu, etc.. Also, you have the (slight) overhead of invoking the job many more times than it's really needed if you have it run multiple days.
Thank you for your feedback. I see your point about the DATEFIRST setting and the potential issues it could cause in a global business context. I'll revise my code to incorporate your method of detecting Tuesday:
DATEDIFF(DAY, 0, @CurrentDate) % 7 = 1 /* Tuesday */As for the overhead of scheduling a job multiple times, I understand your concern. However, in this specific use case, the job in question is relatively lightweight and doesn't consume significant resources. Therefore, I believe the overhead might not be as substantial as it might seem at first glance. Nonetheless, I appreciate your perspective and will keep it in mind for future, more resource-intensive tasks.
Thank you for the feedback, and fair enough.
My other feeling was that having it run multiple times (2, 3, 4?) at the start of the month could be deceptive / confusing to people looking at the schedule. They would naturally think that the job runs multiple times a month, when in fact it only runs once a month and is designed to run only once a month.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 6, 2023 at 5:55 pm
My other feeling was that having it run multiple times (2, 3, 4?) at the start of the month could be deceptive / confusing to people looking at the schedule. They would naturally think that the job runs multiple times a month, when in fact it only runs once a month and is designed to run only once a month.
Heh... I think that the wrong people might be looking at the schedule jobs if that's true BUT... it is what it is and we have to sometimes accommodate "other people". 😀
With that in mind, you could make a job that runs sometime well before the beginning of the next month that would calculate the next "First Monday if not a holiday, Tuesday if it is" date and modify the job schedule so that it shows up on the schedule just once for the next month.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 6, 2023 at 9:45 pm
ScottPletcher wrote:My other feeling was that having it run multiple times (2, 3, 4?) at the start of the month could be deceptive / confusing to people looking at the schedule. They would naturally think that the job runs multiple times a month, when in fact it only runs once a month and is designed to run only once a month.
Heh... I think that the wrong people might be looking at the schedule jobs if that's true BUT... it is what it is and we have to sometimes accommodate "other people". 😀
With that in mind, you could make a job that runs sometime well before the beginning of the next month that would calculate the next "First Monday if not a holiday, Tuesday if it is" date and modify the job schedule so that it shows up on the schedule just once for the next month.
Really? You see a job scheduled to exec on the first Mon, Tue, Wed, Thu and Fri of a month and you automatically assume it will genuinely run only once per month? You're ahead of me on that one, I would assume it was designed to run multiple times, for whatever reason.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 7, 2023 at 6:27 am
Jeff Moden wrote:ScottPletcher wrote:My other feeling was that having it run multiple times (2, 3, 4?) at the start of the month could be deceptive / confusing to people looking at the schedule. They would naturally think that the job runs multiple times a month, when in fact it only runs once a month and is designed to run only once a month.
Heh... I think that the wrong people might be looking at the schedule jobs if that's true BUT... it is what it is and we have to sometimes accommodate "other people". 😀
With that in mind, you could make a job that runs sometime well before the beginning of the next month that would calculate the next "First Monday if not a holiday, Tuesday if it is" date and modify the job schedule so that it shows up on the schedule just once for the next month.
Really? You see a job scheduled to exec on the first Mon, Tue, Wed, Thu and Fri of a month and you automatically assume it will genuinely run only once per month? You're ahead of me on that one, I would assume it was designed to run multiple times, for whatever reason.
Yes... really. If there's any doubt, I'll go look but there won't be any doubt because I'll know the pattern to begin with. It's my job as a DBA to know. If other people are looking at the schedule, then it's their job to know as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2023 at 8:10 am
Yeah that exchange kind of speaks volumes for itself. Being the senior lead database developer in charge of what is going on within a mess of a database because folks were not documenting nor communicating things and as such we are still fighting that horrible legacy trying to bring it back under more manageable control while still moving it forward as needed.
This is to say those in charge need to always know what is happening, when it is happening, and to some degree why it is happening at all times and they need to document it so that anyone stepping in does as well. Otherwise, well you are just looking for a world of hurt at some point in time down the road.
For this particular situation, I am not exactly sure how I would set it up but what I do know is that whatever the collective decision would be -- it would be fairly well documented so that anyone looking at it would also know exactly what was meant to be going on without having to even check the logic.
As it stands I am kind of torn, K.I.S.S. (Keep It Simple and Smart) says to not make it overly complicated and so far that first suggested method is definitely overly complicated. The second plan, having a Job that runs once a month to set the next months scheduled job sounds simpler but extending that out to a single Job that runs say sometime in December and that sets all the Jobs for the next year using more precise criterion I think would even be simpler. I mean so far I see no reason that could not be done, based on the criterion presented. However, if for some reason allocating a whole year is not viable then perhaps setting the next 6 or 3 months would do it. I mean no sense having a monthly Job do what a Quarterly, Bi-Annually, or Annual Job could do just as well.
August 15, 2023 at 10:40 pm
A question of exactitude: What if the Monday holiday falls on the seventh of the month? Is it Tuesday the 1st or Tuesday the 8th? It will have bearing.
August 16, 2023 at 8:20 am
Well hughfconnor that was one of the things I was driving at in the solution I provided as the business logic for that was not clearly defined and would need to be addressed but have not heard anything back from the initiator of the question so I am assuming they got a version that met their requirements.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply