Declare @t table (
	id int,
	clockTime datetime,
	InOrOut varchar(10)
)
/*
Insert Into @t Select 1, '2011-04-01 07:55:00', 'IN'
Insert Into @t Select 1, '2011-04-01 10:00:00', 'OUT'
Insert Into @t Select 1, '2011-04-01 10:35:00', 'IN'
Insert Into @t Select 1, '2011-04-01 17:05:00', 'OUT'
*/

Insert Into @t Select 2, '2011-04-01 07:55:00', 'IN'
Insert Into @t Select 2, '2011-04-01 10:00:00', 'OUT'
Insert Into @t Select 2, '2011-04-01 10:35:00', 'IN'
--Insert Into @t Select 2, '2011-04-01 17:05:00', 'OUT'
Insert Into @t Select 2, '2011-04-02 07:55:00', 'IN'
--Insert Into @t Select 2, '2011-04-02 10:00:00', 'OUT'
Insert Into @t Select 2, '2011-04-02 10:35:00', 'IN'
Insert Into @t Select 2, '2011-04-02 17:05:00', 'OUT'

;with cte1(id, clockTime, InOrOut, r)
As (

	Select 
		id, clockTime, InOrOut, r = Row_Number() Over(Partition By id Order By clockTime)
	From @t
), cte2(id, InTime, OutTime)
As (

	Select 
		id = isnull(A.id,B.id),
		InTime = A.ClockTime,
		OutTime = B.Clocktime 
	From (Select * From cte1 Where InOrOut = 'IN') A
	Full Join (Select * From cte1 Where InOrOut = 'OUT') B
	On A.id = B.id
	and A.r = B.r-1
)

Select * 
From cte2
Order By id, InTime

; with cte as
(
	select	*, row_no = row_number() over (partition by id order by ClockTime)
	from	@t
),
cte2 as
(
	select	c1.id, ClockIn = c1.ClockTime, ClockOut = case 	when c2.InOrOut = 'OUT'
								then c2.ClockTime
								else dateadd(day, datediff(day, 0, c1.ClockTime), 0) + '17:00'
								end
	from	cte c1
		left join cte c2	on	c1.row_no	= c2.row_no - 1
                                        and     c1.id           = c2.id
	where	c1.InOrOut	= 'IN'
)
select	id, 
	Date 		= dateadd(day, datediff(day, 0, ClockIn), 0),
	Hours_Worked	= convert(varchar(10), dateadd(second, datediff(second, ClockIn, ClockOut), 0), 108)
from	cte2

quote:

---

My results dont seem to be correct.

---

cte2 as
(
select c1.Emp_ID, ClockIn = c1.ClockTime, ClockOut = case when c2.Name = 'OUT' then c2.ClockTime
else dateadd(day, datediff(day, 0, c1.ClockTime), 0) + '17:00' end
from cte c1 left join cte c2 on	c1.row_no = c2.row_no - 1 and c1.Emp_ID = c2.Emp_ID
where c1.Name = 'IN')

select   
   id, 
   Date = dateadd(day, datediff(day, 0, ClockIn), 0),
   Hours_Worked = convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108)
FROM
   cte2
GROUP BY
   id,
   dateadd(day, datediff(day, 0, ClockIn), 0)

select   
   id, 
   Date = dateadd(day, datediff(day, 0, ClockIn), 0),
   Hours_Worked = convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108)
FROM
   cte2
GROUP BY
   id,
   dateadd(day, datediff(day, 0, ClockIn), 0)

quote:

---

Originally posted by Jas

Thank you soooo much. Thanks to everyone who helped. :-)

I just have one more question. With this data, I want to find out how many hours the person worked before 08:00:00AM and how many hours the person worked after 16:00:00 each day.

How do I go about doing this?

---

select   
   id, 
   Date = dateadd(day, datediff(day, 0, ClockIn), 0),
   Hours_Worked = convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108),
   Hours_Worked_Before8AM = 
   convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, 
      case 
         when DATEDIFF(hh,0,ClockIn)%24 < 8 AND DATEDIFF(hh,0,ClockOut)%24 >= 8 
            THEN DATEADD(hh,(DATEDIFF(hh,0,ClockOut)/24)*24+8,0) 
      end
   )), 0), 108),
   Hours_Worked_After4PM = 
   convert(varchar(10), dateadd(second, sum(datediff(second, 
      case 
         when DATEDIFF(hh,0,ClockIn)%24 < 16 AND DATEDIFF(hh,0,ClockOut)%24 >= 16 
            THEN DATEADD(hh,(DATEDIFF(hh,0,ClockIn)/24)*24+16,0) 
      END,
      ClockOut
   )), 0), 108)
FROM
   cte2
GROUP BY
   id,
   dateadd(day, datediff(day, 0, ClockIn), 0)

quote:

---

Originally posted by Jas

Also, I want to change the query to allow for a default if the person only clocked out and not in for a day. What do I need to change in the query to accomodate this? So lets say if there is no IN clock for the day, we must assume that the person started at 08:00AM.

I tried the following but it does not work:

; with cte as (select Emp.Name + ' ' + Emmp.Surname As FullName, Clock.ClockTime, Dir.Name,
row_no = row_number() over (partition by Emp.Name + ' ' + Emp.Surname order by C.ClockTime)
from Employee Emp INNER JOIN ClockIns Clock ON Emp.Emp_Id = Clock.Emp_ID
INNER JOIN Direction Dir ON Clock.Direction_ID = Dir.Direction_ID),
cte2 as ( select c1.FullName,
ClockIn = case when c2.Name = 'IN' then c1.ClockTime
else dateadd(day, datediff(day, 0, c1.ClockTime), 0) + '08:00:00' end,
ClockOut = case when c2.Name = 'OUT' then c2.ClockTime
else dateadd(day, datediff(day, 0, c1.ClockTime), 0) + '17:00:00' end
from cte c1 left join cte c2 on c1.row_no = c2.row_no - 1
and c1.FullName = c2.FullName where c1.Name = 'IN')
select FullName, Date = dateadd(day, datediff(day, 0, ClockIn), 0),
Hours_Worked = convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108)
FROM
cte2
GROUP BY
FullName, dateadd(day, datediff(day, 0, ClockIn), 0)

---