Pledge Billing - Last Business Day

OR, alternative question: Does anyone have magic SQL that auto populates pledge billing dates with the last business day of the month?

OR, alternative question: Does anyone have magic SQL that auto populates pledge billing dates with the last business day of the month?

This was fun to figure out!

This is a single expression you can use in a select statement, and should work as long as your SET DATEFIRST setting is 7 (US default).

I've provided it with @datetime as an input parameter, but you could inline GETDATE() or replace them with a column from the select statement if needed. Line breaks are just for readability.

It works by getting the weekday of the first day of next month, and doing some math to it that results in the number of days to subtract from that day in order to get to the last monday-friday day in the previous month. Then that goes into the surrounding DATEADD function.

DECLARE @datetime datetime = '4-30-2017';

SELECT
  last_business_day =
    DATEADD(
      day,
      -1 * ((ABS((DATEPART(weekday, DATEADD(month, DATEDIFF(month, 0, @datetime) + 1, 0)) + 4) % 7 - 4)
              + ((DATEPART(weekday, DATEADD(month, DATEDIFF(month, 0, @datetime) + 1, 0)) + 4) % 7 - 4)) / 2) - 1,
      DATEADD(month, DATEDIFF(month, 0, @datetime) + 1, 0)
    );

(https://bitbucket.org/snippets/TN_WebShare/5X9ok)

As for the other thing, I actually just uploaded some code to the Bitbucket site that provides some tools for manipulating the Tessitura Report Server. This could be used, theoretically, to programmatically send report requests to the report server that don't fit into the built-in scheduling possibilities. Still pretty new, minimally tested, and dangerous, but I spent a lot of time reverse-engineering the report server the past couple weeks to see how I could make better use of it: https://bitbucket.org/TN_WebShare/report-server-tools/src/master/On-Demand%20Reporting/

(If you don't have access to Bitbucket, see information on accessing that as well as other resources here: https://tessituracoders.bitbucket.io/ I'm usually available to chat on the Slack team as well!)

Wow! How fun! Thanks so much for looking into this. We might end up going in a different direction to align with our subscription billing, but it's great to know this is possible!