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

i have table keeps times of entrance , exit of employee taken fingerprint machine.

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

employees in 2 cases if want make sure machine got fingerprints. , if there many fingerprints machines in company, 1 near main gate second near offices.

employees can exit , return many times day may requested so, within period of time must have stayed @ company in order calculated work time , determine next fingerprint exit.(for example 20 minutes ) let's call b

the problem:

in process of determining entrance , exit times out of records inserted database there 2 regards:

if fingerprint entrance should select minimum time within , after b has reached consider next fingerprints exit, selecting maximum time

this whole days of month.

note work day starts 08:00am , ends in next day @ 07:59am, let's call 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 write sql server query bring 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 next day...

i can using loops , conditions in datagrid of ui language, takes lot of time, when calculating many employees whole month or more.

please try below query, if sql version 2012 or above:

    select emp_id,[date],max(intime) intime, max(outtime) outtime         (     select          emp_id,         cast(combdt date) [date],         case          when row_number() on (partition emp_id,cast(combdt date) order etime1 asc)%2 =1         etime1 else null          end intime,         case          when row_number() on (partition emp_id,cast(combdt date) order etime1 asc)%2 =0         etime2 else null         end outtime,         (row_number() on (partition emp_id,cast(combdt date) order etime1 asc)+1)/2 badge          (     --since min of entry taken , max of exit taken      -- i'm apply comparision between min , max determine logic of b     select *         (         select              t.emp_id,             t.combdt,             min(t.combdt) etime1,             t.etime2,              case                  when datediff(mi,isnull(lag(etime2) over(partition t.emp_id,cast(combdt date) order etime2),0),min(t.combdt)) >20                 1                  else 0             end flag                      (                 select                      t1.emp_id,                     t1.combdt,                     max(t2.combdt) etime2,                     max(t2.r) r2                                      (                         select                              *,                             edate+etime combdt,                             row_number() over(partition emp_id, edate order etime asc) r                         tbl                      )   t1                 left join                      (                         select                              *,                             edate+etime combdt,                             row_number() over(partition emp_id, edate order etime asc) r                                                  tbl                      )   t2                     on t1.emp_id=t2.emp_id ,                          dateadd(mi,3,t1.combdt)>t2.combdt -- put                     group t1.emp_id, t1.combdt,t1.r             )t         group t.emp_id,t.combdt,t.etime2         )t flag =1     )t     )t     group emp_id,[date],badge 

output of long query :

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 demo here: http://sqlfiddle.com/#!6/e8762/4

p.s.: note above question long consists of multiple small problems , b, date overlap constraints , calculating in-out consecutive entries , not provide sql version.

if using sql server version not support lag/lead function consider using join. there numerous examples show how it.