Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting sequential time records into IN and OUT times


Converting sequential time records into IN and OUT times

Author
Message
pssudarshan
pssudarshan
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 270
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45119 Visits: 39918
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2180 Visits: 12521
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;


pssudarshan
pssudarshan
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 270
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45119 Visits: 39918
@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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pssudarshan
pssudarshan
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 270
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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45119 Visits: 39918
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pssudarshan
pssudarshan
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 270
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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45119 Visits: 39918
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pssudarshan
pssudarshan
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 270
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.
Attachments
Code.txt (16 views, 4.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search