Select empty work hours of task

[ayamnash]

I have a staff of employees working hours from 8:0 to 16:00 Each employee is given tasks, each task has a specific duration of one or two hours ..., and the employee may be given more than one task per day. I want to know which hours of the employees spend it without task
As below

 

[jdraw]

What exactly have you tried? Can you show us your work?
Sounds like homework.

 

[ayamnash]

This is not my homework. It is a new database that I want to design, but it is the first time I was faced with such a problem, and I did not find a solution to it and how to create its query.i have tried unmatched query but i failed

 

[jdraw]

Can you show us your
- table(s) design(s)
-unmatched query?
Have you worked out the logic of the requirement in simple English?

eg: So in general terms:
These are the possible work hours in a day; these are the hours when employeeA is tasked, so
total hours -tasked hours for employee = idle hours for employee

HoursAvailable unmatched with HoursTasked

 

[ayamnash]

my question about one table. Why do you want me to attach my database? I dont want total hours. This is easy . I want to show detail idle hours for employee as shown in the attached picture?

 

[jdraw]

I was trying to help. Just trying to see what you are actually working with. Are you saying you don't have a table(s) and can't post the design? This link may help with concept of times/overlap/occupied.

 

[ayamnash]

No .
i have tables .
But there is no need to attach my tables and my question about one table
If you want help me thats right
If not l have no any problems

 

[CJ_London]

if this is an application for the 'real world', you need more tables

1. table to show start and end times of the working day and/or table to show contracted hours for each employee
2. table to record employee absences from the shop floor (holiday/sickness/training etc)
3. table to record what days are not working days (bank holidays/shutdowns etc)

not to mention employees should be in a separate table and this table should have a foreign key back to that table

Your requirement is complex and you appear to want a complete solution, but if you don't want to help us to help you, we can't do that

So best I can suggest is you investigate using subqueries or perhaps domain functions