Holiday Database

[paulmitchell507]

I realise this is not the first post on the subject, but I have so far
been unable to find a good solution. I have created a company holiday
database in Access 2000. The user enters a holiday request via a HTML
form and the data is put into the database via ADO.

I only want my user to enter a start/end date, and the the total
number or working days for each request. Using SQL, I would like to
store the start date, end date + any days in between in my DB.

i.e if I enter 2 dates 01/08/08 - 03/08/08 I want to store in my DB
01/08/08,02/08/08 & 03/08/08.

The reason for this is, further down the line, I want to use SQL to
return any user who has requested holiday at the same time. I am not
concerned about weekends, as I am not using the values to calculate
number of holidays requested/outstanding.

Help!

Paul

 

[Michael Gramelspacher]

I realise this is not the first post on the subject, but I have so far
been unable to find a good solution. I have created a company holiday
database in Access 2000. The user enters a holiday request via a HTML
form and the data is put into the database via ADO.

I only want my user to enter a start/end date, and the the total
number or working days for each request. Using SQL, I would like to
store the start date, end date + any days in between in my DB.

i.e if I enter 2 dates 01/08/08 - 03/08/08 I want to store in my DB
01/08/08,02/08/08 & 03/08/08.

The reason for this is, further down the line, I want to use SQL to
return any user who has requested holiday at the same time. I am not
concerned about weekends, as I am not using the values to calculate
number of holidays requested/outstanding.

Help!

Paul

This requires a calendar table.

Assume your table is like this.


CREATE TABLE [Absences Days] (
employee_id INTEGER NOT NULL
REFERENCES Employees (employee_id),
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
PRIMARY KEY (employee_id, start_date));

Using a calendar table, this gets all work days in the absence periods.

Query: Employee Absences Days
--------------------------------
SELECT Calendar.calendar_date,
[Absences Periods].employeeID
FROM Calendar,
[Absences Periods]
WHERE (((Calendar.calendar_date) BETWEEN [Absences Periods].[start_date]
AND [Absences Periods].[end_date])
AND ((Calendar.work_day) = 1)
AND ((Calendar.holiday) = 0));

These days have multiple absences.


Query: Multiple Absences Days
------------------------------
SELECT [Employee Absences Days].calendar_date
FROM [Employee Absences Days]
GROUP BY [Employee Absences Days].calendar_date
HAVING (((Count(*))>1));

These are the employees absent on the same days.

Query: Employees Absent on Same Day
--------------------------------------
SELECT [Employee Absences Days].employeeID,
Employees.EmployeeName,
[Employee Absences Days].calendar_date
FROM [Multiple Absences Days]
INNER JOIN ([Employee Absences Days]
INNER JOIN Employees
ON [Employee Absences Days].employeeID =
Employees.EmployeeId)
ON [Multiple Absences Days].calendar_date = [Employee Absences
Days].calendar_date
ORDER BY [Employee Absences Days].calendar_date;

 

[paulmitchell507]

I realise this is not the first post on the subject, but I have so far
been unable to find a good solution.  I have created a company holiday
database in Access 2000.  The user enters a holiday request via a HTML
form and the data is put into the database via ADO.

I only want my user to enter a start/end date, and the the total
number or working days for each request.  Using SQL, I would like to
store the start date, end date + any days in between in my DB.

i.e if I enter 2 dates 01/08/08 - 03/08/08 I want to store in my DB
01/08/08,02/08/08 & 03/08/08.

The reason for this is, further down the line, I want to use SQL to
return any user who has requested holiday at the same time.  I am not
concerned about weekends, as I am not using the values to calculate
number of holidays requested/outstanding.

Paul

This requires a calendar table.

Assume your table is like this.

CREATE TABLE [Absences Days] (
employee_id INTEGER NOT NULL
REFERENCES Employees (employee_id),
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
PRIMARY KEY (employee_id, start_date));

Using a calendar table, this gets all work days in the absence periods.

Query: Employee Absences Days
--------------------------------
SELECT Calendar.calendar_date,
       [Absences Periods].employeeID
FROM   Calendar,
       [Absences Periods]
WHERE  (((Calendar.calendar_date) BETWEEN [Absences Periods].[start_date]
                                          AND [Absences Periods].[end_date])
        AND ((Calendar.work_day) = 1)
        AND ((Calendar.holiday) = 0));

These days have multiple absences.

Query: Multiple Absences Days
------------------------------
SELECT [Employee Absences Days].calendar_date
FROM [Employee Absences Days]
GROUP BY [Employee Absences Days].calendar_date
HAVING (((Count(*))>1));

These are the employees absent on the same days.

Query: Employees Absent on Same Day
--------------------------------------
SELECT   [Employee Absences Days].employeeID,
         Employees.EmployeeName,
         [Employee Absences Days].calendar_date
