Employee Availblility

[Guest]

I have orders with a start date, duration and end date

OrderID Orderdate Length Enddate
1 2/01/05 20 22/01/05
2 14/01/05 10 24/01/05
3 5/02/05 10 15/02/05
4 10/02/05 15 25/02/05
5 9/03/05 19 28/03/05

now, the employees have another table, which stores their availablity

Employeeid Startdate Duration Enddate
1 1/01/05 15 16/01/05
1 18/01/05 10 28/01/05
1 2/03/05 25 27/03/05
2 5/01/05 14 19/01/05
2 2/02/05 26 28/02/05
2 10/03/05 12 22/03/05
3 3/01/05 20 23/01/05
3 28/01/05 18 15/02/05
3 25/02/05 20 17/03/05
4 1/03/05 25 26/03/05
4 1/04/05 19 20/04/05
5 1/01/05 25 26/01/05
5 1/02/05 24 25/02/05
5 1/03/05 26 27/03/05
5 1/04/05 23 24/04/05

i have created a query which retrieves all employees which are available
after the orderdate and have a higher duration then the length

but

when i tried to restrict this by making sure the orderdate is before the
employee enddate, the query did not retrieve anything and even if this did,
the results would not be correct. i can seem to think how i can achieve this.

have you any ideas on how i can retrieve the best result, i have displayed
my data above. thanks Nikos, you are a great help, thank you

 

[JohnFol]

I think you have too many fields. Either Length OR EndDate is calculated and
therefore should not be stored.

The SQL would probably be something like

"Where Order.OrderDate>=Employee.StartDate and Order.EndDate <=
Emplyee.EndDate"

 

[Guest]

Thanks for the response,

I have made a mistake, in the order table, there isnt an enddate. also when
i try this query, it retrieves all employees that are available but do not
have enough days available. when i try to insert query with the duration,
nothing is returned

i have amended the details below, hope you can help ?

thanks for the reply

 

[Michel Walsh]

Hi,


There is no overlap if orderDate start after the ending employee
assignation, or if the orderDate end before the employee starting
assignation,

NO overlapping if:
OrderDate.Start > Assignments.Ending OR OrderDate.End <
Assignments.Starting

there is overlapping in the negation. Using De Morgan:

OrderDate.Start <= Assginments.Ending AND OrderDate.End >=
Assignments.Starting


So, employee WITH an overlapping (so employee NOT available):

SELECT A.EmpID
FROM Assignments As A INNER JOIN Orders As O
ON O.Start <= A.Ending AND O.End >= A.Starting


Employee NOT IN that list should be those you can consider for OrderID "x"


SELECT x.OrderID, e.EmpID
FROM Orders As x, Employees As e
WHERE e.EmpID NOT IN( SELECT a.empID
FROM Assignments As a INNER JOIN Orders As o
ON o.Start <= a.ending AND o.end >= a.starting
WHERE o.OrderID <> x.OrderID )

ORDER BY x.OrderID, e.EmpID


note that we added the condition that when we look for overlap, we exclude
the actual Order (x.OrderID) from those orders (o) we consider.




Hoping it may help,
Vanderghast, Access MVP