← К задачам SQL
← Предыдущая Следующая →

312. Часы вне назначения

Expert 0 решили интервалы

Найдите дни табеля, для которых нет назначения этого сотрудника на тот же проект, покрывающего дату. Границы from_date и to_date включительны.

Сроки самого проекта — не то же самое, что личное назначение: человек мог выйти на проект позже официального старта.

Используйте таблицы worklog, assignments, employees и projects.

Результат: employee, project, work_date, hours. Сортировка по сотруднику, затем по дате.

Схема базы команды

Схема БД состоит из пяти отношений:

departments (id, name)

employees (id, name, department_id, manager_id, hired_on, salary)

projects (id, name, start_date, end_date)

assignments (employee_id, project_id, role, from_date, to_date)

worklog (id, employee_id, work_date, hours, project_id)

employees.manager_id ссылается на ту же таблицу: дерево подчинения. У руководителя компании manager_id равен NULL. Назначение на проект — отрезок дат включительно с обеих сторон. Один человек может быть на нескольких проектах сразу, если интервалы пересекаются. worklog — фактические рабочие дни; пропуск даты разрывает серию.

Схема данных: departments, employees, projects, assignments, worklog departments Column Name Data Type Nulls id int name text employees Column Name Data Type Nulls id int name text department_id int manager_id int YES hired_on date salary numeric projects Column Name Data Type Nulls id int name text start_date date end_date date assignments Column Name Data Type Nulls employee_id int project_id int role text from_date date to_date date worklog Column Name Data Type Nulls id int employee_id int work_date date hours int project_id int

Данные таблиц

Первые 5 строк каждой таблицы

departments 4

id name
1 Управление
2 Разработка
3 Аналитика
4 Поддержка

employees 13

id name department_id manager_id hired_on salary
1 Ирина Белова 1 None 2019-01-10 280000
2 Павел Чернов 2 1 2020-03-01 210000
3 Елена Юрченко 3 1 2020-05-20 190000
4 Анна Ким 2 2 2021-06-15 220000
5 Олег Сафин 2 2 2021-09-01 150000

projects 6

id name start_date end_date
1 Альфа 2024-01-01 2024-06-30
2 Бета 2024-03-01 2024-12-31
3 Гамма 2024-07-01 2024-09-30
4 Дельта 2024-02-01 2024-04-30
5 Омега 2024-08-01 2024-11-30

assignments 17

employee_id project_id role from_date to_date
2 1 lead 2024-01-01 2024-06-30
3 3 lead 2024-07-01 2024-09-30
4 1 dev 2024-01-01 2024-06-30
4 2 dev 2024-03-01 2024-12-31
4 6 dev 2024-03-15 2024-04-30

worklog 30

id employee_id work_date hours project_id
1 2 2024-03-04 8 1
2 2 2024-03-05 8 1
3 2 2024-03-06 8 1
4 2 2024-03-09 8 1
5 4 2024-03-04 8 1

Данные таблиц

Первые 5 строк. Полная схема — по кнопке выше.

departments 4

id name
1 Управление
2 Разработка
3 Аналитика
4 Поддержка

employees 13

id name department_id manager_id hired_on salary
1 Ирина Белова 1 None 2019-01-10 280000
2 Павел Чернов 2 1 2020-03-01 210000
3 Елена Юрченко 3 1 2020-05-20 190000
4 Анна Ким 2 2 2021-06-15 220000
5 Олег Сафин 2 2 2021-09-01 150000

projects 6

id name start_date end_date
1 Альфа 2024-01-01 2024-06-30
2 Бета 2024-03-01 2024-12-31
3 Гамма 2024-07-01 2024-09-30
4 Дельта 2024-02-01 2024-04-30
5 Омега 2024-08-01 2024-11-30

assignments 17

employee_id project_id role from_date to_date
2 1 lead 2024-01-01 2024-06-30
3 3 lead 2024-07-01 2024-09-30
4 1 dev 2024-01-01 2024-06-30
4 2 dev 2024-03-01 2024-12-31
4 6 dev 2024-03-15 2024-04-30

worklog 30

id employee_id work_date hours project_id
1 2 2024-03-04 8 1
2 2 2024-03-05 8 1
3 2 2024-03-06 8 1
4 2 2024-03-09 8 1
5 4 2024-03-04 8 1

Комментарии (0)

Зарегистрируйтесь или войдите, чтобы оставить комментарий

Пока нет комментариев. Будьте первым!