select time in and time out for different shift

Question

I need to find out the clock in and clock out for each employee each day. It has 2 shift, first shift is start from 0830 - 2030, second shift is from 2030 - 0830(next day). Below is the sample record I have,

 Time In                    Time Out             User
2017-06-16 07:30:00.000 2017-06-16 08:30:00.000 I0495
2017-06-16 08:30:00.000 2017-06-16 12:30:00.000 I0495
2017-06-16 13:00:00.000 2017-06-16 15:30:00.000 I0495
2017-06-16 15:30:00.000 2017-06-16 19:00:00.000 I0495

2017-06-16 20:30:00.000 2017-06-16 21:30:00.000 I0603
2017-06-16 21:30:00.000 2017-06-17 00:00:00.000 I0603
2017-06-17 00:00:00.000 2017-06-17 00:30:00.000 I0603
2017-06-17 01:30:00.000 2017-06-17 04:30:00.000 I0603
2017-06-17 05:30:00.000 2017-06-17 08:30:00.000 I0603

2017-06-17 07:30:00.000 2017-06-17 08:30:00.000 I0495
2017-06-17 08:30:00.000 2017-06-17 12:30:00.000 I0495
2017-06-17 13:00:00.000 2017-06-17 15:30:00.000 I0495
2017-06-17 15:30:00.000 2017-06-17 19:00:00.000 I0495

I try the query as below

Select min(tbl.timein), max(tbl.timeout), tbl.user form
(
Select timein, timeout, user from tbl where timein >= '2017-06-16 
07:00:00' and timeout <= '2017-06-16 20:30:00' 
union all
Select timein, timeout, user from tbl where timein >= '2017-06-16 
20:60:00' and timeout <= '2017-06-17 08:30:00'
)tbl
group by tb.user

The results i get are below which is not what as expected

2017-06-16 07:30:00.000 2017-06-17 08:30:00.000 I0495
2017-06-16 20:30:00.000 2017-06-17 08:30:00.000 I0603
2017-06-17 07:30:00.000 2017-06-18 08:30:00.000 I0495   

The expected results are

2017-06-16 07:30:00.000 2017-06-16 19:00:00.000 I0495
2017-06-16 20:30:00.000 2017-06-17 08:30:00.000 I0603
2017-06-17 07:30:00.000 2017-06-17 19:00:00.000 I0495

Any idea how I can do this. Thanks.

Answer 1

A reasonable way to solve this knowing the maximum break a user can take between a timeout and the next timein. Assuming it is <= 2 hours, you can do this by getting the difference of previous timeout with the current row's timein per user. Then assigning groups per day based on this using a running sum.

select usr,timein,timeout
,sum(case when diff <=120 then 0 else 1 end) over(partition by usr order by timein) as grp
from (select t.*
      ,datediff(minute,lag(timeout) over(partition by usr order by timein),timein) as diff
      from tbl t
     ) t

After the groups are assigned, you just need the min timein and max timeout per user and group.

select usr,min(timein),max(timeout)
from (select usr,timein,timeout
      ,sum(case when diff <=120 then 0 else 1 end) over(partition by usr order by timein) as grp
      from (select t.*
           ,datediff(minute,lag(timeout) over(partition by usr order by timein),timein) as diff
            from tbl t
           ) t
     ) t
group by usr,grp