March 24, 2014 at 7:02 pm
Hi,
We have a Time & Attendance application that stores the time punched by each employee in a sequential format. There is no indication as to whether the time is an "IN" time or "OUT" time. The application takes the first entry for the Employee /Day to be "IN", the next one to be "OUT" , the 3rd one to be "IN" and so on.
I want to write a query to export records with the in and out times for each employee per day. How can this be done
March 24, 2014 at 8:25 pm
pssudarshan (3/24/2014)
Hi,We have a Time & Attendance application that stores the time punched by each employee in a sequential format. There is no indication as to whether the time is an "IN" time or "OUT" time. The application takes the first entry for the Employee /Day to be "IN", the next one to be "OUT" , the 3rd one to be "IN" and so on.
I want to write a query to export records with the in and out times for each employee per day. How can this be done
The best way to do it is to have someone modify the time-punch machine to identify the ins and outs.
The second best way would be to use ROW_NUMBER() with a partition by employee and day... all the odd numbers would be "INs", and the even numbers would be "Outs".
If you want a coded example, please see the first link under "Helpful Links" in my signature line below.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 24, 2014 at 9:02 pm
Got part of the way there... must have done something silly, because the math looks backwards... I created some sample records to play with... hopefully it's enough.
SELECT EmpID, InOutDate
,MIN(InOutTimeStamp) AS InStamp
,MAX(InOutTimeStamp) AS OutStamp
,DATEDIFF(n,MAX(InOutTimeStamp),MIN(InOutTimeStamp)) AS MinsWorked
FROM
(SELECT EmpID
, InOutDate
, InOutTimeStamp
, ROW_NUMBER() OVER (PARTITION BY EmpID, InOutDate ORDER BY EmpID, InOutDate) AS rn
FROM
(
SELECT EmpID
, InOutTimeStamp
, CAST(InOutTimeStamp AS DATE) AS InOutDate
FROM
(SELECT 1 AS EmpID,'2-1-2014 8:30' AS InOutTimeStamp
UNION ALL
SELECT 1, '2-1-2014 18:30'
UNION ALL
SELECT 1, '2-2-2014 7:30'
UNION ALL
SELECT 1, '2-2-2014 15:30'
UNION ALL
SELECT 2, '2-1-2014 6:30'
UNION ALL
SELECT 2, '2-1-2014 19:30') x
) y
) z
GROUP BY EmpID, InOutDate
ORDER BY EmpID, InOutDate;
March 27, 2014 at 7:15 pm
Hi Jeff & Pietlinden
Thanks to both of you for your tips. Using these, I have written this code that provides me with the time in and out for a day and the number of minutes worked.
-- Code Start----
CREATE TABLE #TempAttendance (
[PayPunchID] [varchar](15) NOT NULL,
[PayrollID] [varchar](15) NOT NULL,
[FirstName] [varchar](255) NULL,
[LastName] [varchar](255) NULL,
[ActualTime] [DateTime] NOT NULL,
[RoundedTime] [DateTime] NOT NULL,
[Date] [DateTime] NULL,
[TimeN] [INT] NULL,
[INOUT] [VARCHAR] (3) NULL
);
WITH CTEEmpAttendance AS
(SELECT A.EmployeeID AS PayPunchID, E.EmployeeID AS PayrollID, E.FirstName, E.LastName, A.DateTime, A.RoundDateTime, DATEADD(dd,0,DATEDIFF(dd,0,A.DateTime)) AS DateasDate, row_number()
OVER (partition BY A.EmployeeID, DATEADD(dd,0,DATEDIFF(dd,0,A.DateTime))
ORDER BY A.EmployeeID, A.DateTime) AS TimeN
FROM tblAttendance A INNER JOIN tblEmployees E
ON A.EmployeeID = E.ID
WHERE [DateTime] >= '15 Mar 2014'
AND [DateTime] < '22 Mar 2014'
)
INSERT INTO #TempAttendance ( PayPunchID, PayrollID, FirstName, LastName, ActualTime, RoundedTime, Date, TimeN, INOUT)
SELECT PayPunchID, PayrollID, FirstName, LastName, [DateTime], RoundDateTime, DateasDate, TimeN,INOUT =
CASE
WHEN (TimeN % 2) != 0 THEN 'IN'
WHEN (TimeN % 2) = 0 THEN 'OUT'
END
FROM CTEEmpAttendance
-- SELECT * from #TempAttendance
SELECT PayPunchID, PayrollID, FirstName, LastName, Date, MIN(ActualTime) AS Actual_IN, MIN(RoundedTime) AS IN_Time, MAX(ActualTime) AS Actual_OUT, MAX(RoundedTime) AS OUT_Time, DATEDIFF(n, MIN(RoundedTime), MAX(RoundedTime)) AS MinsWorked
FROM #TempAttendance
GROUP BY PayPunchID, PayrollID, FirstName, LastName, Date
DROP TABLE #TempAttendance
-- Code end --
Using the above code, I am able to get the number of minutes worked based on the first and last punches in a day. But our employees are required to punch out and in when they go on breaks. How can we get the number of minutes worked when the data looks like this...
T&AIDEmp IDFirstNameLastNameActual timeRounded TimeDatecounterIN/OUT
841273JohnSmith15/03/2014 10:5615/03/2014 11:0015/03/2014 0:001IN
841273JohnSmith15/03/2014 13:5715/03/2014 13:4515/03/2014 0:002OUT
841273JohnSmith15/03/2014 14:3915/03/2014 14:4515/03/2014 0:003IN
841273JohnSmith15/03/2014 20:4615/03/2014 20:4515/03/2014 0:004OUT
841273JohnSmith16/03/2014 7:2016/03/2014 7:3016/03/2014 0:001IN
841273JohnSmith16/03/2014 10:5816/03/2014 10:4516/03/2014 0:002OUT
841273JohnSmith16/03/2014 11:1916/03/2014 11:3016/03/2014 0:003IN
841273JohnSmith16/03/2014 16:1316/03/2014 16:0016/03/2014 0:004OUT
841273JohnSmith17/03/2014 9:5117/03/2014 10:0017/03/2014 0:001IN
841273JohnSmith17/03/2014 13:3317/03/2014 13:3017/03/2014 0:002OUT
841273JohnSmith17/03/2014 14:0717/03/2014 14:1517/03/2014 0:003IN
841273JohnSmith17/03/2014 19:5317/03/2014 19:4517/03/2014 0:004OUT
841273JohnSmith18/03/2014 9:4818/03/2014 10:0018/03/2014 0:001IN
841273JohnSmith18/03/2014 13:5118/03/2014 13:4518/03/2014 0:002OUT
841273JohnSmith18/03/2014 14:3018/03/2014 14:4518/03/2014 0:003IN
841273JohnSmith18/03/2014 19:5418/03/2014 19:4518/03/2014 0:004OUT
841273JohnSmith19/03/2014 6:4819/03/2014 7:0019/03/2014 0:001IN
841273JohnSmith19/03/2014 10:2119/03/2014 10:1519/03/2014 0:002OUT
841273JohnSmith19/03/2014 10:3819/03/2014 10:4519/03/2014 0:003IN
841273JohnSmith19/03/2014 13:3919/03/2014 13:3019/03/2014 0:004OUT
841273JohnSmith19/03/2014 14:0919/03/2014 14:1519/03/2014 0:005IN
841273JohnSmith19/03/2014 17:0019/03/2014 17:0019/03/2014 0:006OUT
841273JohnSmith20/03/2014 10:3820/03/2014 10:4520/03/2014 0:001IN
841273JohnSmith20/03/2014 14:2320/03/2014 14:1520/03/2014 0:002OUT
841273JohnSmith20/03/2014 14:5420/03/2014 15:0020/03/2014 0:003IN
841273JohnSmith20/03/2014 20:2020/03/2014 20:1520/03/2014 0:004OUT
841273JohnSmith21/03/2014 6:4721/03/2014 7:0021/03/2014 0:001IN
841273JohnSmith21/03/2014 10:1021/03/2014 10:0021/03/2014 0:002OUT
841273JohnSmith21/03/2014 10:3521/03/2014 10:4521/03/2014 0:003IN
841273JohnSmith21/03/2014 13:2721/03/2014 13:1521/03/2014 0:004OUT
841273JohnSmith21/03/2014 14:0121/03/2014 14:1521/03/2014 0:005IN
841273JohnSmith21/03/2014 20:4021/03/2014 20:3021/03/2014 0:006OUT
I need to use the rounded time... Thanks for all the great guidance
March 27, 2014 at 8:04 pm
Thanks for the great feedback and posting what you did. If it were me, I'd still lobby heavily for the clock punches that came out of the "Pay Punch" machine to have a built in In/Out indicator for each row it sends because, someday, someone is going to punch in at 10PM and punch out at 7AM.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 27, 2014 at 9:28 pm
Hi Jeff,
Lobbying for a change in the clock design is not an option at present. I am stuck with this clock and I now have to work out a way for calculating hours worked with what I have. With the tips i got from you and @pietlinden I have been able to calculate the minutes worked in a day -first punch to last punch. I am still trying to find a way of calculating the time difference between successive IN and OUT punches. Once this is done, I will need to add all of these for a day together to get the actual time worked in a day. Then comes the calculation of normal time, overtime and double overtime.. but that is not too difficult once I get to an accurate computation of hours per day.
I would appreciate some pointers on how to achieve this.
cheers
Sudarshan.
March 27, 2014 at 9:50 pm
pssudarshan (3/27/2014)
Hi Jeff,Lobbying for a change in the clock design is not an option at present. I am stuck with this clock and I now have to work out a way for calculating hours worked with what I have. With the tips i got from you and @pietlinden I have been able to calculate the minutes worked in a day -first punch to last punch. I am still trying to find a way of calculating the time difference between successive IN and OUT punches. Once this is done, I will need to add all of these for a day together to get the actual time worked in a day. Then comes the calculation of normal time, overtime and double overtime.. but that is not too difficult once I get to an accurate computation of hours per day.
I would appreciate some pointers on how to achieve this.
cheers
Sudarshan.
If the column is a DATETIME column, time differences are easy for periods of less than 24 hours. Just subtract the In time from the Out time and display in the 108 (hh:mm:ss) format using CONVERT.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 27, 2014 at 10:21 pm
True. But the problem is that the IN time and the OUT time are not on the same row. My difficulty is in getting the IN time for a particular employee and date paired with the OUT time for the same employee and date and getting these two values on the same row to compute the time difference. Please bear in mind that there will be multiple IN and OUT times for each employee / day combination.
March 27, 2014 at 10:26 pm
pssudarshan (3/27/2014)
True. But the problem is that the IN time and the OUT time are not on the same row. My difficulty is in getting the IN time for a particular employee and date paired with the OUT time for the same employee and date and getting these two values on the same row to compute the time difference. Please bear in mind that there will be multiple IN and OUT times for each employee / day combination.
Post a CREATE TABLE and some readily consumable data and I'll show you how to do it. See the first link under "Helpful Links" in my signature line below for how to do that.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2014 at 3:03 pm
Hi,
I posted this file yesterday. Can't understand why it does not appear today.
Anyway, the attached file has the scripts to create and populate a table that is a replica of the table I get after doing a bit of work on the original table to mark times as IN and OUT.
Sudarshan.
March 28, 2014 at 11:07 pm
Perfect. Thanks for posting that file.
Ignoring the TimeN and InOut columns that you made, here's the code to pivot (using a CROSSTAB) the pairs of times, calculate the differences between each time pair, and format the results.
WITH cteEnumerate AS
(
SELECT PayPunchID, PayrollID, FirstName, LastName, ActualTime, RoundedTime, Date,
TimeN = ROW_NUMBER() OVER (PARTITION BY PayrollID,Date ORDER BY ActualTime)-1
FROM #Temp
),
cteCrossTab AS
(
SELECT PayPunchID, PayrollID, FirstName, LastName, Date,
ActualTimeIn = MAX(CASE WHEN TimeN %2 = 0 THEN ActualTime ELSE 0 END),
ActualTimeOut = MAX(CASE WHEN TimeN %2 = 1 THEN ActualTime ELSE 0 END),
RoundedTimeIn = MAX(CASE WHEN TimeN %2 = 0 THEN RoundedTime ELSE 0 END),
RoundedTimeOut = MAX(CASE WHEN TimeN %2 = 1 THEN RoundedTime ELSE 0 END)
FROM cteEnumerate
GROUP BY PayPunchID, PayrollID, FirstName, LastName, Date, TimeN/2
)
SELECT PayPunchID, PayrollID, FirstName, LastName,
Date = CONVERT(CHAR( 7),Date ,120),
ActualTimeIn = CONVERT(CHAR( 5),ActualTimeIn ,108),
ActualTimeOut = CONVERT(CHAR( 5),ActualTimeOut ,108),
ActualDuration = CONVERT(CHAR( 5),ActualTimeOut -ActualTimeIn ,108),
RoundedTimeIn = CONVERT(CHAR( 5),RoundedTimeIn ,108),
RoundedTimeOut = CONVERT(CHAR( 5),RoundedTimeOut,108),
RoundedDuration = CONVERT(CHAR( 5),RoundedTimeOut-RoundedTimeIn,108)
FROM cteCrossTab
;
That returns the following...
PayPunchID PayrollID FirstName LastName Date ActualTimeIn ActualTimeOut ActualDuration RoundedTimeIn RoundedTimeOut RoundedDuration
---------- --------- --------- -------- ------- ------------ ------------- -------------- ------------- -------------- ---------------
8412 73 Jone Daua 2014-03 10:56 13:57 03:01 11:00 13:45 02:45
8412 73 Jone Daua 2014-03 14:39 20:46 06:07 14:45 20:45 06:00
8412 73 Jone Daua 2014-03 07:20 10:58 03:38 07:30 10:45 03:15
8412 73 Jone Daua 2014-03 11:19 16:13 04:54 11:30 16:00 04:30
8412 73 Jone Daua 2014-03 09:51 13:33 03:42 10:00 13:30 03:30
8412 73 Jone Daua 2014-03 14:07 19:53 05:46 14:15 19:45 05:30
8412 73 Jone Daua 2014-03 09:48 13:51 04:03 10:00 13:45 03:45
8412 73 Jone Daua 2014-03 14:30 19:54 05:24 14:45 19:45 05:00
8412 73 Jone Daua 2014-03 06:48 10:21 03:33 07:00 10:15 03:15
8412 73 Jone Daua 2014-03 10:38 13:39 03:01 10:45 13:30 02:45
8412 73 Jone Daua 2014-03 14:09 17:00 02:51 14:15 17:00 02:45
8412 73 Jone Daua 2014-03 10:38 14:23 03:45 10:45 14:15 03:30
8412 73 Jone Daua 2014-03 14:54 20:20 05:26 15:00 20:15 05:15
8412 73 Jone Daua 2014-03 06:47 10:10 03:23 07:00 10:00 03:00
8412 73 Jone Daua 2014-03 10:35 13:27 02:52 10:45 13:15 02:30
8412 73 Jone Daua 2014-03 14:01 20:40 06:39 14:15 20:30 06:15
--Jeff Moden
Change is inevitable... Change for the better is not.
March 29, 2014 at 9:59 pm
Hi Jeff,
Thanks a lot for your post. This should work just fine. I shall try this out on our server tomorrow and update you about the results.
Cheers
Sudarshan.
March 30, 2014 at 10:49 pm
Hi Jeff,
Your script worked like a charm. Thanks a lot.
Sudarshan.
March 31, 2014 at 10:17 am
Thanks for the feedback.
Two things, though...
1. You're the one that has to support it. Do you understand how it works?
2. I'm also still greatly concerned about the people who may punch in late on one day and punch out in the wee hours of the next day which would throw this whole method on the floor. If it were me, I'd spend some time on the detection of that particular problem because it's going to happen when you least expect it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2014 at 4:43 pm
Hi Jeff,
I do understand the fundamentals of your script. I understand your concern regarding employees who login in the afternoon and log out early the next day morning. We don't operate on a 24 hours basis and our stores close by around 9:00 - 9:30 PM in the normal course. On special days, when our staff are required to work late into the night, we will manually override the system hours. Since these are only 2 or 3 days in a year, we can handle this.
Thank you for highlighting the point though. This just goes to prove that you are not just helpful, you are also very professional in your approach.
Cheers
Sudarshan.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply