SQL Jobs Schedule to run last working day in a month

I need to schedule a job the last working day of every month, Italian holyday calendar. First i need this function to calculate if a particular day is  a workind day or not (Eastern day included). With this function the task is simple. Create a new Job schedule with Sql Server Agent, the job contains two step, the first check if the real job need to execute, the second step is the real job. The second step is executed only if the first succeeded. The check is simple:

DECLARE @data AS DATETIME
 ,@month AS INT
 ,@newdate AS DATETIME
 ,@newmonth AS INT
SELECT @data = getdate()

IF [dbo].[ufn_IsWorkingDay](@data) = 0
 RAISERROR (
 'Not working day'
 ,16
 ,127
 )
ELSE
BEGIN
 SELECT @month = datepart(m, @data)
 SELECT @newdate = [dbo].[ufn_NextWorkingDay](@data)
 SELECT @newmonth = datepart(m, @newdate)

IF @newmonth = @month
 RAISERROR (
 'Not at the end'
 ,16
 ,127
 )
END

If current date is not working day the we raise a simple error, so the second step do not will be executed. If the current day is a working day, calculate the next working day, if the mont part of the date is different then the current day is the last working day of the month so the second step can be executed.

The function ufn_NextWorkingDay is simple:

CREATE FUNCTION [dbo].[ufn_NextWorkingDay]
( 
 @Date datetime =GETDATE
)
returns datetime 
as 
begin
    declare @newdate datetime,@return as datetime
    SELECT @newdate = dateadd(day, 1, @Date)
    if [dbo].[ufn_IsWorkingDay](@newdate)=1 
         select @return = @newdate
   else
         select @return = [dbo].[ufn_NextWorkingDay](@newdate)
   return @return 
end


Pubblicato

in

da

Tag: