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
  • 2
    You need a calendar table. For example, what if the last weekday of the month is a holiday? How exactly does your business define "work day"? Apr 9, 2015 at 17:01
  • Ah, and we have an ERP system (with a SQL database on the same SQL server I'm working on) that marks what is a work day and stores this in a table; so that aspect is covered... Now... can I make the script depict when the job fires? Or would have to make this two schedules for each work day (one for the normal fire after hours and one for the month end day) and go from there.
    – JCBWS
    Apr 9, 2015 at 18:46
  • You need to check today's date against the table and see if today is the last work day of the current month. If you want code, you're going to need to provide more details about your ERP table. Apr 9, 2015 at 19:03
  • @AaronBertrand I've added some sample data I'll be using for the work day comparison into my question.
    – JCBWS
    Apr 10, 2015 at 11:42
  • @AaronBertrand any luck thus far? Or did you have a chance to look into this yet.
    – JCBWS
    Apr 14, 2015 at 19:25

1 Answer 1


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


 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
  -- do your last work day of the month stuff

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

  • Thanks for this, Aaron! I've only recently joined all the other communities underneath Stack Exchange (Database Administrators being one of them). Once I get enough reputation, I'll +1 your answer as well as billinkc's suggestion.
    – JCBWS
    Apr 15, 2015 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.