quote:

---

Originally posted by khtan

;with
cte as
(
	select	UserID, 
		CheckDate 	= dateadd(day, datediff(day, 0, CheckTime), 0), 
		CheckDuration	= datediff(minute, 
							min(case when CheckType = 'I' then CheckTime end), 
							max(case when CheckType = 'O' then CheckTime end))				
	from	checkInout
	group by UserID, dateadd(day, datediff(day, 0, CheckTime), 0)
),
cte2 as
(
	select	UserID,
		CheckDate,
		Attendance 	= case 	when CheckDuration is null then 'NCO'
					when CheckDuration >= 8 * 60 then 'P'
					when CheckDuration <  8 * 60 then 'A'
					end
				
	from	cte
)
select	UserID, 
	[2014-02-25]	= isnull([2014-02-25], 'A'), 
	[2014-02-26]	= isnull([2014-02-26], 'A'), 
	[2014-02-27]	= isnull([2014-02-27], 'A')
from	cte2 c
	pivot
	(
		max(Attendance)
		for CheckDate in ([2014-02-25], [2014-02-26], [2014-02-27])
	) p

---

KH
[spoiler]Time is always against us[/spoiler]

---