Douglas said:
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.
--