Geek Answers Handbook

Query for the timecard database

I have a time table for staff to input / output hours, the layout is as follows:

employeeID Clock-in DateTime Clock Type 1004 24/09/2013 13:08 in 1004 25/09/2013 00:50 out 1005 24/09/2013 12:08 in 1005 25/09/2013 03:55 out 2003 24/09/2013 10:06 in 2003 24/09/2013 15:42 out 2003 24/09/2013 17:33 in 2003 24/09/2013 22:17 out

How can I make it look like this:

 Date employeeID Clock-in Clock-out time worked (in hours to 2 decimal place) 24/09/2013 1004 24/09/2013 13:08 25/09/2013 00:50 xx.xx 24/09/2013 1005 24/09/2013 12:08 25/09/2013 03:55 xx.xx 24/09/2013 2003 24/09/2013 10:06 24/09/2013 15:42 xx.xx 24/09/2013 2003 24/09/2013 17:33 24/09/2013 22:17 xx.xx

Note:

  • An employee can have several hours of input / output (i.e. morning / day, night shift).
  • date runs from 04:00:00 to 03:59:59 (i.e., in the example above, everything is classified as 24/09/2013 )
  • How can I process records without a timeout? can I set something like if null , the time is the next date 03:59:59 ?

Thanks in advance.

I updated the following with jods suggestion:

Example 3 I / O Record Sets

 EmployeeID CardDate PunchType 1000 21/09/2013 07:43 in 1000 21/09/2013 11:29 out 1000 21/09/2013 12:39 in 1000 21/09/2013 20:37 out 1000 21/09/2013 21:58 in 1000 22/09/2013 00:16 out

Your request returns

 Date EmployeeID clockInTime clockOutTime 21/09/2013 1000 07:43:57 00:16:22 21/09/2013 1000 21:58:40 11:29:22
+4
source share
2 answers

If you are using SQL SERVER 2005 and above. You can try this

 WITH cte AS ( SELECT [employeeID] ,ti.ClockIn ,ClockOut = (SELECT MIN(t.ClockIn) FROM timecard t WHERE ti.employeeID = t.employeeID AND t.Type = 'out' AND t.ClockIn > ti.ClockIn AND t.ClockIn < DATEADD(hour, 24 + 4 , CAST(CAST(ti.ClockIn AS DATE) AS datetime)) ) ,NextDayLimit = DATEADD(SECOND, -1, DATEADD(hour, 24 + 4 , CAST(CAST(ti.ClockIn AS DATE) AS datetime))) FROM timecard ti WHERE Type = 'in' ) SELECT employeeID, ClockIn, ISNULL(ClockOut, NextDayLimit) AS ClockOut, CAST(DATEDIFF(minute, ClockIn, ISNULL(ClockOut, NextDayLimit))/60.0 AS NUMERIC(19,2)) AS TimeWorked from cte ORDER BY employeeID, ClockIn
0
source

If you are using SQL 2012, here is the basic idea using LEAD:

 SELECT ROUND(clockIn) as [date], employeeId, clockIn as clockInTime, LEAD(clockIn) OVER (PARTITION BY employeeId, ROUND(clockIn) ORDER BY clockIn) as clockOutTime FROM TimeCard WHERE clockType = 'in'

Then you can get the time by making the difference between the clock and the clock.

There are many corner cases that not all are handled here:

  • can a work period last midnight?
  • what if the first timeout is of type 'out'?
  • What if there are two "in one" or "two times" in a line?

The fact is that the clock is processed without an exit. Since the last line of the section is LEAD NULL.

Here's a more robust option that works in SQL 2005:

 WITH NumberedClockIn AS ( SELECT ROUND(clockIn) as [date], employeeId, clockIn as clockTime, clockType, ROW_NUMBER() OVER (PARTITION BY employeeId, ROUND(clockIn) ORDER BY clockIn) as rn FROM TimeCard ) SELECT IN.[date], IN.employeeId, IN.clockTime as clockInTime, OUT.clockTime as clockOutTime FROM NumberedClockIn IN LEFT OUTER JOIN NumberedClockIn OUT ON IN.[date] = OUT.[date] AND IN.employeeId = OUT.employeeID AND IN.rn + 1 = OUT.rn WHERE IN.clockType = 'in' AND OUT.clockType = 'out'

This is more complex and less efficient, but more robust in the face of invalid "in" and "out" sequences. Again, the way it handles invalid sequences may or may not be the behavior you really want.

0
source

More articles:


All Articles