Hi
Trying to count the number of Workdays only between 2 dates. [Startdate] &
[Enddate] can anyone help please.
Cheers
Sarah
One way of doing this is to have a calendar table and a
holidays table. This is school days, but it could just as
easily be business or work days. Omit the check constraints and
just enter them in table design as validation rules. Holiday
can be in the calendar table rather than in a separate holidays
table.
CREATE TABLE SchoolCalendar
(class_date DATETIME NOT NULL PRIMARY KEY,
weekday_nbr INTEGER NOT NULL,
school_day INTEGER DEFAULT 1 NOT NULL,
holiday INTEGER DEFAULT 0 NOT NULL,
CONSTRAINT ck_schoolcalendar_weekday_nbr
CHECK weekday_nbr BETWEEN 1 AND 7,
CONSTRAINT ck_schoolcalendar_school_day
CHECK school_day IN (0,1),
CONSTRAINT ck_schoolcalendar_holiday
CHECK holiday IN (0,1))
After calendar is filled, make Saturday and Sunday as non-
school days.
UPDATE SchoolCalendar
SET SchoolCalendar.school_day = 0
WHERE (((SchoolCalendar.weekday_nbr) = 1
OR (SchoolCalendar.weekday_nbr) = 7));
CREATE TABLE Holidays
(holiday DATETIME NOT NULL PRIMARY KEY,
holiday_name VARCHAR (30) NOT NULL)
Query: Count class days in calendar period
-------------------------------------------
PARAMETERS [Enter start date:] DateTime, [Enter end date:]
DateTime;
SELECT COUNT(* )
FROM SchoolCalendar
WHERE (((SchoolCalendar.class_date) BETWEEN [Enter start
date:] AND [Enter end date:])
AND SchoolCalendar.school_day = 1
AND NOT EXISTS (SELECT Holidays.holiday
FROM Holidays
WHERE Holidays.holiday =
SchoolCalendar.class_date);