FROM     [Multiple Absences Days]
         INNER JOIN ([Employee Absences Days]
                     INNER JOIN Employees
                       ON [Employee Absences Days].employeeID =
Employees.EmployeeId)
           ON [Multiple Absences Days].calendar_date = [Employee Absences
Days].calendar_date
ORDER BY [Employee Absences Days].calendar_date;- Hide quoted text -

- Show quoted text -

I don't want to sound dense...but how do I create a calendar table? I
don't need to worry about bank holidays or weekends as I just want to
use the calendar to tell me who is on holiday on the specific day.

 

[paulmitchell507]

This requires a calendar table.

Assume your table is like this.

CREATE TABLE [Absences Days] (
employee_id INTEGER NOT NULL
REFERENCES Employees (employee_id),
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
PRIMARY KEY (employee_id, start_date));

Using a calendar table, this gets all work days in the absence periods.

Query: Employee Absences Days
--------------------------------
SELECT Calendar.calendar_date,
       [Absences Periods].employeeID
FROM   Calendar,
       [Absences Periods]
WHERE  (((Calendar.calendar_date) BETWEEN [Absences Periods].[start_date]
                                         AND [Absences Periods].[end_date])
        AND ((Calendar.work_day) = 1)
        AND ((Calendar.holiday) = 0));

These days have multiple absences.

Query: Multiple Absences Days
------------------------------
SELECT [Employee Absences Days].calendar_date
FROM [Employee Absences Days]
GROUP BY [Employee Absences Days].calendar_date
HAVING (((Count(*))>1));

These are the employees absent on the same days.

Query: Employees Absent on Same Day
--------------------------------------
SELECT   [Employee Absences Days].employeeID,
         Employees.EmployeeName,
         [Employee Absences Days].calendar_date
FROM     [Multiple Absences Days]
         INNER JOIN ([Employee Absences Days]
                     INNER JOIN Employees
                       ON [Employee Absences Days].employeeID =
Employees.EmployeeId)
           ON [Multiple Absences Days].calendar_date = [Employee Absences
Days].calendar_date
ORDER BY [Employee Absences Days].calendar_date;- Hide quoted text -

- Show quoted text -

I don't want to sound dense...but how do I create a calendar table?  I
don't need to worry about bank holidays or weekends as I just want to
use the calendar to tell me who is on holiday on the specific day.- Hide quoted text -

- Show quoted text -

FORGET THAT LAST POST (NOT THE BIT ABOUT BEING DENSE)
I HAVE CREATED THE CALENDAR_TBL

 

[Michael Gramelspacher]

FORGET THAT LAST POST (NOT THE BIT ABOUT BEING DENSE)
I HAVE CREATED THE CALENDAR_TBL

I note that I named the table one thing, and then referred to the table in the
queries with another name. Not real smart.

If you got the queries to work, then maybe also try this crosstab:

Query: Absences Schedule Crosstab
-----------------------------------
TRANSFORM First(IIf(a.absence_type Is Null,"N",a.absence_type)) AS Expr1
SELECT e.EmployeeId,
DATEADD("d",1 - WEEKDAY(e.calendar_date),e.calendar_date) AS [First],
DATEADD("d",7 - WEEKDAY(e.calendar_date),e.calendar_date) AS [Last]
FROM [Employee Days] AS e
LEFT JOIN [Employee Absences Days] AS a
ON (e.calendar_date = a.calendar_date)
AND (e.EmployeeId = a.employeeID)
GROUP BY e.EmployeeId,DATEADD("d",1 - WEEKDAY(e.calendar_date),e.calendar_date),
DATEADD("d",7 - WEEKDAY(e.calendar_date),e.calendar_date)
PIVOT Format(e.[calendar_date],"ddd") In
("Sun","Mon","Tue","Wed","Thu","Fri","Sat");

 

[paulmitchell507]

FORGET THAT LAST POST (NOT THE BIT ABOUT BEING DENSE)
I HAVE CREATED THE CALENDAR_TBL

I note that I named the table one thing, and then referred to the table in the
queries with another name. Not real smart.

If you got the queries to work, then maybe also try this crosstab:

Query: Absences Schedule Crosstab
-----------------------------------
TRANSFORM First(IIf(a.absence_type Is Null,"N",a.absence_type)) AS Expr1
SELECT   e.EmployeeId,
         DATEADD("d",1 - WEEKDAY(e.calendar_date),e.calendar_date) AS [First],
         DATEADD("d",7 - WEEKDAY(e.calendar_date),e.calendar_date) AS [Last]
FROM     [Employee Days] AS e
         LEFT JOIN [Employee Absences Days] AS a
           ON (e.calendar_date = a.calendar_date)
              AND (e.EmployeeId = a.employeeID)
GROUP BY e.EmployeeId,DATEADD("d",1 - WEEKDAY(e.calendar_date),e.calendar_date),
         DATEADD("d",7 - WEEKDAY(e.calendar_date),e.calendar_date)
PIVOT Format(e.[calendar_date],"ddd") In
("Sun","Mon","Tue","Wed","Thu","Fri","Sat");

