May 16, 2011 at 10:13 am
Would like to scedhule a job for the the Tuesday night after the last Friday each month (whether it be current or following month). Can anyone tell me of way to do this ?
May 16, 2011 at 10:16 am
Schedule for every tuesday.
step 1 validate that this is the correct tuesday.
If it's correct update a column somewhere. Then in step 2 1st check that column value is correct and then run the job.
May 16, 2011 at 10:17 am
May 16, 2011 at 10:20 am
mazzz (5/16/2011)
I'd set a separate job to run every Tuesday, use T-SQL to figure out if this Tuesday fulfills the criterion, and then execute the other job or not, accordingly.
So you want to schedule 52 jobs manually every years?? Makes more sens to just do it manually and schedule only 12.
Then again I preffer only 1 which works forever.
May 16, 2011 at 10:24 am
Ninja's_RGR'us (5/16/2011)
mazzz (5/16/2011)
I'd set a separate job to run every Tuesday, use T-SQL to figure out if this Tuesday fulfills the criterion, and then execute the other job or not, accordingly.So you want to schedule 52 jobs manually every years?? Makes more sens to just do it manually and schedule only 12.
Then again I preffer only 1 which works forever.
I'm not sure I understand you. 52?
One job to call the other job, two jobs in total.
Sort of:
IF (various logic checks to figure out if the Tuesday is after the last Friday of the month) = 1
exec msdb.dbo.sp_start_job 'OtherJob'
Edit: I think we're suggesting pretty much the same thing to the OP (we posted at the same time)
May 16, 2011 at 10:28 am
Oh - I see where the confusion comes from.
When I said "a separate job to run every Tuesday" I meant separate from the job that needs to run, not a separate one for each Tuesday!
apologies to the OP for any confusion.
May 16, 2011 at 10:28 am
Ya I just misread your solution. Sorry for the confusion.
May 16, 2011 at 10:30 am
So do you need help figuring out the logic to pick out the "correct" tuesdays?
May 17, 2011 at 7:33 am
Ninja's_RGR'us (5/16/2011)
So do you need help figuring out the logic to pick out the "correct" tuesdays?
Yes I was looking for the Logic
May 17, 2011 at 7:34 am
Do you have a calendar table?
May 17, 2011 at 7:36 am
No but i can create one in excel and import it, if this is required
May 17, 2011 at 7:57 am
Here's the query. I'll post my Calendar definition too but I lost the script that loaded it.
SET DATEFORMAT YMD
DECLARE @test_dt DATETIME
SET @test_dt = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) --Today without time
SET @test_dt = '2011-05-31' -- >> returns run job
SET @test_dt = '2011-05-24' -- >> returns exit
IF 1 = (
SELECT
COUNT(*) --C.dt, C2.*
FROM
dbo.Calendar C
LEFT OUTER JOIN dbo.Calendar C2
ON C.Y = C2.Y
AND C.M = C2.M
AND C.D + 3 = C2.D
WHERE
C.dt = @test_dt-- DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
AND C2.dt IS NULL
)
BEGIN
SELECT
'Run job'
END
ELSE
BEGIN
SELECT
'exit'
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Calendar](
[dt] [smalldatetime] NOT NULL, --date
[isWeekday] [bit] NULL,
[isHoliday] [bit] NULL,
[Y] [smallint] NULL, --year
[FY] [smallint] NULL,
[tinyint] NULL,
[M] [tinyint] NULL, --month
[D] [tinyint] NULL, --day of the month
[DW] [tinyint] NULL,
[monthname] [varchar](9) NULL,
[dayname] [varchar](9) NULL,
[W] [tinyint] NULL,
[UTCOffset] [tinyint] NULL,
[HolidayDescription] [varchar](32) NULL,
[WCalendar] [tinyint] NULL,
[IsBusDay] AS (CONVERT([tinyint],case when [isHoliday]=(0) AND [isWeekday]=(1) then (1) else (0) end,(0))),
[MP] [tinyint] NULL,
[MN] [tinyint] NULL,
[MPY] [smallint] NULL,
[MNY] [smallint] NULL,
[DY] [smallint] NULL,
[monthnameP] [varchar](9) NULL,
[monthnameN] [varchar](9) NULL,
[IsProductionDay] [bit] NOT NULL,
CONSTRAINT [PK__Calendar__7D492A1E] PRIMARY KEY CLUSTERED
(
[dt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
) ON [Data Filegroup 1]
GO
SET ANSI_PADDING OFF
May 17, 2011 at 8:03 am
Just for the sake that you may use this join often in your report(s). You might want to add this index to speed up the self join.
CREATE NONCLUSTERED INDEX [IX_Calendar_Y_M_D]
ON [dbo].[Calendar] ([Y],[M],[D])
May 17, 2011 at 8:13 am
It just came to me that I can actually hand you the data over. It's not like it's proprietary or anything. :hehe:
You'll just have to edit the holiday names and add the holidays for your country.
May 17, 2011 at 8:33 am
You can calculate the Tuesday after the last Friday of the month using the logic below. For the current date, just replace a.DATE from the calendar table with GETDATE(). Also, you only have to schedule the job that starts the other job on the first and last Tuesdays of the month, becasue those are the only days it can be.
select
TuesdayAfterLastFridayOfMonth=
dateadd(dd,((datediff(dd,-53686,dateadd(mm,datediff(mm,-1,a.DATE-10),-1))/7)*7)+4,-53686)
from
-- Date Table Function F_TABLE_DATE available here
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
dbo.F_TABLE_DATE( '20110101','20121231') a
where
dateadd(dd,datediff(dd,0,a.DATE),0) =
dateadd(dd,((datediff(dd,-53686,dateadd(mm,datediff(mm,-1,a.DATE-10),-1))/7)*7)+4,-53686)
order by
a.DATE
Results:
TuesdayAfterLastFridayOfMonth
-----------------------------
2011-01-04 00:00:00.000
2011-02-01 00:00:00.000
2011-03-01 00:00:00.000
2011-03-29 00:00:00.000
2011-05-03 00:00:00.000
2011-05-31 00:00:00.000
2011-06-28 00:00:00.000
2011-08-02 00:00:00.000
2011-08-30 00:00:00.000
2011-10-04 00:00:00.000
2011-11-01 00:00:00.000
2011-11-29 00:00:00.000
2012-01-03 00:00:00.000
2012-01-31 00:00:00.000
2012-02-28 00:00:00.000
2012-04-03 00:00:00.000
2012-05-01 00:00:00.000
2012-05-29 00:00:00.000
2012-07-03 00:00:00.000
2012-07-31 00:00:00.000
2012-09-04 00:00:00.000
2012-10-02 00:00:00.000
2012-10-30 00:00:00.000
2012-12-04 00:00:00.000
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply