How to classify times as In and Out for multiple timestamps in a work day in SQL Server

Question

I have a table that keeps the times of entrance and exit of employee taken from fingerprint machine.

Employees can have many fingerprints for the same entrance and/or exit within a period of time (ex. 3 minutes) let's call it A.

Employees do this in two cases if they want to make sure the machine got their fingerprints. And if there are many fingerprints machines in the company, one near the main gate the second near their offices.

Employees can exit and return many times a day as they may be requested to do so, within a period of time must have stayed at the company in order to be calculated as work time and to determine that the next fingerprint will be exit.(for example 20 minutes ) let's call it B

The problem:

In the process of determining the entrance and exit times out of the records inserted into the database there are two regards:

If the fingerprint is entrance we should select the minimum time within A and after the B has reached consider the next fingerprints as for exit, but with selecting the maximum time

This is for the whole days of the month.

Note that sometimes the work day starts from 08:00am and ends in the next day at 07:59am, let's call this C.

Example

emp_id      edate       etime
100     01/01/2015      08:00:00
100     01/01/2015      08:00:30
100     01/01/2015      08:00:58
100     01/01/2015      08:02:01
100     01/01/2015      10:00:00
100     01/01/2015      10:01:15
100     01/01/2015      10:01:50
100     01/01/2015      12:10:00
100     01/01/2015      12:10:50
100     01/01/2015      12:11:00
100     01/01/2015      13:50:10
100     01/01/2015      13:52:30
100     01/01/2015      13:52:31
100     02/01/2015      01:00:31
100     02/01/2015      01:01:31
100     02/01/2015      01:52:31
100     02/01/2015      04:59:31

I want to write a SQL Server query to bring the result shown as:

emp_id  edate           InTime          OutTime
100     01/01/2015      08:00:00        10:01:50        
100     01/01/2015      12:10:00        13:52:31
100     01/01/2015      01:00:31        01:52:31
100     01/01/2015      01:00:31        01:52:31
100     01/01/2015      04:59:31        null

and same for next day...

I can do it using loops and conditions in a datagrid of any UI language, but it takes a lot of time, especially when calculating this for many employees for a whole month or more.

Answer 1

First, I found a way to query the entrance and exits of employees by joining the table of times with itself and then choose the row that best fit to be one best entrance 'min()' and the second the exit max(), but then I realized that if I can use another better way which I will post it as an answer that I hope any one may need can take or use the idea of.

I noticed that each group of rows of my times have some thing in common if they are in the same hour but the differences of minutes less than (3 minutes).

I need to have the (max()) of the odd rows starting with row 1 to be my first come in.

And the (min()) of the even rows to be the exit of each previous odd row.

I selected the column of the times and added another calculated column to determine which of the times have minutes more than the other (20 minutes) to be the least time the employee can stay at office.

datepart(hour,histIn.etime) + 
(
  case when datepart(MINUTE,histIn.etime)>=20 
  then 1 else 0 end
) hr

with the help of DENSE_RANK() to number the rows with the order by my new calculated column. then I need to select each odd row as an InTime and and every even row will be my OutTime.

,case when RowIdx%2<>0 then min(InTime) end InTime
 ,case when (RowIdx+1)%2<>0 then max(InTime) end OutTime

Here I add another calculated column that group each pair of this couples together.

 (row_number() over (partition by emp_id,edate order by RowIdx asc)+1)/2 as badge

this one is @DhruvJoshi 's hand.

Then last select the couples and get them married.

select emp_id,edate,max(InTime)InTime,max(OutTime)OutTime,badge Counts

Here is the insert:

create table tbl (emp_id int, edate datetime, etime time(0));

insert into tbl values
(100,'01/01/2015','08:00:00'),
(100,'01/01/2015','08:00:30'),
(100,'01/01/2015','08:00:58'),
(100,'01/01/2015','08:02:01'),
(100,'01/01/2015','10:00:00'),
(100,'01/01/2015','10:01:15'),
(100,'01/01/2015','10:01:50'),
(100,'01/01/2015','10:10:50'),
(100,'01/01/2015','12:10:00'),
(100,'01/01/2015','12:10:50'),
(100,'01/01/2015','12:11:00'),
(100,'01/01/2015','13:50:10'),
(100,'01/01/2015','13:52:30'),
(100,'01/01/2015','13:52:31'),
(100,'01/01/2015','15:52:31'),
(100,'02/01/2015','01:00:31'),
(100,'02/01/2015','01:01:31'),
(100,'02/01/2015','01:52:31'),
(100,'02/01/2015','04:59:31'),
(100,'02/01/2015','08:59:31'),
(100,'02/01/2015','10:59:31'),
(200,'01/01/2015','08:10:00'),
(200,'01/01/2015','08:10:30'),
(200,'01/01/2015','08:10:58'),
(200,'01/01/2015','08:12:01'),
(200,'01/01/2015','10:05:00'),
(200,'01/01/2015','10:05:15'),
(200,'01/01/2015','10:05:50'),
(200,'01/01/2015','10:15:50'),
(200,'01/01/2015','12:15:00'),
(200,'01/01/2015','12:10:50'),
(200,'01/01/2015','12:11:00'),
(200,'01/01/2015','14:50:10'),
(200,'01/01/2015','14:52:30'),
(200,'01/01/2015','14:52:31'),
(200,'02/01/2015','04:00:31'),
(200,'02/01/2015','01:01:31'),
(200,'02/01/2015','01:52:31'),
(200,'02/01/2015','04:59:31'),
(200,'02/01/2015','08:59:31'),
(200,'02/01/2015','10:59:31')