I must confess, I am still struggling to understand your original
post, but I don't think it's the table name that's confusing me!! I
would very much appreciate it if you could explain in a bit more
detail what is happening?
At the moment I have a calendar table which has a single field
[cal_date] which is populated with a single date entry for the entire
year, 365 records.
I have a holiday table which contains the employee_ID, Start_Date and
End_Date, this is the table where I enter the requested holiday.
I am not sure I understand your first statement, are we creating a
temp table called Absence Days?

 

[Michael Gramelspacher]

I must confess, I am still struggling to understand your original
post, but I don't think it's the table name that's confusing me!! I
would very much appreciate it if you could explain in a bit more
detail what is happening?
At the moment I have a calendar table which has a single field
[cal_date] which is populated with a single date entry for the entire
year, 365 records.
I have a holiday table which contains the employee_ID, Start_Date and
End_Date, this is the table where I enter the requested holiday.
I am not sure I understand your first statement, are we creating a
temp table called Absence Days?

This is my table, which is the same as yours, except the name is different. It
is not a temp table. Maybe create the table in a new database and insert 3 or 4
rows of dummy data for testing.

CREATE TABLE [Absences Periods] (
employee_id INTEGER NOT NULL
REFERENCES Employees (employee_id),
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
absence_type CHAR(1) NOT NULL,
PRIMARY KEY (employee_id, start_date));


Query: Employee Absences Days
--------------------------------
SELECT [Absences Periods].[employee_id],
Calendar.calendar_date,
[Absences Periods].absence_type
FROM Calendar,
[Absences Periods]
WHERE Calendar.calendar_date BETWEEN [Absences Periods].start_date
AND [Absences Periods].end_date;


The query produces a reselt like this with my dummy data.

employee_id calendar_date absence_type
1 7/24/2008 S
1 7/25/2008 S
1 7/26/2008 S
1 7/27/2008 S
1 7/28/2008 S
1 7/29/2008 S
1 7/30/2008 S
3 7/30/2008 S
1 7/31/2008 S
3 7/31/2008 S
3 8/1/2008 S
5 8/1/2008 S
3 8/2/2008 S
5 8/2/2008 S
3 8/3/2008 S
5 8/3/2008 S
3 8/4/2008 S
5 8/4/2008 S
5 8/5/2008 S
5 8/6/2008 S

Can you get this far? If so, let's continue.

Query: Employee Days
----------------------
SELECT Employees.Employee_id,
a.calendar_date
FROM (SELECT Calendar.calendar_date
FROM Calendar
WHERE (Calendar.calendar_date BETWEEN #7/27/2008#
AND #8/9/2008#)) AS a, Employees;

You can use whatever dates you wish to see. These dates could be parameters, or
maybe a set period such as from today until 4 weeks from now.

Query results look like:

Employee_id calendar_date
1 7/27/2008
2 7/27/2008
3 7/27/2008
4 7/27/2008
5 7/27/2008
1 7/28/2008
2 7/28/2008
3 7/28/2008
4 7/28/2008
5 7/28/2008

and so forth.

Next the crosstab.

Query: Absences Schedule Crosstab
-----------------------------------
TRANSFORM First(IIf(a.absence_type Is Null,"N",a.absence_type)) AS Expr1
SELECT e.Employee_id,
DATEADD("d",1 - WEEKDAY(e.calendar_date),e.calendar_date) AS [First],
DATEADD("d",7 - WEEKDAY(e.calendar_date),e.calendar_date) AS [Last]
FROM [Employee Days2] AS e
LEFT JOIN [Employee Absences Days] AS a
ON (e.Employee_id = a.employee_id)
AND (e.calendar_date = a.calendar_date)
GROUP BY e.Employee_id,DATEADD("d",1 -
WEEKDAY(e.calendar_date),e.calendar_date),
DATEADD("d",7 - WEEKDAY(e.calendar_date),e.calendar_date)
ORDER BY DATEADD("d",1 - WEEKDAY(e.calendar_date),e.calendar_date)
PIVOT Format(e.[calendar_date],"ddd") In
("Sun","Mon","Tue","Wed","Thu","Fri","Sat");

Employee_id First Last Sun Mon Tue Wed Thu Fri Sat
1 7/27/2008 8/2/2008 S S S S S N N
2 7/27/2008 8/2/2008 N N N N N N N
3 7/27/2008 8/2/2008 N N N S S S S
4 7/27/2008 8/2/2008 N N N N N N N
5 7/27/2008 8/2/2008 N N N N N S S
1 8/3/2008 8/9/2008 N N N N N N N
2 8/3/2008 8/9/2008 N N N N N N N
3 8/3/2008 8/9/2008 S S N N N N N
4 8/3/2008 8/9/2008 N N N N N N N
5 8/3/2008 8/9/2008 S S S S N N N

Notice I am also showing absence days for Saturday and Sunday, which I think is
what you want.

 

[Michael Gramelspacher]

FROM [Employee Days2] AS e

In the crosstab should be

FROM [Employee Days] AS e