November 19, 2013 at 2:47 am
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 ?
November 19, 2013 at 3:36 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 19, 2013 at 3:43 am
What about when one of those day is a public/national holiday?
Igor Micev,My blog: www.igormicev.com
November 19, 2013 at 3:45 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 19, 2013 at 4:05 am
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.
Igor Micev,My blog: www.igormicev.com
November 19, 2013 at 7:57 am
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 ?
November 19, 2013 at 8:35 am
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
November 19, 2013 at 8:48 am
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()) <=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
Igor Micev,My blog: www.igormicev.com
November 19, 2013 at 6:04 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 22, 2013 at 5:58 am
This procedure works, tnx
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply