quote:

---

Originally posted by sz1

There are no column ID fields so assuming your Employee name will always stay unique and no other same name will be added...you should be ok, you have 00:40 for John but he clocked in at 08:00 and clocked out at 08:50 which = 00:50, where are you getting your 00:40?

We are the creators of our own reality!

---

quote:

---

outermost should be used (so in,in,out means track time from the first in to the out)

---

06:42 IN     
06:43 OUT
06:52 OUT    Discard
06:53 OUT    Discard
06:54 OUT    Discard

quote:

---

Originally posted by milo1981

Thank you!
Now, it works great when calculating TimeIn and TimeOut!

But..
There is a problem with the last IN record at '2014-03-26 17:17:00.000'.
Being an unpaired record, it should be discarded, like it did with first OUT record at '2014-03-26 05:30:00.000'

---

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

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

insert into #Punch select 'John', '2014-03-26 05:30:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-26 05:36:00.000', 'IN'
insert into #Punch select 'John', '2014-03-26 06:24:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-26 06:42:00.000', 'IN'
insert into #Punch select 'John', '2014-03-26 06:43:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-26 06:52:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-26 06:53:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-26 06:54:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-26 06:55:00.000', 'IN'
insert into #Punch select 'John', '2014-03-26 08:46:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-26 08:52:00.000', 'IN'
insert into #Punch select 'John', '2014-03-26 10:08:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-26 10:12:00.000', 'IN'
insert into #Punch select 'John', '2014-03-26 10:22:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-26 10:24:00.000', 'IN'
insert into #Punch select 'John', '2014-03-26 16:48:00.000', 'IN'
insert into #Punch select 'John', '2014-03-26 16:57:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-26 17:04:00.000', 'IN'
insert into #Punch select 'John', '2014-03-26 17:07:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-26 17:09:00.000', 'IN'
insert into #Punch select 'John', '2014-03-26 17:10:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-26 17:15:00.000', 'IN'
insert into #Punch select 'John', '2014-03-26 17:16:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-26 17:17:00.000', 'IN'
insert into #Punch select 'John', '2014-03-27 08:01:00.000', 'OUT'
insert into #Punch select 'John', '2014-03-27 09:15:00.000', 'IN'
insert into #Punch select 'John', '2014-03-27 09:26:00.000', 'OUT'

-- Detail Query
; with punch as
(
	select	Employee,
		row_no 		= row_number() over (partition by Employee, dateadd(day, datediff(day, 0, PunchDateTime), 0)
							 order by PunchDateTime),
		PunchDate	= dateadd(day, datediff(day, 0, PunchDateTime), 0),
		PunchDateTime,
		ActionType
	from	#Punch
),
punch2 as -- Valid pairs of IN-OUT
(
	select	c.Employee, 
		row_no = row_number() over (partition by c.Employee, c.PunchDate order by c.row_no), 
		c.PunchDate, c.PunchDateTime, c.ActionType
	from	punch c
		left join punch p	on	c.Employee	= p.Employee
					and	c.PunchDate	= p.PunchDate
					and	c.row_no	= p.row_no + 1
		left join punch n	on	c.Employee	= n.Employee
					and	c.PunchDate	= n.PunchDate
					and	c.row_no	= n.row_no - 1
	where	(c.ActionType = 'IN'  and n.ActionType = 'OUT')
	or	(c.ActionType = 'OUT' and p.ActionType = 'IN')
)
select	c.*,
	TimeIn 	= case 	when c.ActionType = 'OUT' 
			then isnull(convert(varchar(5), dateadd(minute, datediff(minute, p.PunchDateTime, c.PunchDateTime), 0), 108), '-')
			else '-'
			end,
	TimeOut	= case 	when c.ActionType = 'IN'
			then isnull(convert(varchar(5), dateadd(minute, datediff(minute, p.PunchDateTime, c.PunchDateTime), 0), 108), '-')
			else '-'
			end
from	punch2 c
	left join punch2 p	on	c.Employee	= p.Employee
				and	c.PunchDate	= p.PunchDate
				and	c.row_no	= p.row_no + 1
order by c.Employee, c.PunchDate, c.row_no

