Schedule a SQL Job only for the first ten working days of each month

  • 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 ?

  • 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

  • What about when one of those day is a public/national holiday?

    Igor Micev,My blog: www.igormicev.com

  • 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

  • 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

  • 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 ?

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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![/I]

    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

  • 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