I'm writing a work schedule application in ASP, and I am using an Access 2000 DB with the following tables:
Code:
WorkSchedule
--------------------------
| ID | Date | EmployeeID |
| .
| .
| .
Employees
---------------------
| EmployeeID | Name |
| .
| .
| .
Status
----------------------------------------------------
| StatusID | DateFrom | DateTo | EmployeeID | Type |
| .
| .
| .
I need to retrieve a list of employees, which are not scheduled for work between the given dates
and which have no status (sick leave, maternity leave etc). The object is to see which employees are available for scheduling on the different dates.
The resulting table/recordset should contain one record per date per available employee (see example below).
Code:
AvailableEmployees
-------------------------
| Date | EmployeeID |
-------------------------
| 01/01/01 | 2 |
| 01/01/01 | 5 |
| 01/01/01 | 7 |
| 03/01/01 | 2 |
| .
| .
We first disregard the statuses. The query below returns a partially correct result. On the dates where only one employee is scheduled, only the
remaining employees are returned (correct). However, on the dates where 2 or more are scheduled, ALL employees are returned (wrong).
Code:
StartDate = "01/01/04" '(mm/dd//yy)
StopDate = "02/30/04"
Query = ""
Query = Query + "SELECT WorkSchedule.Date, Employees.EmployeeID "
Query = Query + "FROM WorkSchedule, Employees "
Query = Query + "WHERE WorkSchedule.Date BETWEEN #StartDate# AND #StopDate# "
Query = Query + "AND Employees.EmployeeID <> WorkSchedule.EmployeeID "
Query = Query + "GROUP BY WorkSchedule.Date, Employees.EmployeeID"
Including the status condition (see query below) has no affect on the result (I assume the condition is incorrect)
Code:
Query = ""
Query = Query + "SELECT WorkSchedule.Date, Employees.EmployeeID "
Query = Query + "FROM WorkSchedule, Employees "
Query = Query + "WHERE WorkSchedule.Date BETWEEN #StartDate# AND #StopDate# "
Query = Query + "AND NOT EXISTS (SELECT * FROM Status "
Query = Query + "WHERE Status.EmployeeID = WorkSchedule.EmployeeID "
Query = Query + "AND (Status.DateFrom <= WorkSchedule.Date AND Status.DateFrom >= WorkSchedule.Date)) "
Query = Query + "AND Employees.EmployeeID <> WorkSchedule.EmployeeID "
Query = Query + "GROUP BY WorkSchedule.Date, Employees.EmployeeID"
I guess I am overlooking something trivial, but I can't seem to get the query right. Suggestions, please?
Palsam
Dev Shed Forums Sponsor:
March 9th, 2004, 05:57 AM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
