How to get the time spent inside and outside in SQL

Question

I have a simple table which stores employees clock-ins and clock-outs throughout the day:

╔══════════╦══════════════════╦════════════╗
║ Employee ║  PunchDateTime   ║ ActionType ║
╠══════════╬══════════════════╬════════════╣
║ John     ║ 2014/03/26 08:00 ║ IN         ║
║ Mark     ║ 2014/03/26 08:12 ║ IN         ║
║ John     ║ 2014/03/26 08:50 ║ OUT        ║
║ John     ║ 2014/03/26 09:29 ║ IN         ║
║ Mark     ║ 2014/03/26 10:35 ║ OUT        ║
║ John     ║ 2014/03/26 10:55 ║ OUT        ║
║ Mark     ║ 2014/03/26 11:42 ║ IN         ║
║ John     ║ 2014/03/26 12:38 ║ IN         ║
║ John     ║ 2014/03/26 16:21 ║ OUT        ║
║ Mark     ║ 2014/03/26 16:49 ║ OUT        ║
╚══════════╩══════════════════╩════════════╝

I want build a query that calculates time spent in and out. The end result should look like this:

╔══════════╦══════════════════╦════════════╦════════╦═════════╗
║ Employee ║  PunchDateTime   ║ ActionType ║ TimeIn ║ TimeOut ║
╠══════════╬══════════════════╬════════════╬════════╬═════════╣
║ John     ║ 2014/03/26 08:00 ║ IN         ║ -      ║ -       ║
║ Mark     ║ 2014/03/26 08:12 ║ IN         ║ -      ║ -       ║
║ John     ║ 2014/03/26 08:50 ║ OUT        ║ 00:40  ║ -       ║
║ John     ║ 2014/03/26 09:29 ║ IN         ║ -      ║ 00:39   ║
║ Mark     ║ 2014/03/26 10:35 ║ OUT        ║ 02:23  ║ -       ║
║ John     ║ 2014/03/26 10:55 ║ OUT        ║ 01:26  ║ -       ║
║ Mark     ║ 2014/03/26 11:42 ║ IN         ║ -      ║ 01:07   ║
║ John     ║ 2014/03/26 12:05 ║ IN         ║ -      ║ 01:10   ║
║ John     ║ 2014/03/26 16:21 ║ OUT        ║ 04:16  ║ -       ║
║ Mark     ║ 2014/03/26 16:49 ║ OUT        ║ 05:07  ║ -       ║
╚══════════╩══════════════════╩════════════╩════════╩═════════╝

Criteria:

• The results need to be by user, per one day. If the interval is longer than one day, the total time spent must be calculated for each day
• Double ins/outs will be disregarded, outermost should be used (so in,in,out means track time from the first in to the out)
• I need only daily results. If the employee enters at the end of the day, and exits the next day, it will be ignored. Starting from the first valid IN and last valid OUT.
• The time spent will be calculated only for valid pairs of INs and OUTs, eliminating those that can't be paired (two or more consecutive INs/OUTs)

SqlFiddle

Graphical representation of how to calculate TimeIn and TimeOut (for one employee):

Answer 1

here you go:

if object_id('tempdb..#Punch') is not null
    drop table #Punch

create table #Punch(
    Employee varchar(50) not null,
    PunchDateTime datetime not null,
    ActionType varchar(3) not null
)

insert into #Punch select 'John', '2014/03/26 06:00', 'OUT' -- extra "out"
insert into #Punch select 'John', '2014/03/26 08:00', 'IN'
insert into #Punch select 'John', '2014/03/26 08:01', 'IN' -- extra "in"
insert into #Punch select 'John', '2014/03/26 08:02', 'IN' -- extra "in"
insert into #Punch select 'John', '2014/03/26 08:03', 'IN' -- extra "in"
insert into #Punch select 'Mark', '2014/03/26 08:12', 'IN'
insert into #Punch select 'John', '2014/03/26 08:50', 'OUT'
insert into #Punch select 'John', '2014/03/26 08:51', 'OUT' -- extra "out"
insert into #Punch select 'John', '2014/03/26 08:52', 'OUT' -- extra "out"
insert into #Punch select 'John', '2014/03/26 08:53', 'OUT' -- extra "out"
insert into #Punch select 'John', '2014/03/26 09:29', 'IN'
insert into #Punch select 'Mark', '2014/03/26 10:35', 'OUT'
insert into #Punch select 'John', '2014/03/26 10:55', 'OUT'
insert into #Punch select 'Mark', '2014/03/26 11:42', 'IN'
insert into #Punch select 'John', '2014/03/26 12:38', 'IN'
insert into #Punch select 'John', '2014/03/26 16:21', 'OUT'
insert into #Punch select 'Mark', '2014/03/26 16:49', 'OUT'

select *
from (
    select
        p.Employee,
        p.PunchDateTime,
        p.ActionType,
        case when p.ActionType = 'IN' 
            then '-' 
            else coalesce(substring(convert(varchar(30), p.PunchDateTime - p.PreviousPunchDateTime, 20), 12, 5), '-')
        end as TimeIn,
        case when p.ActionType = 'OUT' 
            then '-' 
            else coalesce(substring(convert(varchar(30), p.PunchDateTime - p.PreviousPunchDateTime, 20), 12, 5), '-')
        end as TimeOut
    from (
        select
            a.Employee,
            a.PunchDateTime,
            a.ActionType,
            (
                select top 1 b.PunchDateTime 
                from #Punch b 
                where b.Employee = a.Employee 
                    and b.PunchDateTime < a.PunchDateTime
                    and b.ActionType <> a.ActionType
                    and datediff(day, b.PunchDateTime, a.PunchDateTime) = 0 -- same day
                    and not exists(
                        select 1
                        from #Punch c
                        where c.Employee = a.Employee
                            and c.ActionType = a.ActionType
                            and c.PunchDateTime < a.PunchDateTime
                            and c.PunchDateTime > b.PunchDateTime
                    )
                    and not (
                        b.ActionType = 'OUT'
                        and not exists(select 1 from #Punch d where d.Employee = a.Employee and d.ActionType = 'IN' and d.PunchDateTime < b.PunchDateTime)
                    )
                order by b.PunchDateTime asc
            ) as PreviousPunchDateTime
        from #Punch a
    ) p
) p2
where not (p2.ActionType = 'OUT' and p2.TimeIn = '-' and p2.TimeOut = '-')
    and not (
        p2.ActionType = 'IN' and p2.TimeIn = '-' and p2.TimeOut = '-'
        and exists(select 1 from #Punch a where a.ActionType = 'IN' and a.Employee = p2.Employee and a.PunchDateTime < p2.PunchDateTime))
order by
    p2.PunchDateTime