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.

If the first shift starts at `8:30am`, then why are you reporting a range starting at `7:30am` in your expected result set? — Tim Biegeleisen, Jun 29 '17 at 01:20
@TimBiegeleisen because some employee will request to come in early, sometime will come in at 7:00am for morning shift. This is the problem I cant get the expected result — Bubble Bub, Jun 29 '17 at 01:41
@vkp no, there is only time in, time out, userid and some other columns — Bubble Bub, Jun 29 '17 at 01:47
@DerickLoo the ideal way of dealing this kind of early clock-in and out would be their daily roster... the roster would say clock in from 7-8am and clock out on a specific period, this way you'd be able to get the min and max correctly based from the roster — RoMEoMusTDiE, Jun 29 '17 at 01:49
there would a lot of edge cases here based on users logging in early and logging out late in both the shifts. — Vamsi Prabhala, Jun 29 '17 at 01:54
that's true @vkp, that's why it needs to have a roster table — RoMEoMusTDiE, Jun 29 '17 at 01:59
that's the only way you can validate the closest time they clock in and out on that day.. even if your current issue can be resolved but in the long run there will be more problems like people clocking in outside their roster — RoMEoMusTDiE, Jun 29 '17 at 02:08

score 0 · Accepted Answer · answered Jun 29 '17 at 02:06

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

This problem looks like the one that Itzik Ben-Gan explained in his article [Packing Intervals](http://blogs.solidq.com/en/sqlserver/packing-intervals/) including the last section of the article "Ignoring Gaps of Up To a Certain Length". — Vladimir Baranov, Jun 29 '17 at 03:07

score 0 · Answer 2 · answered Jun 29 '17 at 05:46

Maybe this can help and give you an idea as well.

Source:

            DECLARE @T TABLE
                (
                  TimeIn DATETIME ,
                  [TimeOut] DATETIME ,
                  [User] VARCHAR(50)
                )

            INSERT  INTO @T
                    ( TimeIn, TimeOut, [User] )
            VALUES  ( '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' );

Query:

            IF ( OBJECT_ID('tempdb..#tmpTbl') IS NOT NULL )
                BEGIN
                    DROP TABLE #tmpTbl
                END
            CREATE TABLE #tmpTbl
                (
                  id INT IDENTITY(1, 1)
                         PRIMARY KEY ,
                  TimeIn DATETIME ,
                  [TimeOut] DATETIME ,
                  [User] VARCHAR(50) ,
                  flag INT ,
                  [lag] INT
                )

            INSERT  INTO #tmpTbl
                    ( TimeIn ,
                      TimeOut ,
                      [User] ,
                      flag ,
                      lag
                    )
                    SELECT  TimeIn ,
                            TimeOut ,
                            [User] ,
                            flag ,
                            LAG(flag) OVER ( ORDER BY ctr ) LAG
                    FROM    ( SELECT    * ,
                                        IIF(CAST(TimeIn AS TIME) BETWEEN '7:00' AND '8:30', 1, IIF(CAST(TimeIn AS TIME) >= '20:30', 1, 0)) flag ,
                                        ROW_NUMBER() OVER ( ORDER BY ( SELECT
                                                                          0
                                                                     ) ) ctr
                              FROM      @T
                            ) TT 

            DECLARE @Lastid INT
            SELECT TOP 1
                    @Lastid = id
            FROM    #tmpTbl
            ORDER BY id DESC

            UPDATE  #tmpTbl
            SET     flag = 2
            WHERE   id = @Lastid

            UPDATE  #tmpTbl
            SET     flag = 0
            WHERE   lag = 1

            UPDATE  #tmpTbl
            SET     flag = 2
            WHERE   id IN ( SELECT  previd
                            FROM    ( SELECT    flag ,
                                                LAG(flag) OVER ( ORDER BY ID ) lag ,
                                                LAG(id) OVER ( ORDER BY ID ) previd
                                      FROM      #tmpTbl
                                    ) T
                            WHERE   T.flag = 1
                                    AND T.lag = 0 );
            WITH    CTE
                      AS ( SELECT   * ,
                                    ROW_NUMBER() OVER ( ORDER BY id ) ctr
                           FROM     #tmpTbl
                           WHERE    flag = 1
                         ),
                    CTE1
                      AS ( SELECT   * ,
                                    ROW_NUMBER() OVER ( ORDER BY id ) ctr
                           FROM     #tmpTbl
                           WHERE    flag = 2
                         ),
                    CTE2
                      AS ( SELECT   cte.TimeIn ,
                                    cte1.TimeOut ,
                                    cte.[user]
                           FROM     cte
                                    JOIN cte1 ON CTE1.ctr = CTE.ctr
                         )
                SELECT  *
                FROM    CTE2

Result:

        TimeIn                  TimeOut                 user
        ----------------------- ----------------------- -------
        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

        (3 row(s) affected)

select time in and time out for different shift

2 Answers2