Employee available for task between a date range

Question

I have been working on a employee work management project and I am a little stuck. I have 3 tables:

1:  employees

    empid,       empFirst     empLast
    1            jon          smith
    2            mark         road
    3            jane         hall

2:  holiday

    id        employee id      datestart       dateend
    1         2                2015-08-07      2015-08-12
    2         3                2015-07-4       2015-07-11
    3         2                2015-07-20      2015-07-24

3: Task Assigned

   id      taskid        assignedTo(userid)        startTask      endTask
   1       1               1                     2015-07-10      2015-07-14
   2       2               2                     2015-07-29      2015-07-29
   3       2               3                     2015-07-18      2015-07-30
   4       3               2                     2015-08-30      2015-09-03
   5       4               2                     2015-09-10      2015-09-03

I'm not sure how to go about querying the tables to see who is available for a task in a date range (multiple user assigned to the same task). I have a query which I would here:

so if you take the holiday table out if the equation and just run the query below

SELECT employees.empId, employees.empFirst, employees.empLast
FROM employees 
LEFT JOIN taskassigned 
ON employees.empId = taskassigned.assignedTo
WHERE taskassigned.assignedTo IS NULL or  
 not (taskassigned.startTask  BETWEEN '2015-07-29 14:30:00' AND '2015-07-29 18:30:00' 
   or taskassigned.endTask  BETWEEN '2015-07-29 14:30:00' AND '2015-07-29 18:30:00')

the result I get is:

empId  empFirst empLast
1      jon      smith   (he is available)
2      mark     road    
2      mark     road

As you can see Mark is not available on this date (in the task table).

I would like the query the holiday table first to see if they are on holiday then the task table to see if they already have a task on the date range then the result to show me how is available for the task.

Answer 1

I can't test this at the moment, but try:

SELECT employees.empId, employees.empFirst, employees.empLast
FROM employees 
LEFT JOIN taskassigned 
ON employees.empId = taskassigned.assignedTo
LEFT JOIN holiday
ON employees.empId = holiday.employeeId
WHERE (
    taskassigned.assignedTo IS NULL
    OR (
        '2015-07-29 14:30:00'  NOT BETWEEN taskassigned.startTask AND taskassigned.endTask
        AND '2015-07-29 18:30:00'  NOT BETWEEN taskassigned.startTask AND taskassigned.endTask
    )
)
AND (
    holiday.employeeId IS NULL
    OR (
        '2015-07-29 14:30:00'  NOT BETWEEN holiday.dateStart AND holiday.dateEnd
        AND '2015-07-29 18:30:00'  NOT BETWEEN holiday.dateStart AND holiday.dateEnd
    )
)

This would check to see if the specified start date doesn't fall inbetween the assigned task's start or end date, and if the specified end date doesn't fall inbetween the assigned task's start or end date, and then do the same for holidays.