SQL Server – Datepart Logic for Custom SQL Agent Job Schedule

sql serversql-server-agent

Wanted help in code for below logic.

Job A needs to be run on 8th of month.
Job B needs to be run on Tuesday only.
Job c needs to be run on Daily(Minus Tuesday & Sunday).
And If Its Tuesday and 8Th Job A takes the precedence of Job.

i have coded logic for half of it.

Else If Begin logic,

--Tuesday Logic(0=Monday,1=Tuesday,2=Wed,3=Thu,4=Friday,5=Saturday,6=Sunday)
IF (SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,0) + 1)=1 -- If it is tuesday execute the Weekly+Daily Job
       Print 'executing weekly and daily tables'

ELSE
BEGIN
       Print 'executing daily job'
END
END

Best Answer

Here is my logic:

BEGIN
--FULL REFRESH Cycle
declare @EightDayofMonth datetime =(DATEADD(DAY, 8-DAY(GETDATE()), CONVERT(date, GETDATE())));
declare @actuladayofmonth datetime = CONVERT(date, getdate());

----checking for 8th day --condition 1 ( 8th of month and NOT Tuesday and not sunday) if any other day job executes
IF (@actuladayofmonth=@EightDayofMonth) AND (DATEPART(WEEKDAY,GETDATE())<>3 OR DATEPART(WEEKDAY,GETDATE())<>1)

BEGIN
Print 'disabe daily job'
print 'executing full job'
    END

--checking for monthly 8th is tuesday logic
ELSE IF (@actuladayofmonth=@EightDayofMonth) AND DATEPART(WEEKDAY,GETDATE())=3
BEGIN

Print 'Disable Tuesday Weekly'
print 'Disable Daily on tuesday'
Print 'Executing full job'
    END

ELSE IF (@actuladayofmonth=@EightDayofMonth) AND DATEPART(WEEKDAY,GETDATE())=1
BEGIN
Print 'Execute disable sunday'
 END
  END

Related Solutions

Sql-server – Specify a frequency range for a SQL agent schedule

I have dome something like this before by putting logic into the code you are calling to verify if it's in that range and just schedule to run everyday and if it's in that range execute, otherwise exit out gracefully?

Sql-server – can I convert an integer to base-2 without a function

Unless I'm completely misunderstanding your question, then the below T-SQL should do it (please let me know if this isn't what you're looking for). It utilizes bitwise operators to pull out the bit masks for the days:

-- <TEST DATA>
create table DayTable
(
    id int identity(1, 1) not null,
    DayMask tinyint not null
)
go

insert into DayTable
values
(
    62
),
(
    12
)
-- </TEST DATA>

select
        case 
            when DayMask & 1 > 0
                then 1
            else 0
        end
    as Sunday,
        case 
            when DayMask & 2 > 0
                then 1
            else 0
        end
    as Monday,
        case 
            when DayMask & 4 > 0
                then 1
            else 0
        end
    as Tuesday,
        case 
            when DayMask & 8 > 0
                then 1
            else 0
        end
    as Wednesday,
        case 
            when DayMask & 16 > 0
                then 1
            else 0
        end
    as Thursday,
        case 
            when DayMask & 32 > 0
                then 1
            else 0
        end
    as Friday,
        case 
            when DayMask & 64 > 0
                then 1
            else 0
        end
    as Saturday
from DayTable

Best Answer

Related Solutions

Sql-server – Specify a frequency range for a SQL agent schedule

Sql-server – can I convert an integer to base-2 without a function

Related Question