Nth Previous Business Day?

[(PeteCresswell)]

We so a lot of calculations where we need to know the prior
business day or the date of two or three business days ago.

Prior weekdays, slam-dunk... no problem... all in memory, runs
like a flash.

Prior *business* days, we're getting killed by the I/O necessary
to go to a table of company holidays.

Only takes a fraction of a second, but it's used in iterative
processes. Typically we have to run through a hundred or two
hundred.... maybe even 2 thousand dates and discover the Nth
prior business day for each in order to come up with a computed
result.

Only thing I can think of is to get that determination of the
loop and batch it somewhere into a table that can be joined to.

Calendar never changes. Company holidays only change once a
year or so when somebody updates the table. Seems like we could
maintain a table with one row per calendar day and as many
columns as the max number of prior biz days we'd ever need.

Has anybody been here?

 

[John W. Vinson]

Calendar never changes. Company holidays only change once a
year or so when somebody updates the table. Seems like we could
maintain a table with one row per calendar day and as many
columns as the max number of prior biz days we'd ever need.

Sounds like a plan. You can use Insert... Series in Excel to create a table of
every date from now through the next twenty years. Import it into Access; run
a real quick query to delete weekends:

DELETE * FROM AllDates WHERE Weekday([WorkDate]) IN (1, 7);

Then delete the fixed holidays, and then (manually) delete the movable
holidays (e.g. four-day weekends, movable feasts).

You could then simply use a Join with a count of records to find the count of
workdays with only one or a couple of joins.