Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Converting sequential time records into IN and OUT times Expand / Collapse
Author
Message
Posted Monday, March 24, 2014 7:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:50 PM
Points: 25, Visits: 176
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
Post #1554257
Posted Monday, March 24, 2014 8:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 36,983, Visits: 31,508
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1554266
Posted Monday, March 24, 2014 9:02 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 9:51 PM
Points: 745, Visits: 4,766
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;

Post #1554268
Posted Thursday, March 27, 2014 7:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:50 PM
Points: 25, Visits: 176
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&AID Emp ID FirstName LastName Actual time Rounded Time Date counter IN/OUT
8412 73 John Smith 15/03/2014 10:56 15/03/2014 11:00 15/03/2014 0:00 1 IN
8412 73 John Smith 15/03/2014 13:57 15/03/2014 13:45 15/03/2014 0:00 2 OUT
8412 73 John Smith 15/03/2014 14:39 15/03/2014 14:45 15/03/2014 0:00 3 IN
8412 73 John Smith 15/03/2014 20:46 15/03/2014 20:45 15/03/2014 0:00 4 OUT
8412 73 John Smith 16/03/2014 7:20 16/03/2014 7:30 16/03/2014 0:00 1 IN
8412 73 John Smith 16/03/2014 10:58 16/03/2014 10:45 16/03/2014 0:00 2 OUT
8412 73 John Smith 16/03/2014 11:19 16/03/2014 11:30 16/03/2014 0:00 3 IN
8412 73 John Smith 16/03/2014 16:13 16/03/2014 16:00 16/03/2014 0:00 4 OUT
8412 73 John Smith 17/03/2014 9:51 17/03/2014 10:00 17/03/2014 0:00 1 IN
8412 73 John Smith 17/03/2014 13:33 17/03/2014 13:30 17/03/2014 0:00 2 OUT
8412 73 John Smith 17/03/2014 14:07 17/03/2014 14:15 17/03/2014 0:00 3 IN
8412 73 John Smith 17/03/2014 19:53 17/03/2014 19:45 17/03/2014 0:00 4 OUT
8412 73 John Smith 18/03/2014 9:48 18/03/2014 10:00 18/03/2014 0:00 1 IN
8412 73 John Smith 18/03/2014 13:51 18/03/2014 13:45 18/03/2014 0:00 2 OUT
8412 73 John Smith 18/03/2014 14:30 18/03/2014 14:45 18/03/2014 0:00 3 IN
8412 73 John Smith 18/03/2014 19:54 18/03/2014 19:45 18/03/2014 0:00 4 OUT
8412 73 John Smith 19/03/2014 6:48 19/03/2014 7:00 19/03/2014 0:00 1 IN
8412 73 John Smith 19/03/2014 10:21 19/03/2014 10:15 19/03/2014 0:00 2 OUT
8412 73 John Smith 19/03/2014 10:38 19/03/2014 10:45 19/03/2014 0:00 3 IN
8412 73 John Smith 19/03/2014 13:39 19/03/2014 13:30 19/03/2014 0:00 4 OUT
8412 73 John Smith 19/03/2014 14:09 19/03/2014 14:15 19/03/2014 0:00 5 IN
8412 73 John Smith 19/03/2014 17:00 19/03/2014 17:00 19/03/2014 0:00 6 OUT
8412 73 John Smith 20/03/2014 10:38 20/03/2014 10:45 20/03/2014 0:00 1 IN
8412 73 John Smith 20/03/2014 14:23 20/03/2014 14:15 20/03/2014 0:00 2 OUT
8412 73 John Smith 20/03/2014 14:54 20/03/2014 15:00 20/03/2014 0:00 3 IN
8412 73 John Smith 20/03/2014 20:20 20/03/2014 20:15 20/03/2014 0:00 4 OUT
8412 73 John Smith 21/03/2014 6:47 21/03/2014 7:00 21/03/2014 0:00 1 IN
8412 73 John Smith 21/03/2014 10:10 21/03/2014 10:00 21/03/2014 0:00 2 OUT
8412 73 John Smith 21/03/2014 10:35 21/03/2014 10:45 21/03/2014 0:00 3 IN
8412 73 John Smith 21/03/2014 13:27 21/03/2014 13:15 21/03/2014 0:00 4 OUT
8412 73 John Smith 21/03/2014 14:01 21/03/2014 14:15 21/03/2014 0:00 5 IN
8412 73 John Smith 21/03/2014 20:40 21/03/2014 20:30 21/03/2014 0:00 6 OUT

I need to use the rounded time... Thanks for all the great guidance
Post #1555731
Posted Thursday, March 27, 2014 8:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 36,983, Visits: 31,508
@pssudarshan,

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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1555737
Posted Thursday, March 27, 2014 9:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:50 PM
Points: 25, Visits: 176
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.
Post #1555749
Posted Thursday, March 27, 2014 9:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 36,983, Visits: 31,508
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1555750
Posted Thursday, March 27, 2014 10:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:50 PM
Points: 25, Visits: 176
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 #1555756
Posted Thursday, March 27, 2014 10:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 36,983, Visits: 31,508
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1555757
Posted Friday, March 28, 2014 3:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:50 PM
Points: 25, Visits: 176
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.


  Post Attachments 
Code.txt (8 views, 4.14 KB)
Post #1556162
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse