DateDiff counting weekdays only

[Guest]

I need to calculate the number of weekdays, weekends should not be included,
between an[OrderDate] and [ShipDate] in my database report. When I use the
"DateDiff" function it returns both weekdays and weekends; i.e.
=DateDiff("d",[OrderDate],[ShipDate])

 

[Douglas J. Steele]

That's how the DateDiff function is supposed to work.

To only get weekdays, take a look in the Date/Time section of "The Access
Web" http://www.mvps.org/access/

There are a couple of solutions there.

 

[onedaywhen]

To only get weekdays, take a look in the Date/Time section of "The Access
Web" http://www.mvps.org/access/

If you application is data-centric <vbg> consider the standard trick of
an auxiliary Calendar table:

(Quote) Getting the number of business days between two dates. This
question comes up quite often, and was the catalyst for this article.
Many people would consider writing a UDF that loops through all the
days in a range, determines whether each one is a working day, apply
business logic to determine if the day is considered a holiday, and
keep a running count. Luckily, we can do this much more easily against
a calendar table. Let's say we wanted all the business days in April
2004:

SELECT COUNT(*) FROM dbo.Calendar
WHERE isWeekday = 1
AND isHoliday = 0
AND dt >= '20040401' AND dt < '20040501'

(unquote)

http://www.aspfaq.com/show.asp?id=2519

Jamie.

--

 

[Guest]

Thanks for the help!!!

That's how the DateDiff function is supposed to work.

To only get weekdays, take a look in the Date/Time section of "The Access
Web" http://www.mvps.org/access/

There are a couple of solutions there.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I need to calculate the number of weekdays, weekends should not be
included,
between an[OrderDate] and [ShipDate] in my database report. When I use the
"DateDiff" function it returns both weekdays and weekends; i.e.
=DateDiff("d",[OrderDate],[ShipDate])

 

[Douglas J. Steele]

If you application is data-centric <vbg> consider the standard trick of
an auxiliary Calendar table:

(Quote) Getting the number of business days between two dates. This
question comes up quite often, and was the catalyst for this article.
Many people would consider writing a UDF that loops through all the
days in a range, determines whether each one is a working day, apply
business logic to determine if the day is considered a holiday, and
keep a running count. Luckily, we can do this much more easily against
a calendar table. Let's say we wanted all the business days in April
2004:

SELECT COUNT(*) FROM dbo.Calendar
WHERE isWeekday = 1
AND isHoliday = 0
AND dt >= '20040401' AND dt < '20040501'

(unquote)

http://www.aspfaq.com/show.asp?id=2519

The problem with such a solution is that it requires that someone maintain
the calendar.

One approach, of course, is use a more generic approach to holidays. I
described one way to do this in my June, 2005 "Access Answers" column in
Pinnacle Publication's "Smart Access". You can download the column (and
sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html.

 

[onedaywhen]

The problem with such a solution is that it requires that someone maintain
the calendar.

Not really. Once the Calendar table has been established, using one row
per day for the next, say, twenty years, it will require little in the
way of regular maintenance e.g. Monday is fairy likely to remain a
workday.

One approach, of course, is use a more generic approach to holidays. I
described one way to do this in my

The advantage of the Calendar table approach is it *is* customizable
i.e. as simple as an SQL UPDATE command.

Does your generic routine account for my enterprise's Founder's Day
being a movable feast that always falls on the second Friday in
October? reintroduction of the Three-Day Week? the office opening
hours? My Calendar table surely does.

Jamie.

--

 

[Douglas J. Steele]

The advantage of the Calendar table approach is it *is* customizable
i.e. as simple as an SQL UPDATE command.

Does your generic routine account for my enterprise's Founder's Day
being a movable feast that always falls on the second Friday in
October? reintroduction of the Three-Day Week? the office opening
hours? My Calendar table surely does.

Obviously you couldn't be bothered to read the article.

 

[onedaywhen]

Obviously you couldn't be bothered to read the article.

Actually, that is correct. It's not a simple link to an article, is it?
Rather, it requires downloading a zip file, virus scanning, extracting,
opening a PDF containing multiple articles and navigating the correct
article by scanning the text for the one you are likely referring to. I
chose instead to give you an opportunity to 'talk up' your article <g>.


