Nth Previous Business Day?

  • Thread starter Thread starter (PeteCresswell)
  • Start date Start date
P

(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?
 
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.
Click to expand...

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.
 
Back
Top