and here is the select:

select emp_id,edate,max(InTime)InTime,max(OutTime)OutTime,badge Counts
from (
       select emp_id,edate
       ,case when RowIdx%2<>0 then min(InTime) end InTime
       ,case when (RowIdx+1)%2<>0 then max(InTime) end OutTime
       ,RowIdx
       ,(row_number() over (partition by emp_id,edate order by RowIdx asc)+1)/2 as badge
       from
       (
          select emp_id,edate,etime InTime
          ,datepart(hour,histIn.etime)+(case when datepart(MINUTE,histIn.etime)>=20 then 1 else 0 end)hr
          ,DENSE_RANK() over(partition by HistIn.emp_id,HistIn.edate
          order by emp_id,edate
          ,datepart(hour,histIn.etime)+(case when datepart(MINUTE,histIn.etime)>=20 then 1 else 0 end)
          )RowIdx 
          from tbl HistIn 
       )aa
       group by emp_id,edate,RowIdx
    )bb
group by emp_id,edate,badge
order by emp_id,edate,badge

They are all here with this link: http://sqlfiddle.com/#!6/b0aa3/1

Thanks every body.

Answer 2

Please try the below query, if your SQL version is 2012 or above:

    select emp_id,[date],max(intime) as InTime, max(outtime) as OutTime
    from
    (
    select 
        emp_id,
        cast(combdt as date) as [date],
        case 
        when row_number() over (partition by emp_id,cast(combdt as date) order by etime1 asc)%2 =1
        then etime1 else null 
        end as intime,
        case 
        when row_number() over (partition by emp_id,cast(combdt as date) order by etime1 asc)%2 =0
        then etime2 else null
        end as outtime,
        (row_number() over (partition by emp_id,cast(combdt as date) order by etime1 asc)+1)/2 as badge
    from 
    (
    --since min of entry is taken and max of exit is taken 
    -- I'm apply the comparision between min and max to determine the logic of B
    select * from
        (
        select 
            t.emp_id,
            t.combdt,
            min(t.combdt) as etime1,
            t.etime2, 
            case 
                when DATEDIFF(mi,ISNULL(lag(etime2) over(partition by t.emp_id,cast(combdt as date) order by etime2),0),min(t.combdt)) >20
                then 1 
                else 0
            end as flag 
        from
            (
                select 
                    t1.emp_id,
                    t1.combdt,
                    max(t2.combdt) as etime2,
                    max(t2.r) as r2 
                from
                    (
                        select 
                            *,
                            edate+etime as combdt,
                            row_number() over(partition by emp_id, edate order by etime asc) as r
                        from tbl 
                    )   t1
                left join 
                    (
                        select 
                            *,
                            edate+etime as combdt,
                            row_number() over(partition by emp_id, edate order by etime asc) as r
                        from 
                        tbl 
                    )   t2
                    on t1.emp_id=t2.emp_id and 
                        dateadd(mi,3,t1.combdt)>t2.combdt -- this is where we put A
                    group by t1.emp_id, t1.combdt,t1.r
            )t
        group by t.emp_id,t.combdt,t.etime2
        )t where flag =1
    )t
    )t
    group by emp_id,[date],badge

Output of this long query is :

emp_id          date       InTime       OutTime
100 2015-01-01  2015-01-01 08:00:00.000 2015-01-01 10:01:50.000
100 2015-01-01  2015-01-01 12:10:00.000 2015-01-01 13:52:31.000
100 2015-02-01  2015-02-01 01:00:31.000 2015-02-01 01:52:31.000
100 2015-02-01  2015-02-01 04:59:31.000 NULL

SQl fiddle link for demo is here: http://sqlfiddle.com/#!6/e8762/4

P.S.: Note that the above question is too long as it consists of multiple small problems like A and B, date overlap constraints and calculating in-out from consecutive entries and does not provide SQL version.

If you are using SQL server version which does not support lag/lead function consider using a JOIN. There are numerous SO examples which will show you how to do it.