SQLTeam.com | Weblogs | Forums

Calculate Total Time Out


#1

I have employee attendance Table and recording his time In and Out in one table like shown below

EmployeeCode TrxTime TrxType
22 10.16 IN
22 11.17 OU
22 11.34 IN
22 13.02 OU
22 13.14 IN
22 15.41 OU
22 15.49 IN
22 16.25 IN
22 16.43 OU
22 16.54 IN
22 17.3 OU

I want to calculate total time he stay outside which means (Time In - Time Out ) but Time IN or OUT can be duplicate as see above in bold

I want Result something like as shown below

EmployeeCode TrxTime TrxType Total Time Stay outside
22 10.16 IN
22 11.17 OU
22 11.34 IN 17
22 13.02 OU
22 13.14 IN 12
22 15.41 OU
22 15.49 IN 8
22 16.25 IN
22 16.43 OU
22 16.54 IN 11
22 17.3 OU

Total 48 Minuets


#2

This might get you started:

with cte(employeecode,trxtime,trxtype,rn)
  as (select employeecode
            ,cast(replace(trxtime,'.',':') as time) trxtime
            ,trxtype
            ,row_number()
                 over(partition by employeecode
                      order by trxtime
                     )
             as rn
        from employee_attendance
     )
select a.employeecode
      ,a.trxtime
      ,a.trxtype
      ,datediff(minute,b.trxtime,a.trxtime) as time_out
  from cte as a
       left outer join cte as b
                    on b.employeecode=a.employeecode
                   and b.rn=a.rn-1
                   and b.trxtype='OU'
                   and a.trxtype='IN'
;