Post reply

Alternating weekly schedule

  • May 3, 2016 at 12:06 pm

    #318701

    I'm trying to create an Agent job that alternates it action each Saturday. In other words:

    first week do X

    next week do Y

    next week do X

    next week do Y

    etc...

    I can't figure out the date logic to accomplish this since a Saturday could fall on any date. Any ideas?

  • May 3, 2016 at 12:13 pm

    #1876376

    two ideas.

    can you just use two jobs, one scheduled to start on Saturday, and repeat each two weeks?

    can you use a datemath and inline logic within the job step to determine if the inner command should run?

    something like

    IF DATEdiff(wk,0,getdate()) % 2 = 0 --or 1

    BEGIN

    EXEC(xproc)

    END

    IF DATEdiff(wk,0,getdate()) % 2 = 1 --or 0

    BEGIN

    EXEC(yproc)

    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!

  • May 3, 2016 at 12:21 pm

    #1876382

    Lowell beat me to the suggestion.

    CREATE PROCEDURE Choose_Weekly_SP

    AS

    IF( SELECT DATEDIFF(wk, date_created, GETDATE()) % 2

    FROM msdb.dbo.sysjobs

    WHERE name = 'YourJob'

    ) = 0

    EXECUTE ProcedureX;

    ELSE

    EXECUTE ProcedureY;

    GO

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 3, 2016 at 12:27 pm

    #1876384

    Thanks guys! That's exactly what I was looking for.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply