Help Time Attendance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help Time Attendance

Author

Topic

kickapoo
Starting Member

7 Posts

Posted - 2012-07-10 : 11:06:24

Please can someone help me....
I have a query which query the min and max of time in and out...the scenario is Employee log in at July 7 at 2pm at log out at 1am of July 8. He came in again at 2pm of July 8 and log out at July 8 around 11pm. the problem is i query the MIN and MAX, how can i get his 2 out on the same day?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-10 : 12:45:27

will you be capturing in and out time against same record or different records? what are the columns you've in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kickapoo
Starting Member

7 Posts

Posted - 2012-07-10 : 19:35:18

Hi,

im capturing same record.. my table consist of CardNo,EmployeeName,DateLog, Timein, Timeout.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-10 : 22:09:47

what does DateLog contain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kickapoo
Starting Member

7 Posts

Posted - 2012-07-10 : 22:17:26

Dates of their logs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-10 : 22:21:41

sorry how do you know person got out next day from this? As I see here you've DateLog field which has date value so cant understand how you will represent logout time that happened next day

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kickapoo
Starting Member

7 Posts

Posted - 2012-07-10 : 22:27:39

sorry for making this complicated, here is the sample

--------------------------------------------
EmpNo | DateLog | Timein | TimeOut
--------------------------------------------
001 | 07/01/2012 | 2:00pm | null
001 | 07/02/2012 | null | 1:00am
001 | 07/02/2012 | 2:00pm | 11:00pm
001 | 07/03/2012 | 2:00pm | 11:00pm

the programme get the data from bioscypt and save to database, and i create a script which will generate the first in and the last out (i use min and max), the employees has 3 shift schedule

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-11 : 10:57:37

ok. then isnt it a matter of just matching record with null timein against previous null timeout record? something like


;With CTE
AS
(
SELECT t.EmpNo,convert(datetime,convert(char(11),t1.DateLog)+PrevTimeIn) AS DateIn,convert(datetime,convert(char(11),t2.DateLog)+TimeOut) AS DateOut
FROM table t
CROSS APPLY (SELECT TOP 1 DateLog,TimeIn AS PrevTimeIn
             FROM table
             WHERE EMpNo = t.EmpNo
             AND DateLog < t.DateLog
             AND TimeOut IS NULL
             ORDER BY DateLog DESC,TimeIn DESC
             )t1            
WHERE t.TimeIn IS NULL
UNION ALL
SELECT EmpNo,convert(datetime,convert(char(11),DateLog) + TimeIn) AS DateIn,convert(datetime,convert(char(11),DateLog)+TimeOut) AS DateOut
FROM YourTable
WHERE TimeIn > ''
AND TimeOut > ''
)

SELECT EmpNo,DateIn,DateOut
FROM CTE

I hope your timein,timeout fields are varchar and DateLog is date datatype

Apply min max logic over final select to get your required output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources