CREATE TABLE visits (
visit_id INT,
emp_id INT,
time_in DATETIME,
timeout DATETIME,
duration INT
);
INSERT INTO visits (visit_id, emp_id, time_in, timeout, duration)
VALUES
(15, 2, '2012-03-14 09:30:00', '2012-03-14 10:30:00', 60),
(16, 2, '2012-03-14 10:00:00', '2012-03-14 11:00:00', 60),
(18, 2, '2012-03-14 10:00:00', '2012-03-14 11:00:00', 60),
(25, 2, '2012-03-14 10:00:00', '2012-03-14 11:00:00', 60),
(30, 5, '2012-05-16 13:00:00', '2012-05-16 14:30:00', 90),
(33, 5, '2012-05-16 13:30:00', '2012-05-16 15:30:00', 120);
Schema (SQLite v3.39)
CREATE TABLE visits (
visit_id INT,
emp_id INT,
time_in DATETIME,
timeout DATETIME,
duration INT
);
INSERT INTO visits (visit_id, emp_id, time_in, timeout, duration)
VALUES
(15, 2, '2012-03-14 09:30:00', '2012-03-14 10:30:00', 60),
(16, 2, '2012-03-14 10:00:00', '2012-03-14 11:00:00', 60),
(18, 2, '2012-03-14 10:00:00', '2012-03-14 11:00:00', 60),
(25, 2, '2012-03-14 10:00:00', '2012-03-14 11:00:00', 60),
(30, 5, '2012-05-16 13:00:00', '2012-05-16 14:30:00', 90),
(33, 5, '2012-05-16 13:30:00', '2012-05-16 15:30:00', 120);
Schema (SQLite v3.39)
CREATE TABLE visits (
visit_id INT,
emp_id INT,
time_in DATETIME,
timeout DATETIME,
duration INT
);
INSERT INTO visits (visit_id, emp_id, time_in, timeout, duration)
VALUES
(15, 2, '2012-03-14 09:30:00', '2012-03-14 10:30:00', 60),
(16, 2, '2012-03-14 10:00:00', '2012-03-14 11:00:00', 60),
(18, 2, '2012-03-14 10:00:00', '2012-03-14 11:00:00', 60),
(25, 2, '2012-03-14 10:00:00', '2012-03-14 11:00:00', 60),
(30, 5, '2012-05-16 13:00:00', '2012-05-16 14:30:00', 90),
(33, 5, '2012-05-16 13:30:00', '2012-05-16 15:30:00', 120);
Query #1
select
emp_id,
time_in,
timeout,
duration,
visit_id,
rank () over (partition by emp_id,time_in order by timeout desc, visit_id asc) rank
from visits;
| emp_id | time_in | timeout | duration | visit_id | rank |
|---|---|---|---|---|---|
| 2 | 2012-03-14 09:30:00 | 2012-03-14 10:30:00 | 60 | 15 | 1 |
| 2 | 2012-03-14 10:00:00 | 2012-03-14 11:00:00 | 60 | 16 | 1 |
| 2 | 2012-03-14 10:00:00 | 2012-03-14 11:00:00 | 60 | 18 | 2 |
| 2 | 2012-03-14 10:00:00 | 2012-03-14 11:00:00 | 60 | 25 | 3 |
| 5 | 2012-05-16 13:00:00 | 2012-05-16 14:30:00 | 90 | 30 | 1 |
| 5 | 2012-05-16 13:30:00 | 2012-05-16 15:30:00 | 120 | 33 | 1 |
View on DB Fiddle
Ideally, I want to group all emp_id = 2 visits together and extract highest duration which we see is 60 but the first row has been separated from the others under emp_id = 2. Same for emp_id = 5, which would be 120 for duration but it’s saying 90 and 120. Is this where ‘gap and island’ come into play or is there an alternative?
Thanks