-- Summary by Employee & Date
; with punch as
(
	select	Employee,
		row_no 		= row_number() over (partition by Employee, dateadd(day, datediff(day, 0, PunchDateTime), 0)
							 order by PunchDateTime),
		PunchDate	= dateadd(day, datediff(day, 0, PunchDateTime), 0),
		PunchDateTime,
		ActionType
	from	#Punch
),
punch2 as
(
	select	c.Employee, 
		row_no = row_number() over (partition by c.Employee, c.PunchDate order by c.row_no), 
		c.PunchDate, c.PunchDateTime, c.ActionType
	from	punch c
		left join punch p	on	c.Employee	= p.Employee
					and	c.PunchDate	= p.PunchDate
					and	c.row_no	= p.row_no + 1
		left join punch n	on	c.Employee	= n.Employee
					and	c.PunchDate	= n.PunchDate
					and	c.row_no	= n.row_no - 1
	where	(c.ActionType = 'IN'  and n.ActionType = 'OUT')
	or	(c.ActionType = 'OUT' and p.ActionType = 'IN')
)
select	c.Employee, c.PunchDate,
	TimeIn 	= sum(case 	when c.ActionType = 'OUT' 
				then datediff(minute, p.PunchDateTime, c.PunchDateTime)
				end),
	TimeOut	= sum(case 	when c.ActionType = 'IN'
				then datediff(minute, p.PunchDateTime, c.PunchDateTime)
				end)
from	punch2 c
	left join punch2 p	on	c.Employee	= p.Employee
				and	c.PunchDate	= p.PunchDate
				and	c.row_no	= p.row_no + 1
group by c.Employee, c.PunchDate
order by c.Employee, c.PunchDate

Employee   row_no  PunchDate   PunchDateTime     ActionType TimeIn TimeOut 
---------- ------- ----------- ----------------- ---------- ------ ------- 
John       1       2014-03-26  2014-03-26 05:36  IN         -      -
John       2       2014-03-26  2014-03-26 06:24  OUT        00:48  -
John       3       2014-03-26  2014-03-26 06:42  IN         -      00:18
John       4       2014-03-26  2014-03-26 06:43  OUT        00:01  -
John       5       2014-03-26  2014-03-26 06:55  IN         -      00:12
John       6       2014-03-26  2014-03-26 08:46  OUT        01:51  -
John       7       2014-03-26  2014-03-26 08:52  IN         -      00:06
John       8       2014-03-26  2014-03-26 10:08  OUT        01:16  -
John       9       2014-03-26  2014-03-26 10:12  IN         -      00:04
John       10      2014-03-26  2014-03-26 10:22  OUT        00:10  -
John       11      2014-03-26  2014-03-26 16:48  IN         -      06:26
John       12      2014-03-26  2014-03-26 16:57  OUT        00:09  -
John       13      2014-03-26  2014-03-26 17:04  IN         -      00:07
John       14      2014-03-26  2014-03-26 17:07  OUT        00:03  -
John       15      2014-03-26  2014-03-26 17:09  IN         -      00:02
John       16      2014-03-26  2014-03-26 17:10  OUT        00:01  -
John       17      2014-03-26  2014-03-26 17:15  IN         -      00:05
John       18      2014-03-26  2014-03-26 17:16  OUT        00:01  -
John       1       2014-03-27  2014-03-27 09:15  IN         -      -
John       2       2014-03-27  2014-03-27 09:26  OUT        00:11  -

Employee   PunchDate   TimeIn      TimeOut     
---------- ----------- ----------- ----------- 
John       2014-03-26  260         440
John       2014-03-27  11          NULL

quote:

---

Originally posted by milo1981

Can you explain to me, what does the new PunchDate column does?

---

row_number() over (partition by Employee, dateadd(day, datediff(day, 0, PunchDateTime), 0)
							 order by PunchDateTime)

select	c.Employee, 
	TimeIn 	= sum(case 	when c.ActionType = 'OUT' 
				then datediff(minute, p.PunchDateTime, c.PunchDateTime)
				end),
	TimeOut	= sum(case 	when c.ActionType = 'IN'
				then datediff(minute, p.PunchDateTime, c.PunchDateTime)
				end)
from	punch2 c
	left join punch2 p	on	c.Employee	= p.Employee
				and	c.row_no	= p.row_no + 1
group by c.Employee
order by c.Employee

; with punch as
(
	select	Employee,
		row_no 		= row_number() over (partition by Employee order by PunchDateTime),
		PunchDateTime,
		ActionType
	from	#Punch 
        where   PunchDateTime >= @StartDate
        and     PunchDateTime <  @EndDate
),