2

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"? – Aaron Bertrand Apr 9 '15 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 '15 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. – Aaron Bertrand Apr 9 '15 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 '15 at 11:42
  • @AaronBertrand any luck thus far? Or did you have a chance to look into this yet. – JCBWS Apr 14 '15 at 19:25
2

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.)

| improve this answer | |
  • 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 '15 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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