SQL Server 2008 R2 – Run SQL Job Earlier on Last Work Day of Month

jobssql-server-2008-r2sql-server-agent

Right now, I have a SQL job (within SQL Server 2008 R2) that dumps a complex select statement into a table every weeknight (excluding weekends) and it works great.

Due to our company's Month End procedures, they require the data in this table for that day when running Month End. Right now I fire the job off manually, disable it so the same data isn't dump twice for the same day then enable the job again the following business day.

Is there a way to schedule this? Or script a job and have it depict whether or not it's the last work day of the month and depict what time to fire based on this parameter?

If this is not possible, then so be it I suppose… I haven't been able to find anything that might help point me to such a solution thus far. Any help is appreciated though!

EDIT: Here's a sample result set of a view that I'll using for the last work day of the month comparison (as you can see, I only have Work Days selected):

    CalendarDate:           WorkDay:
    2015-03-02 00:00:00.000        1
    2015-03-03 00:00:00.000        1
    2015-03-04 00:00:00.000        1
    2015-03-05 00:00:00.000        1
    2015-03-06 00:00:00.000        1
    2015-03-09 00:00:00.000        1
    2015-03-10 00:00:00.000        1
    2015-03-11 00:00:00.000        1
    2015-03-12 00:00:00.000        1
    2015-03-13 00:00:00.000        1
    2015-03-16 00:00:00.000        1
    2015-03-17 00:00:00.000        1
    2015-03-18 00:00:00.000        1

Best Answer

You can write this more tersely (e.g. without any variables at all), but I thought logically breaking it up might be more helpful.

DECLARE @Today DATE = SYSDATETIME();

DECLARE @FirstDayNextMonth DATE = DATEADD(MONTH, 1,
 DATEADD(DAY, 1-DAY(@Today), @Today));

DECLARE @LastWorkDayThisMonth DATE;

SELECT @LastWorkDayThisMonth = MAX(CalendarDate)
  FROM dbo.CalendarView -- guessing on name here
  WHERE WorkDay = 1
  AND CalendarDate >= @Today
  AND CalendarDate < @FirstDayNextMonth;

IF @LastWorkDayThisMonth = @Today
BEGIN
  -- do your last work day of the month stuff
END

(Also, I suggest changing your calendar view to expose DATE and not DATETIME.)

Related Question