Now I have taken the time to do all the above I see we are in the main
talk cross purposes. Your proposal is to build a calendar of holidays
and have provided what is no doubt some very nifty and useful code to
create the required rows. Mine is a variation on this theme by
proposing a table of *all* days, using a column to indicate whether the
column is a holiday, implicitly leaving the reader all alone to create
those rows themselves. I think with a combination of the two proposals,
we could be on to a real winner <g>.

Jamie.

--

 

[onedaywhen]

Your proposal is to build a calendar of holidays
and have provided what is no doubt some very nifty and useful code to
create the required rows.

I chickened out of saying this earlier...

Yours is a nifty 'rules engine' way of predicting public holidays. From
a quick look at how Easter is calculated, I think you still need a
table of dates of Easter Sundays, whether Western or Orthodox are
appropriate for each locationID and, as you point out, whether the
Sunday is itself a considered holiday.

However, it seems a bit complex and a lot of trouble when you consider
that information about public holidays is, well, not exactly scarce
e.g. this has the advantage of being 'official':

http://www.dti.gov.uk/employment/bank-public-holidays/bank-public-holidays/page18893.html

This is analogous to the whole approach, IMO. Why go to the
bother/expense of 'calculating' values when you can get them
ready-rolled from a website/database table?

Note from the above source that some holidays are marked, 'Subject to
Royal Proclamation': there will be exceptions to rules that the engine
cannot predict e.g. the occasion when our organisation voted to swap
the Bank Holiday (discretional for most employers) in lieu of the 1st
Jan falling on a Sunday for an extra day during the Christmas break.

Jamie.

--

 

[Douglas J. Steele]

I chickened out of saying this earlier...

Yours is a nifty 'rules engine' way of predicting public holidays. From
a quick look at how Easter is calculated, I think you still need a
table of dates of Easter Sundays, whether Western or Orthodox are
appropriate for each locationID and, as you point out, whether the
Sunday is itself a considered holiday.

The various algorithms I checked (and included at least one of in the code)
is supposed to be able to correctly calculate Easter Sunday.

However, it seems a bit complex and a lot of trouble when you consider
that information about public holidays is, well, not exactly scarce
e.g. this has the advantage of being 'official':

http://www.dti.gov.uk/employment/bank-public-holidays/bank-public-holidays/page18893.html

This is analogous to the whole approach, IMO. Why go to the
bother/expense of 'calculating' values when you can get them
ready-rolled from a website/database table?

Because if you don't have a rule-based approach, and your tables only go to
year n, what happens in year n+1 if someone forgets to update the table? My
concern is based on actual experience: we used a similar approach to what
you're suggesting, and prepopulated our table with several years, fully
expecting the application to be retired before the final date was achieved.
You guessed it: the application lasted longer than anticipated, and no one
was assigned the responsibility of updating the table.

 

[onedaywhen]

Because if you don't have a rule-based approach, and your tables only go to
year n, what happens in year n+1 if someone forgets to update the table?

The trick is to avoid the mistake of failing to provide enough data
<g>. In this thread, I proposed a Calendar for a tentative 20 years
(circa 8K rows). Under normal circumstances (OLTP, no history, etc) I
go for all dates between years 1990 to 2100 (circa 40K rows); I figure
the industry-wide Y2.1K project will pick up the maintenance after that
<g>.

However, as with a rules-based approach, this does not mean zero
maintenance e.g. how many years in advance was it known that 2002-06-03
was to be Golden Jubilee Bank Holiday? (About one, IIRC).

The various algorithms I checked (and included at least one of in the code)
is supposed to be able to correctly calculate Easter Sunday.

In the article you say, "I'm not going to explain how any of the
algorithms I found work nor am I going to guarantee that they're
completely accurate". The lack of accuracy would be hard to sell to a
client who could themselves google to find the 'published' dates! I say
again, why go to the bother of *predicting* something (accurately or
otherwise) that is already known?

To be honest, I'm scratching my head here. This is a database
application. Storing 40K+ dates in table is no big deal. The rule of
thumb is storing the results of a calculation is acceptable is the
calculation cost is greater than a simple disk read (I suppose the
immediate cost here is loss of accuracy). I have a ready-rolled script
to cerate a Calendar table and admin can similarly modify it with SQL
code... To me, generating public holidays on the fly is akin to
generating employee_number based on employment_start_date: sure, you
can do it, but why not just store it?

