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.