This problem falls into the realm of what I believe is generally called "island and gap analysis".
If this is a real-world system you're working on (not a study project), be aware that this is a hard problem even for experts, let alone someone who is having to ask where to begin.
Your bosses should be warned that there is no practicable solution which is fully automatic (only a partial solution that deals with unambiguous cases, and then flags unusual/exceptional cases for human review).
Also, if the EMP column refers to an individual Employee Id, be aware that in the example you've given, Employee 3 is shown as doing two shifts back-to-back over 1/11/2017 and 2/11/2017.
If that is correct, then in all likelihood you will not be able to solve this problem at all, because the implication of some workers doing a 16-hour shift is that you can't rely on the rest period being the larger part of the day (and that frustrates the identification of the "islands and gaps").
Here's an example of how you'd begin to tackle the problem in SQL, assuming there was a table called "punch_log" with two columns "emp" and "logdatetime".
WITH
step1 AS
(
SELECT
emp
,ROW_NUMBER() OVER (PARTITION BY emp ORDER BY logdatetime) AS employee_punch_num
,logdatetime
,LEAD(logdatetime) OVER (PARTITION BY emp ORDER BY logdatetime) AS next_logdatetime
FROM
punch_log
)
,step2 AS
(
SELECT
*
,CAST(DATEDIFF(MINUTE, logdatetime, next_logdatetime) AS FLOAT)/60 AS length_between_punches
FROM
step1
)
,step3 AS
(
SELECT
*
,IIF(length_between_punches IS NULL, NULL, IIF(length_between_punches > 12 , 'REST', 'WORK')) AS work_rest_ind
FROM
step2
)
,step4 AS
(
SELECT
*
,employee_punch_num - ROW_NUMBER() OVER (PARTITION BY emp, work_rest_ind ORDER BY emp, employee_punch_num) AS work_rest_punch_num
FROM
step3
)
,step5 AS
(
SELECT
*
,DENSE_RANK() OVER (PARTITION BY emp, work_rest_ind ORDER BY work_rest_punch_num) AS work_rest_period_index
FROM
step4
)
,step6 AS
(
SELECT
*
,MIN(logdatetime) OVER (PARTITION BY emp, work_rest_ind, work_rest_period_index) AS work_rest_period_begin
,MAX(next_logdatetime) OVER (PARTITION BY emp, work_rest_ind, work_rest_period_index) AS work_rest_period_end
FROM
step5
)
,step7 AS
(
SELECT
*
,ROUND(CAST(DATEDIFF(MINUTE, work_rest_period_begin, work_rest_period_end) AS FLOAT)/60,2) AS period_length_hrs
,ROW_NUMBER() OVER (PARTITION BY emp, work_rest_ind, work_rest_period_begin, work_rest_period_end ORDER BY (SELECT NULL)) AS duplicate_row_num
FROM
step6
)
SELECT
emp
,work_rest_ind
,CAST(FORMAT(work_rest_period_begin, 'yyyy-MM-dd hh:mm') AS NCHAR(16)) AS period_begin
,CAST(FORMAT(work_rest_period_end, 'yyyy-MM-dd hh:mm') AS NCHAR(16)) AS period_end
,period_length_hrs
FROM
step7
WHERE
duplicate_row_num = 1
ORDER BY
emp
,work_rest_period_begin
From your data, that produces the following results:
emp work_rest_ind period_begin period_end period_length_hrs
----------- ------------- ---------------- ---------------- ----------------------
3 WORK 2017-11-01 02:00 2017-11-02 06:10 16.17
3 REST 2017-11-02 06:10 2017-11-02 10:01 15.85
3 WORK 2017-11-02 10:01 2017-11-03 06:10 8.15
3 REST 2017-11-03 06:10 2017-11-05 06:00 47.83
3 WORK 2017-11-05 06:00 2017-11-05 02:01 8.02
3 REST 2017-11-05 02:01 2017-11-06 02:01 24
3 WORK 2017-11-06 02:01 2017-11-06 10:01 8
3 NULL 2017-11-06 10:01 NULL NULL
Submit an article or tip