· 6 years ago · Jun 29, 2019, 08:34 PM
1+----+------------+--------+---------------------+---------------------+---------------------+
2| id | job_number | emp_id | created_at | assigned_at | completed_at |
3+----+------------+--------+---------------------+---------------------+---------------------+
4| 1 | 1 | 1 | 2019-06-28 01:00:00 | 2019-06-28 01:15:00 | 2019-06-28 02:00:00 |
5| 2 | 2 | 1 | 2019-06-28 01:30:00 | 2019-06-28 01:45:00 | 2019-06-28 02:30:00 |
6| 3 | 3 | 1 | 2019-06-28 02:00:00 | 2019-06-28 02:15:00 | 2019-06-28 03:00:00 |
7| 4 | 4 | 1 | 2019-06-28 05:00:00 | 2019-06-28 05:15:00 | 2019-06-28 06:00:00 |
8| 5 | 5 | 2 | 2019-06-28 01:00:00 | 2019-06-28 01:15:00 | 2019-06-28 02:00:00 |
9| 6 | 6 | 2 | 2019-06-28 01:30:00 | 2019-06-28 01:45:00 | 2019-06-28 02:30:00 |
10| 7 | 7 | 2 | 2019-06-28 03:00:00 | 2019-06-28 03:05:00 | 2019-06-28 03:30:00 |
11| 8 | 8 | 3 | 2019-06-28 01:00:00 | 2019-06-28 01:15:00 | 2019-06-28 02:00:00 |
12| 9 | 9 | 3 | 2019-06-28 02:00:00 | 2019-06-28 02:15:00 | 2019-06-28 03:00:00 |
13+----+------------+--------+---------------------+---------------------+---------------------+
14
15+------------+--------+---------------------+---------------------+---------------------+
16| job_number | emp_id | created_at | assigned_at | completed_at |
17+------------+--------+---------------------+---------------------+---------------------+
18| 1 | 1 | 2019-06-28 01:00:00 | 2019-06-28 01:15:00 | 2019-06-28 02:00:00 |
19| 2 | 1 | 2019-06-28 01:30:00 | 2019-06-28 01:45:00 | 2019-06-28 02:30:00 |
20| 3 | 1 | 2019-06-28 02:00:00 | 2019-06-28 02:15:00 | 2019-06-28 03:00:00 |
21| 5 | 2 | 2019-06-28 01:00:00 | 2019-06-28 01:15:00 | 2019-06-28 02:00:00 |
22| 6 | 2 | 2019-06-28 01:30:00 | 2019-06-28 01:45:00 | 2019-06-28 02:30:00 |
23+------------+--------+---------------------+---------------------+---------------------+
24
25-- create a new test schema
26create schema test;
27
28use test;
29
30-- create the test table
31create table if not exists test_jobs (
32 id int not null auto_increment primary key
33 , job_number int not null default 0
34 , emp_id int not null default 0
35 , created_at datetime not null default current_timestamp
36 , assigned_at datetime not null default current_timestamp
37 , completed_at datetime not null default current_timestamp
38) engine=innodb character set=utf8;
39
40-- seed test data
41insert into test_jobs
42(job_number, emp_id, created_at, assigned_at, completed_at)
43values
44(1, 1, '2019-06-28 01:00:00', '2019-06-28 01:15:00', '2019-06-28 02:00:00'),
45(2, 1, '2019-06-28 01:30:00', '2019-06-28 01:45:00', '2019-06-28 02:30:00'),
46(3, 1, '2019-06-28 02:00:00', '2019-06-28 02:15:00', '2019-06-28 03:00:00'),
47(4, 1, '2019-06-28 05:00:00', '2019-06-28 05:15:00', '2019-06-28 06:00:00'),
48(5, 2, '2019-06-28 01:00:00', '2019-06-28 01:15:00', '2019-06-28 02:00:00'),
49(6, 2, '2019-06-28 01:30:00', '2019-06-28 01:45:00', '2019-06-28 02:30:00'),
50(7, 2, '2019-06-28 03:00:00', '2019-06-28 03:05:00', '2019-06-28 03:30:00'),
51(8, 3, '2019-06-28 01:00:00', '2019-06-28 01:15:00', '2019-06-28 02:00:00'),
52(9, 3, '2019-06-28 02:00:00', '2019-06-28 02:15:00', '2019-06-28 03:00:00');
53
54-- make sure test data is properly seeded
55select * from test_jobs;
56
57-- Get the jobs are were started in between another job
58create temporary table test_jobs_1
59select
60 tj1.job_number
61 , tj1.emp_id
62 , tj1.created_at
63 , tj1.assigned_at
64 , tj1.completed_at
65from
66 test_jobs tj1 inner join test_jobs tj2
67 on tj1.emp_id = tj2.emp_id
68 where tj1.job_number <> tj2.job_number
69 and tj1.assigned_at between tj2.created_at and tj2.completed_at;
70
71-- make sure you get the right data
72select * from test_jobs_1;
73+------------+--------+---------------------+---------------------+---------------------+
74| job_number | emp_id | created_at | assigned_at | completed_at |
75+------------+--------+---------------------+---------------------+---------------------+
76| 2 | 1 | 2019-06-28 01:30:00 | 2019-06-28 01:45:00 | 2019-06-28 02:30:00 |
77| 3 | 1 | 2019-06-28 02:00:00 | 2019-06-28 02:15:00 | 2019-06-28 03:00:00 |
78| 6 | 2 | 2019-06-28 01:30:00 | 2019-06-28 01:45:00 | 2019-06-28 02:30:00 |
79+------------+--------+---------------------+---------------------+---------------------+
80
81-- create copy of this temporary table to get around pesky issue of not being able to reopen temporary table
82create temporary table temp_test_jobs select * from test_jobs_1;
83
84-- Now create another table which trims data to only employees who can have did more than
85-- one job simultaneously and the jobs that were not included in previous basically minus
86-- set of test_jobs_1 but only for the selected employees
87create temporary table test_jobs_2
88select
89 tj.job_number
90 , tj.emp_id
91 , tj.created_at
92 , tj.assigned_at
93 , tj.completed_at
94from test_jobs tj
95where tj.job_number not in (select distinct(job_number) from test_jobs_1)
96and tj.emp_id in (select distinct(emp_id) from temp_test_jobs);
97
98-- make sure you have correct test data
99select * from test_jobs_2;
100
101+------------+--------+---------------------+---------------------+---------------------+
102| job_number | emp_id | created_at | assigned_at | completed_at |
103+------------+--------+---------------------+---------------------+---------------------+
104| 1 | 1 | 2019-06-28 01:00:00 | 2019-06-28 01:15:00 | 2019-06-28 02:00:00 |
105| 4 | 1 | 2019-06-28 05:00:00 | 2019-06-28 05:15:00 | 2019-06-28 06:00:00 |
106| 5 | 2 | 2019-06-28 01:00:00 | 2019-06-28 01:15:00 | 2019-06-28 02:00:00 |
107| 7 | 2 | 2019-06-28 03:00:00 | 2019-06-28 03:05:00 | 2019-06-28 03:30:00 |
108+------------+--------+---------------------+---------------------+---------------------+
109
110-- Now out of that minus set called test_jobs_2 find out who were the root jobs
111-- or those that got failed to include in first one
112create temporary table test_jobs_3
113select
114 tj2.job_number
115 , tj2.emp_id
116 , tj2.created_at
117 , tj2.assigned_at
118 , tj2.completed_at
119from test_jobs_2 tj2 inner join test_jobs_1 tj1 on tj2.emp_id = tj1.emp_id
120where tj2.created_at < tj1.assigned_at and tj2.completed_at > tj1.assigned_at;
121
122-- make sure you got the correct data
123select * from test_jobs_3;
124+------------+--------+---------------------+---------------------+---------------------+
125| job_number | emp_id | created_at | assigned_at | completed_at |
126+------------+--------+---------------------+---------------------+---------------------+
127| 1 | 1 | 2019-06-28 01:00:00 | 2019-06-28 01:15:00 | 2019-06-28 02:00:00 |
128| 5 | 2 | 2019-06-28 01:00:00 | 2019-06-28 01:15:00 | 2019-06-28 02:00:00 |
129+------------+--------+---------------------+---------------------+---------------------+
130
131-- Final result
132select tj.job_number, tj.emp_id, tj.created_at, tj.assigned_at, tj.completed_at
133from (select * from test_jobs_1 union select * from test_jobs_3) tj order by tj.job_number;
134
135+------------+--------+---------------------+---------------------+---------------------+
136| job_number | emp_id | created_at | assigned_at | completed_at |
137+------------+--------+---------------------+---------------------+---------------------+
138| 1 | 1 | 2019-06-28 01:00:00 | 2019-06-28 01:15:00 | 2019-06-28 02:00:00 |
139| 2 | 1 | 2019-06-28 01:30:00 | 2019-06-28 01:45:00 | 2019-06-28 02:30:00 |
140| 3 | 1 | 2019-06-28 02:00:00 | 2019-06-28 02:15:00 | 2019-06-28 03:00:00 |
141| 5 | 2 | 2019-06-28 01:00:00 | 2019-06-28 01:15:00 | 2019-06-28 02:00:00 |
142| 6 | 2 | 2019-06-28 01:30:00 | 2019-06-28 01:45:00 | 2019-06-28 02:30:00 |
143+------------+--------+---------------------+---------------------+---------------------+