August 3, 2023 at 4:41 pm
Hello,
I need to setup a Stored Procedure to run on the first Monday of each month that is not a holiday. If the first Monday of the month is a holiday, than I need the PROC to run on Tuesday. Via Scheduled Jobs, how is this possible? Can someone help with a query on hold to do this? Below is my holiday schedule sample:
-- DROP TABLE #h
CREATE TABLE #h (HolidayDate date, HolidayName varchar(100))
INSERT INTO #h (HolidayDate, HolidayName) VALUES ('2023-01-02', 'New Year Day')
INSERT INTO #h (HolidayDate, HolidayName) VALUES ('2023-01-02', 'Patriots Day')
INSERT INTO #h (HolidayDate, HolidayName) VALUES ('2023-01-02', 'Memorial Day')
INSERT INTO #h (HolidayDate, HolidayName) VALUES ('2023-01-02', 'Juneteenth')
INSERT INTO #h (HolidayDate, HolidayName) VALUES ('2023-01-02', 'July 4th')
INSERT INTO #h (HolidayDate, HolidayName) VALUES ('2023-01-02', 'Victory Day')
INSERT INTO #h (HolidayDate, HolidayName) VALUES ('2023-01-02', 'Labor Day')
INSERT INTO #h (HolidayDate, HolidayName) VALUES ('2023-01-02', 'Columbus Day')
INSERT INTO #h (HolidayDate, HolidayName) VALUES ('2023-01-02', 'Veterans Day')
INSERT INTO #h (HolidayDate, HolidayName) VALUES ('2023-01-02', 'Thanksgiving')
INSERT INTO #h (HolidayDate, HolidayName) VALUES ('2023-01-02', 'Day After Thanksgiving')
INSERT INTO #h (HolidayDate, HolidayName) VALUES ('2023-01-02', 'Christmas Eve')
INSERT INTO #h (HolidayDate, HolidayName) VALUES ('2023-01-02', 'Christmas Day')
SELECT * FROM #h
Many thanks in advance!
August 3, 2023 at 5:39 pm
I don't believe you can do this directly. However, you could have the first step of the job determine if it is a holiday, and if so, to schedule a run for the next day and immediately exit; if it's not a holiday, exec the proc normally.
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 3, 2023 at 7:27 pm
Yes we have already done this -- but you will also need a job that updates your Holiday table for each new year. We store the previous year, the current year, and the next year -- and the job runs some arbitrary day in January of each year updating the the table by dropping the previous year and adding the next year. Note: we just store the dates when doing previous, current, and next year so it just ends up being a continuous flow of dates that get referenced.
Of course, you could even have that Holiday table also hold the first Monday of each month as well, rather than have it configured each time when you do the query. Then it would become an extremely simple look up. Which might be the most efficient approach.
Now as for getting the first Monday of each month (to perhaps auto populate the above table) you can create a procedure that calculates that information using the following:
-- Get First Day of the Month
Declare @FirstMonday DATE = CONCAT( @ChosenYear, "-", @ChosenMonth, "-01');
SET DATEFIRST 7;
WHILE DATEPART(WEEKDAY, @FirstMonday) <> 1
BEGIN
SET @FirstMonday = DATEADD(DAY, 1, @FirstMonday)
END
INSERT INTO [dbo].[tbCalendarData]
( DateType -- 1 = 1st Monday 2 = Holiday
,TheDate
,Description
)
VALUES ( 1
,@FirstMonday
,CONCAT( 'First Monday of ', FORMAT( @FirstMonday, 'MMMM' ) )
);
The above then could be placed within another WHILE loop to fill in every 1st Monday of each month into your Calendar table. Of course if you need the 1st Monday that is not a Holiday or the 1st Non-Holiday Day after the 1st Monday if the 1st Monday is a Holiday then there would be a bit more logic to apply but with this you should have a good basis to build from for whatever you need something like this for.
August 3, 2023 at 8:45 pm
I'd strongly urge you not to mess with the DATEFIRST setting. You don't need to, and it could throw off other code which might need a different setting (if the default was different than 7).
(1) Schedule a job to run on the first Monday of every month (you can do this directly in SQL Server job scheduler).
(2) Add code for the first job step like this:
DECLARE @new_run_date date
DECLARE @schedule_id int
IF EXISTS(SELECT 1 FROM dbo.your_holiday_table WHERE HolidayDate = CAST(GETDATE() AS date))
BEGIN
PRINT 'Today (Monday) is a holiday, attempting to reschedule job.'
SET @new_run_date = DATEADD(DAY, 1, GETDATE())
EXEC msdb.dbo.sp_add_jobschedule @job_name = 'your job name here',
@freq_type = 1, /*run one time only*/
@active_start_date = @new_run_date,
@active_start_time = 'hh:mm:ss', /*the time you want the job to run on Tuesday*/
@schedule_id = @schedule_id OUTPUT
IF @schedule_id IS NOT NULL
PRINT ' Job has been successfully rescheduled for tomorrow.'
ELSE
PRINT ' !!Failure!! Job could not be rescheduled, REVIEW MANULLY!!'
/*naturally in the real world you would likely use an email or some other direct msg method*/END /*IF*/ELSE
BEGIN
EXEC dbo.proc_you_want_to_exec --...param(s)...
END /*ELSE*/
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 3, 2023 at 9:46 pm
You could do it by having this code in the first step of your job and set the schedule to run on the first Monday and Tuesday of every month.
DECLARE @CurrentDate AS date = GETDATE()
-- Exit if it is a Tuesday and the previous day was not a holiday or if it is a holiday.
IF EXISTS(SELECT *
FROM dbo.your_holiday_table
WHERE (HolidayDate <> DATEADD(dd, -1, @CurrentDate) -- Previous day was not a holiday
AND DATEDIFF(DAY, 0, @CurrentDate) % 7 = 1 -- It is a Tuesday
OR @CurrentDate = HolidayDate) -- @CurrentDate is a holiday
THROW 51000, 'Not a run day', 1; -- Cause the job to gracefully stop
In the Advanced page of the step you should set the On failure action to Quit the job reporting success.
This method will also allow you to run the job manually on any day just by starting the job from Step 2
August 3, 2023 at 9:51 pm
@scottpletcher rather than say not to use SET DATEFIRST, perhaps explaining why you feel using it is dangerous, your vague statement does not really counter the documentation stating that one should use it. Granted MS's documentation is actually less reliable than their code but that is where I got that information from (aka I just googled for that information and extrapolated the answer). I mean none of us, where I work, had heard of SET DATEFIRST prior to this, nor did we use it in our solution (as no one had heard of it until now -- I always learn when I teach). So if you have some insights in this that would be a very nice to know item. I mean I know I would really like to here the why of it as I never take surface statements without some facts to back it up. Facts that I can later do a fact-check on.
That being said, while your Job does handle running something on the First Day of the Month that is not a Holiday -- it does not cover the full criterion nor does it help the requestor to learn how to figure this out. However, yes ultimately this individual will want to use a Job to execute their Stored Procedure using the criterion they end up creating to solve the solution. Still, keep in mind that their full criterion was/is:
Criterion: A Job that runs on the 1st Monday of a Month (not the first day) unless that Monday is a Holiday then run it on the following Tuesday.
However, to extend that criterion out to its more logical conclusion it should read:
Criterion: A Job that runs on the 1st Monday of a Month (not the first day) unless that Monday is a Holiday then run it on the next subsequent weekday (aka business-day) that is not a Holiday.
This way if the Monday lands on the first day of a multi-day holiday then this job will run on the first business-day following that Monday. Now I agree most Official Holidays are only 1 day long, however, some company's Official Holidays are more than 1 day long meaning one should look for the full solution (cover most of the what ifs) in order to future proof one's code.
Thus in the end, this user does need to do a bit more than create a Job as you suggest and helping them build a full quality solution from the ground up using a bit more than the actual situation requires will help them extrapolate later on how to create something similar but that has different criterion, thus trying to teach them to fish rather than just give them a fish.
August 4, 2023 at 3:23 pm
@dennis Jensen:
I never presume to know the requirements of the task better than the person requesting the code.
"If the first Monday of the month is a holiday, than I need the PROC to run on Tuesday."
Maybe they always want to run it on Tues, even if that happens to be a holiday. If they have a further requirement, i.e. the possibility of a Tuesday holiday following a Monday holiday and they want to move the date again, they can state it.
Changing DATEFIRST affects the results of many functions, including DATEPART. That alone could cause errors in other code.
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 4, 2023 at 3:30 pm
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.
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 4, 2023 at 3:30 pm
<removed, dup comment>
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 4, 2023 at 3:35 pm
I like to keep things as simple as possible. I also want to use the tools that exist.
I would set the schedule like this for the proc in Agent and as Scott noted, reschedule this to the next non-holiday. While Scott only moves this one day, you could easily consult the holiday table and find the next non-holiday table.
FWIW, I wouldn't have a table of holidays. It's a small table, so I'd have all dates and mark some as holidays, as a dimension table. That way if someone needs to mark other days for something, you have an easy way to do this.
August 4, 2023 at 3:37 pm
Personally I prefer a nonworkdays-only table (I don't call it a "holiday" table because sometimes it's a nonwork day but not a holiday (such as for a power outage, etc.)).
That keeps the table very small (one page), even for multiple years of nonworkdays.
If necessary, I would keep a separate table of work days. It'd be very easy to union/merge the two tables.
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 4, 2023 at 3:42 pm
I get that. However, I often find the next person that writes code struggles to easily join here. Joining with all days is really simple.
August 4, 2023 at 3:43 pm
Yikes, if a developer can't join/merge two tables with distinct date values to a combined table, they need additional training, and pronto.
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 4, 2023 at 3:45 pm
As Morpheus said, "welcome to the real world."
August 4, 2023 at 3:46 pm
Fair enough.
In that case, let's create a view that has the merged tables for anyone to use. That is much cleaner.
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".
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply