April 11, 2009 at 10:03 am
Hi Experts,
I want to run the job on first and third business day of the month ,means it needs to skip sat/sun , please advice how i can achieve that?
Thanks
April 11, 2009 at 2:50 pm
The first businnes day of the month is very relative. Exmple...i´m from brazil and we can a day off (holiday sample) and in your contry no.
So I THINK its not possible because sql server will have a internal table with all businnes day of all countrys..and this for year.
What you can do..maybe create a table with yours businness days(just the first and third..and you can insert the data only on time for year). It will have 2 lines for mounth( first and third) and when your job finished, you search for the next businnes day.
Also You can do a table with local holidays and calculate the first and third business day..you can use this table for other things...What is better for you.
So..you can use sp_update_schedule to update your job schedule to this date.
You create a new step in job, and if it finished with successfully the new step can do this...all automated.
Look at sp_update_schedule in BOL.
April 13, 2009 at 7:04 am
Laerte has the right idea. Here's how I would do it.
1. Create a calendar table that has flags for holidays and weekends
2. Create a job that runs daily that checks to see if today is the 1st or 3rd business day. If it is then execute sp_start_job which will start the non-scheduled job that I need to have run on the 1st and 3rd business day.
Something like this in the job step:
IF EXISTS(;WITH cteDates
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY the_date) AS business_day_of_month,
the_date
FROM
calendar
WHERE
is_weekend = 0 AND
is_holiday = 0 AND
the_date BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),
0)
AND DATEADD(MONTH,
DATEDIFF(MONTH, 0, GETDATE()) + 1, -1)
)
SELECT
*
FROM
cteDates
WHERE
the_date = DATEADD(Day, DATEDiff(Day, 0, GETDATE()), 0) AND
business_day_of_month IN (1, 3))
BEGIN
EXECUTE msdb.dbo.sp_start_job @job_name = 'job_name'
END
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 13, 2009 at 7:15 am
April 13, 2009 at 7:39 am
ROW_NUMBER = Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition
What is this in english i can't understand the ROW_NUMBER()
April 13, 2009 at 7:42 am
April 13, 2009 at 8:03 am
Tracey,
ROW_NUMBER is one of the new statistical/windowing functions added in SQL Server 2005. Basically it created a sequential number based on the OVER clause. In the current situation I wanted to identify the 1st and 3rd business days of a month. If you run this:
DECLARE @calendar TABLE(the_date SMALLDATETIME, is_weekend BIT, is_holiday BIT)
-- create a numbers table, just 100 for testing
;WITH cteNumbers
AS
(
SELECT TOP 100
ROW_NUMBER() OVER(ORDER BY NAME) AS n
FROM
sys.all_columns
)
-- create data for the calendar table
INSERT INTO @calendar (
the_date,
is_weekend,
is_holiday
)
SELECT
DATEADD(DAY, N.n-1, '4/1/09') AS the_date,
/*
this sets Weekends. You actually should use the
@@DateFirst function because the weekends actually would
be different days of the week based on that setting
*/
CASE
WHEN DATEPART(dw, DATEADD(DAY, N.N-1, '4/1/09') ) IN (1,7) THEN 1
ELSE 0
END AS is_weekend,
/*
For where I work the 3 dates are all holidays
Good Friday, Memorial Day, and Independence Day
*/
CASE
WHEN DATEADD(DAY, N.N-1, '4/1/09') IN ('4/10/09', '5/25/09', '7/4/09') THEN 1
ELSE 0
END AS is_holiday
FROM
cteNumbers AS N
WHERE
n <= 100
;WITH cteDates
AS
(
SELECT
/*
This days number the rows returned ordered by the_date. Since I am only
returning 1 month I do not need to use the Partition BY. If I was
returning more than 1 month I would use:
ROW_NUMBER() OVER (PARTITION BY DatePart(month, the_date) ORDER BY the_date)
Which would cause the ROW_NUMBER() value to restart when the month changes.
*/
ROW_NUMBER() OVER (ORDER BY the_date) AS business_day_of_month,
the_date
FROM
@calendar
WHERE
is_weekend = 0 AND
is_holiday = 0 AND
the_date BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),
0)
AND DATEADD(MONTH,
DATEDIFF(MONTH, 0, GETDATE()) + 1, -1)
)
SELECT * FROM cteDates
It return this (Partial results):
business_day_of_month the_date
--------------------- -----------------------
1 2009-04-01 00:00:00
2 2009-04-02 00:00:00
3 2009-04-03 00:00:00
4 2009-04-06 00:00:00
5 2009-04-07 00:00:00
6 2009-04-08 00:00:00
7 2009-04-09 00:00:00
8 2009-04-13 00:00:00
9 2009-04-14 00:00:00
10 2009-04-15 00:00:00
11 2009-04-16 00:00:00
12 2009-04-17 00:00:00
13 2009-04-20 00:00:00
14 2009-04-21 00:00:00
15 2009-04-22 00:00:00
Notice how the weekends and holidays are skipped so the ROW_NUMBER() does not just return the day of the month.
Oh and here is a blog post that explains how to use @@DateFirst when looking for a specific day of the week.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 13, 2009 at 8:28 am
Thanks for notes ....
April 14, 2009 at 5:38 pm
Thanks Jack and all , appreciate your help..
October 13, 2014 at 8:00 am
Thanks for post.
I entered into similar issue when I was asked to deliver a report on the first working day of the month.
I tried the sql posted but was having syntax error as my if exists doesn't like CTE at all. So I modified the SQL a shown below and it worked perfect for me. The sql first checked if today is the first working day of the month and if yes, it will then will trigger the subscription. The job was meant to run daily.
IF EXISTS (
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY dt) AS business_day_of_month,
dt
FROM
dbo.calendar
WHERE
isweekday = 1 AND
isholiday = 0
AND dt BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND DATEADD(MONTH,DATEDIFF(MONTH, 0, GETDATE()) + 1, -1)
) a where 1=1 and business_day_of_month = 1 and dt = dateadd (dd, 0, datediff(dd, 0,getdate()))
)
BEGIN
EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData = '888a3842-66d6-4bcb-be06-67929edbf43a'
END
October 14, 2014 at 2:32 pm
Here is a simple way, create a step called Check Schedule
DECLARE @FirstDayofMonth date
SET @FirstDayofMonth = DATEADD(month, DATEDIFF(month, 0, getdate()), 0)
--print @FirstDayofMonth
IF @FirstDayofMonth <> CAST(GETDATE() AS DATE)
BEGIN
RAISERROR ('Not scheduled today', 16, 127)
END
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply