SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Daily Attendance Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sufarlin
Starting Member

2 Posts

Posted - 03/04/2014 :  04:22:56  Show Profile  Reply with Quote
Need help...javascript:insertsmilie('')javascript:insertsmilie('')

I have a "checkInout" table with a structure like the following, I took the "checkinout" table from the database attendance machine.

UserID (Int)
CheckTime (datetime)
CheckType (nvarchar(1))

UserID -;- CheckTime -;- CheckType
349 -;- 2014-02-25 08:00:28.000 -;- I
349 -;- 2014-02-25 17:10:13.000 -;- O
912 -;- 2014-02-25 07:49:44.000 -;- I
912 -;- 2014-02-25 17:50:47.000 -;- O
919 -;- 2014-02-25 08:00:49.000 -;- I
919 -;- 2014-02-25 17:07:21.000 -;- O
919 -;- 2014-02-26 07:50:01.000 -;- I
919 -;- 2014-02-26 07:59:16.000 -;- I
919 -;- 2014-02-27 08:01:46.000 -;- I
919 -;- 2014-02-27 17:30:58.000 -;- O
919 -;- 2014-02-27 17:29:54.000 -;- I
919 -;- 2014-02-27 17:35:15.000 -;- O

I want to get a result like this

UserId -;- 2014-02-25 -;- 2014-02-26 -;- 2014-02-27
349 -;- P -;- A -;- A
912 -;- P -;- A -;- A
919 -;- P -;- A -;- A
919 -;- A -;- NCO -;- A
919 -;- A -;- A -;- P

with this condition

1. if (Min (CHECKTIME) where CHECKTYPE = I) - (Max (CHECKTIME) where CHECKTYPE = O)> 8:00:00 (total hours) then "P"
2. if (Min (CHECKTIME) where CHECKTYPE = I) - (Max (CHECKTIME) where CHECKTYPE = O) <08:00:00 (total hours) then "A"

P = Present
A = Not Present
NCO = No Check Out
08:00:00 is the number of hours worked

I've tried various ways, but did not get satisfactory results
can anyone willing to help me? I'm building HR management and I stopped in this issue

I am waiting for your reply, you can reply via this forum or directly to my email sufarlin@yahoo.com

Thank you for your attention,






Sufarlin

khtan
In (Som, Ni, Yak)

Singapore
17608 Posts

Posted - 03/04/2014 :  04:58:47  Show Profile  Reply with Quote

;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
Time is always against us

Go to Top of Page

Sufarlin
Starting Member

2 Posts

Posted - 03/04/2014 :  05:24:47  Show Profile  Reply with Quote
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
Time is always against us




thank you for your answer, your answer is very precious to me. once again thank you. but there is one more question that makes me confused. how to display it in crystal report with checkdate conditions for one month while the number of days in a month sometimes there are 31 days, 28 days, 30 days?

Sufarlin
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000