included at least one [algorithm] in the code

Not in the SQL code With an ADO connection from Excel, and
considering the title of this thread is about counting weekdays, I
tried using your MDB to count workdays for locationid=3 between two
dates.

I couldn't see how I could progress without a table of Easter Sundays
(this grabbed from the net, ten years' worth will do <g>):

CREATE TABLE EasterSundays (
LocationId INTEGER NOT NULL,
easter_sunday_date DATETIME NOT NULL,
PRIMARY KEY (LocationId, easter_sunday_date)
)
;
INSERT INTO EasterSundays (LocationId, easter_sunday_date)
VALUES (1, #2000-04-23#)
;
INSERT INTO EasterSundays (LocationId, easter_sunday_date)
SELECT DT1.LocationId, DT1.easter_sunday_date
FROM (
SELECT E2.LocationId, #2001-04-15# AS easter_sunday_date
FROM EasterSundays AS E2
UNION ALL
SELECT E2.LocationId, #2002-03-31#
FROM EasterSundays AS E2
UNION ALL
SELECT E2.LocationId, #2003-04-20#
FROM EasterSundays AS E2
UNION ALL
SELECT E2.LocationId, #2004-04-11#
FROM EasterSundays AS E2
UNION ALL
SELECT E2.LocationId, #2005-03-27#
FROM EasterSundays AS E2
UNION ALL
SELECT E2.LocationId, #2006-04-16#
FROM EasterSundays AS E2
UNION ALL
SELECT E2.LocationId, #2007-04-08#
FROM EasterSundays AS E2
UNION ALL
SELECT E2.LocationId, #2008-03-23#
FROM EasterSundays AS E2
UNION ALL
SELECT E2.LocationId, #2009-04-12#
FROM EasterSundays AS E2
) AS DT1
;

In absence of a Calendar table, I needed a Sequence table of integers
to help enumerate the dates in the Holiday table (again, ten years will
do):

CREATE TABLE Sequence (
seq INTEGER NOT NULL PRIMARY KEY
)
;
INSERT INTO [Sequence] (seq) VALUES (0)
;
INSERT INTO [Sequence] (seq)
SELECT DT1.Seq
FROM (
SELECT 1 AS Seq FROM Sequence
UNION ALL
SELECT 2 FROM Sequence
UNION ALL
SELECT 3 FROM Sequence
UNION ALL
SELECT 4 FROM Sequence
UNION ALL
SELECT 5 FROM Sequence
UNION ALL
SELECT 6 FROM Sequence
UNION ALL
SELECT 7 FROM Sequence
UNION ALL
SELECT 8 FROM Sequence
UNION ALL
SELECT 9 FROM Sequence
) AS DT1
;

First, the holidays for which FixedMonth and FixedMonthDay have been
supplied:

SELECT DATEADD('yyyy', S1.Seq, DATESERIAL(DATEPART('yyyy',
#2004-04-01#), H1.FixedMonth, H1.FixedMonthDay)) AS holiday_date
FROM Holidays AS H1,
Sequence AS S1
WHERE H1.Locationid = 1
AND H1.FixedMonth IS NOT NULL
AND H1.FixedMonthDay IS NOT NULL
AND S1.seq BETWEEN 0 AND DATEDIFF('yyyy', #2004-04-01#, #2005-01-31#)
AND DATEADD('yyyy', S1.Seq, DATESERIAL(DATEPART('yyyy', #2004-04-01#),
H1.FixedMonth, H1.FixedMonthDay)) BETWEEN #2004-04-01# AND #2005-01-31#
;

Next the Easter holidays:

SELECT DATEADD('d', H1.RelativeToEasterSunday , E1.easter_sunday_date)
AS holiday_date
FROM Holidays AS H1
INNER JOIN EasterSundays AS E1
ON E1.LocationID = H1.LocationID
WHERE H1.RelativeToEasterSunday IS NOT NULL
AND H1.LocationID = 3
AND E1.easter_sunday_date BETWEEN #2004-04-01# AND #2005-01-31#
;

Then I got bogged down in detail: is Easter Sunday a holiday? what do I
do about the 2006-12-26 being a Sunday? what are the dates of the
weekdays so I don't double count? what are the other columns in the
Holidays table for? I came to the conclusion this would make a great
Excel spreadsheet

Jamie.

--