Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Schedule a SQL Job only for the first ten working days of each month Expand / Collapse
Author
Message
Posted Tuesday, November 19, 2013 2:47 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:49 AM
Points: 5, Visits: 26
I want to make a schedule in SQL only for the first ten working days of each month.
What's the best way to do this ?

Post #1515463
Posted Tuesday, November 19, 2013 3:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 12,245, Visits: 9,218
Unfortunately you cannot specify many rules in a SQL Server Agent schedule.
It seems you have to create 10 different schedules, each for one single day.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1515475
Posted Tuesday, November 19, 2013 3:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 2,732, Visits: 2,650
What about when one of those day is a public/national holiday?
Post #1515478
Posted Tuesday, November 19, 2013 3:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 12,245, Visits: 9,218
IgorMi (11/19/2013)
What about when one of those day is a public/national holiday?


Good point.
It might be easier to create a stored procedure that checks if the job should run or not.
If it can run, start the job.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1515479
Posted Tuesday, November 19, 2013 4:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 2,732, Visits: 2,650
Koen Verbeeck (11/19/2013)
IgorMi (11/19/2013)
What about when one of those day is a public/national holiday?


Good point.
It might be easier to create a stored procedure that checks if the job should run or not.
If it can run, start the job.


Right, using logic in SP is the solution in this case. Apart that, a separate table holding the holiday days should be maintained on yearly basis as well. Usually some holidays have different date every year, and because of that it should be updated every year before 01/01.
Post #1515487
Posted Tuesday, November 19, 2013 7:57 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:49 AM
Points: 5, Visits: 26
It's indead an idea to generate a SP that does the calculation of these working days.
Does anyone have an example on how to do this or even better a example script ?
Post #1515568
Posted Tuesday, November 19, 2013 8:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 12,755, Visits: 31,122
d.velders (11/19/2013)
It's indead an idea to generate a SP that does the calculation of these working days.
Does anyone have an example on how to do this or even better a example script ?


assuming you have a Calendar table, here's one suggestion:
IF  DAY(GETDATE()) <=10 
AND EXISTS(SELECT 1 FROM TallyCalendar WHERE IsHoliday = 0 AND isWorkDay = 1 And TheDate = CONVERT(date,getdate()) )
BEGIN
PRINT 'first ten days of month, continue processing'
END
ELSE
BEGIN
print 'second third month, alternate processing ?'
END



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1515602
Posted Tuesday, November 19, 2013 8:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 2,732, Visits: 2,650
Lowell (11/19/2013)
d.velders (11/19/2013)
It's indead an idea to generate a SP that does the calculation of these working days.
Does anyone have an example on how to do this or even better a example script ?


assuming you have a Calendar table, here's one suggestion:
IF  DAY(GETDATE()) <=10 
AND EXISTS(SELECT 1 FROM TallyCalendar WHERE IsHoliday = 0 AND isWorkDay = 1 And TheDate = CONVERT(date,getdate()) )
BEGIN
PRINT 'first ten days of month, continue processing'
END
ELSE
BEGIN
print 'second third month, alternate processing ?'
END



IF  DAY(GETDATE()) &lt;=10 

this could be 12-th, 13-th and max 14-th of the month if the 1st and 2nd of the month are Sat and Sun. It could even be 15-th if the 3th is a national holiday, and calculation even tougher if there are two holidays in series during business days.
The algorithm for always determining the first 10 working days, and at the same time skipping national holidays in business days is not that easy.
Albeit, a Tally table could help a lot.

Regards
IgorMi


Post #1515610
Posted Tuesday, November 19, 2013 6:04 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:39 PM
Points: 3,596, Visits: 5,113
Start with a Calendar table. For this example, I'll give you my utility FUNCTION to generate a calendar on the fly.

CREATE FUNCTION [dbo].[GenerateCalendar] 
(
@FromDate DATETIME,
@NoDays INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== High speed code provided courtesy of Jeff Moden (idea by Dwain Camps)
--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
SELECT [SeqNo] = t.N,
[Date] = dt.DT,
[Year] = dp.YY,
[YrNN] = dp.YY % 100,
[YYYYMM] = dp.YY * 100 + dp.MM,
[BuddhaYr] = dp.YY + 543,
[Month] = dp.MM,
[Day] = dp.DD,
[WkDNo] = DATEPART(dw,dt.DT),
[WkDName] = CONVERT(NCHAR(9),dp.DW),
[WkDName2] = CONVERT(NCHAR(2),dp.DW),
[WkDName3] = CONVERT(NCHAR(3),dp.DW),
[JulDay] = dp.DY,
[JulWk] = dp.DY/7+1,
[WkNo] = dp.DD/7+1,
[Qtr] = DATEPART(qq,dt.Dt),
[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,
[LdOfMo] = DATEPART(dd,dp.LDtOfMo),
[LDtOfMo] = dp.LDtOfMo
FROM cteTally t
CROSS APPLY ( --=== Create the date
SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)
) dt
CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"
-- (Cascading CROSS APPLY, Acourtesy of ChrisM)
SELECT YY = DATEPART(yy,dt.DT),
MM = DATEPART(mm,dt.DT),
DD = DATEPART(dd,dt.DT),
DW = DATENAME(dw,dt.DT),
Dy = DATEPART(dy,dt.DT),
LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)

) dp
GO


Next add a holiday table.

DECLARE @Today DATE = '2013-12-15';  -- Replace in your SP with GETDATE()
DECLARE @StartOfMonth DATE = DATEADD(month, DATEDIFF(month, 0, @Today), 0);

CREATE TABLE #Holidays
(
Holiday_Start DATE NOT NULL
,Holiday_End DATE NULL
);

INSERT INTO #Holidays
SELECT '2013-12-02', NULL UNION ALL SELECT '2013-12-09', '2013-12-10'


Now JOIN these two tables such that you can construct a flag indicating whether @Today is a workday or not.

SELECT TOP 1 a.[Date], a.WkDName
,WorkDay = ROW_NUMBER() OVER (ORDER BY a.[Date])
,TodayIsWorkDay = CASE WHEN a.[Date] = @Today THEN 1 ELSE 0 END
FROM dbo.GenerateCalendar(@StartOfMonth, 1+DATEDIFF(day, @StartOfMonth, @Today)) a
LEFT JOIN
(
SELECT d=CAST(DATEADD(day, n, Holiday_Start) AS DATETIME)
FROM #Holidays
CROSS APPLY
(
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
) Tally (n)
WHERE DATEADD(day, n, Holiday_Start) BETWEEN Holiday_Start AND ISNULL(Holiday_End, Holiday_Start)
) b ON a.[Date] = b.d
WHERE a.WkDName NOT IN ('Sunday', 'Saturday') AND d IS NULL
ORDER BY a.[Date] DESC;
GO
DROP TABLE #Holidays;


If @Today is not a workday, [Date], WkDName and WorkDay will contain the info for the prior work day. The TodayIsWorkDay flag will indicate whether @Today is a work day or not.

Note that the Tally (derived) table must include enough entries to span the longest holiday period (including weekend days).



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1515856
Posted Friday, November 22, 2013 5:58 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:49 AM
Points: 5, Visits: 26
This procedure works, tnx
Post #1516745
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse