Find working time of each employee

Question

Find working time of each employee

dudekula.kareemulla

Ten Centuries

Points: 1039
More actions
August 18, 2015 at 11:14 pm

#319970

IF EXISTS ( SELECT TOP 1 1 FROM sys.tables WHERE name = '#EmpHours' )
DROP TABLE #EmpHours;
CREATE TABLE #EmpHours
(
EmpNameVARCHAR(50) ,
SwipeDate DATE ,
SwipeTime TIME ,
SwipeType VARCHAR(10)
);
INSERT INTO #EmpHours( EmpName, SwipeDate, SwipeTime, SwipeType )
VALUES ( 'Arjun','2013-01-07', '08:00', 'SwipeIN'), --** N
( 'Rosy','2013-01-07', '08:10', 'SwipeIN'), --** N
( 'Arjun','2013-01-07', '11:30', 'SwipeOUT'),
( 'Arjun','2013-01-07', '11:35', 'SwipeOUT'), --** X
( 'Arjun','2013-01-07', '12:45', 'SwipeIN'), --** N
( 'Rosy','2013-01-07', '16:45', 'SwipeOUT'), --** X
( 'Arjun','2013-01-07', '14:25', 'SwipeIN'),
( 'Arjun','2013-01-07', '17:30', 'SwipeOUT'),
( 'Sandhya','2013-01-07', '08:33', 'SwipeIN'), --** N
( 'Sandhya','2013-01-07', '17:33', 'SwipeOUT'),
( 'Sandhya','2013-01-07', '17:35', 'SwipeOUT'); --** X
GO
/*
Find working time of each employee .
1. If there are two 'SwipeIN's without SwipeOUT take earliest SwipeIN (See --** N)
2. If there are two 'SwipeOUT's without SwipeIN take latest SwipeOUT ( See --** X )
3. Pair SwipeIN and SwipeOUT according to time
and calculate woringtime for each Emplouee
4. Assumption for single calendar day
5. No hard coding. Should be General Solution
6. First Entry for the day is always SwipeIN
output should be
EmpNameSwipeINTimeSwipeOUTTimesecondsWorkTime
Arjun2013-01-07 08:00:00.0002013-01-07 11:35:00.0001290003:35:00
Arjun2013-01-07 14:25:00.0002013-01-07 17:30:00.0001110003:05:00
Rosy2013-01-07 08:10:00.0002013-01-07 16:45:00.0003090008:35:00
Sandhya2013-01-07 08:33:00.0002013-01-07 17:35:00.0003252009:02:00
*/
--=================================================================

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Seems like you might be making this harder than it really is.

Is it not true that the odd-numbered "logins" for each employee are clocking in and the even ones are clocking out? In that case, you could use ROW_NUMBER() and partition by EmployeeID and Date, and you should get pairs.

SELECT EmpName

,SwipeDate

,SwipeTime

,(1+ROW_NUMBER() OVER (PARTITION BY EmpName, SwipeDate ORDER BY EmpName, SwipeDate))/2 AS rn

FROM #EmpHours

Once you have that, you can use a LAG substitute (was introduced in SQL 2012). Here's one[/url] from Pinal Dave at SQLAuthority.

It will let you pair the rows (in/out pairs) on the same row and then just do simple date math.

of course you'd have to figure out first if there were any clock in/out pairs that were screwy by counting the logins per day and making sure the number was even.

After that, it's just a simple DATEDIFF() question.

dudekula.kareemulla Ten Centuries Points: 1039 More actions · Answer 2

Hi Thanks for your reply ,,,,

I got your point but here situation may be swipe-in and swipe-out machines are separate and

new joiners may be confused and done instead of swipe-in they done swipe-out like that mistakes happen. in this situation data entry happen in table like this

and also this query execution data base is SQL2008.

pietlinden SSC Guru Points: 63503 More actions · Answer 3

How about...

SELECT EmpName, SwipeDate, Time_In, Time_Out, DATEDIFF(minute,Time_In,Time_Out) AS WorkTimeMins

FROM

(SELECT TOP 100 PERCENT EmpName, SwipeDate, MIN(SwipeTime) AS Time_In, MAX(SwipeTime) AS Time_Out

FROM #EmpHours

GROUP BY EmpName, SwipeDate

ORDER BY EmpName, SwipeDate) x;

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

;WITH ProcessedData AS (

SELECT EmpName, SwipeDate, MIN_SwipeTime = MIN(SwipeTime), MAX_SwipeTime = MAX(SwipeTime), SwipeType, grp

FROM (

SELECT EmpName, SwipeDate, SwipeTime, SwipeType, grp = rn1-rn2

FROM (

SELECT

EmpName, SwipeDate, SwipeTime, SwipeType,

rn1 = ROW_NUMBER() OVER(PARTITION BY SwipeDate ORDER BY SwipeTime),

rn2 = ROW_NUMBER() OVER(PARTITION BY SwipeDate, EmpName, SwipeType ORDER BY SwipeTime)

FROM #EmpHours

) d

) e

GROUP BY EmpName, SwipeDate, SwipeType, grp

)

SELECT

i.EmpName, i.SwipeDate,

TimeIn = i.MIN_SwipeTime,

x.[TimeOut]

FROM ProcessedData i

CROSS APPLY (

SELECT [TimeOut] = MIN(MAX_SwipeTime)

FROM ProcessedData o

WHERE o.SwipeType = 'SwipeOUT'

AND o.SwipeDate = i.SwipeDate

AND o.EmpName = i.EmpName

AND o.MAX_SwipeTime > i.MIN_SwipeTime

) x

WHERE i.SwipeType = 'SwipeIN'

ORDER BY i.SwipeDate, i.EmpName

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Really, really looks like and sounds like homework. Even if it's not, you should at least try. And rules 4, 5, and 6 create an oxymoron because to not be "hardcoded", you need to consider more than one day and you can never assume that no one will work through midnight.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Jeff Moden (8/20/2015)
Really, really looks like and sounds like homework. Even if it's not, you should at least try. And rules 4, 5, and 6 create an oxymoron because to not be "hardcoded", you need to consider more than one day and you can never assume that no one will work through midnight.

Your intuition is uncanny, Jeff. I don't mind assisting, but actually doing someone's coursework galls. Student alert!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

dudekula.kareemulla (8/19/2015)
Hi Thanks for your reply ,,,,
I got your point but here situation may be swipe-in and swipe-out machines are separate and
new joiners may be confused and done instead of swipe-in they done swipe-out like that mistakes happen. in this situation data entry happen in table like this
and also this query execution data base is SQL2008.

You have a solution which you have marked as correct. Since we now know you are a student, are you not interested in how the solution works? What will you do if your courseleader asks you how it works?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden