Sql 2012 - find first in and last OUT for employees

Question

0.00/5 (No votes)

See more:

, +

I am trying to create a query in SQL Server 2012 to calculate time difference between First IN & Last OUT swipes for each employee(for normal day shift and night shift) in the company.

Requirement are:

Calculate regular shift punch First in / Last out
Calculate night shift punch First in / Last out
Check regular shift with missing punch out - If no OUT punch mark as NULL
Check regular shift with missing punch in - If no IN punch mark as NULL
Check night shift with missing punch out - If no OUT punch mark as NULL
Check night shift with missing punch in - If no IN punch mark as NULL
Calculate time difference between First Punch IN and Last OUT

I do not have any fixed shift timings for each employee and want to check if time difference between punches is more than threshold (15 hours) then treat OUT as missed punch or else calculate hours difference normally.

Current Results:

C++

EmpName	InDate	OutDate	WorkTime
User1	NULL	2015-07-28 21:05:32.000	NULL
User1	2015-07-20 11:07:29.000	2015-07-20 21:13:27.000	10:05:58
User1	2015-07-21 12:07:03.000	2015-07-21 21:04:02.000	08:56:59
User1	2015-07-22 11:48:06.000	NULL	NULL
User1	2015-07-22 13:57:58.000	2015-07-22 20:59:22.000	07:01:24
User1	2015-07-23 12:38:41.000	2015-07-23 21:03:13.000	08:24:32
User1	2015-07-24 11:51:03.000	NULL	NULL
User1	2015-07-24 14:36:55.000	2015-07-24 20:51:06.000	06:14:11
User1	2015-07-27 12:10:54.000	2015-07-27 21:04:47.000	08:53:53
User1	2015-07-28 11:24:09.000	NULL	NULL
User1	2015-07-29 12:03:09.000	2015-07-29 21:00:28.000	08:57:19
User1	2015-07-30 11:58:43.000	NULL	NULL
User1	2015-07-30 13:25:02.000	2015-07-30 21:00:20.000	07:35:18
User1	2015-07-31 12:11:36.000	2015-07-31 21:34:07.000	09:22:31
User2	2016-06-20 23:03:33.000	2016-06-21 02:36:38.000	03:33:05
User2	2016-06-21 17:02:29.000	2016-06-21 23:35:25.000	06:32:56
User2	2016-06-22 17:42:01.000	2016-06-23 02:47:18.000	09:05:17

The above results are not reflecting correctly as it should. Not sure what to do further to fix the issue. You will be able to recognize the issue with result once you run the below query.

Expected Results:

C++

EmpName	InDate	OutDate	WorkTime
User1	2015-07-20 11:07:29	2015-07-20 21:13:27	10:05:58
User1	2015-07-21 12:07:03	2015-07-21 21:04:02	8:56:59
User1	2015-07-22 11:48:06	2015-07-22 20:59:22	9:11:16
User1	2015-07-23 12:38:41	2015-07-23 21:03:13	8:24:32
User1	2015-07-24 11:51:03	2015-07-24 20:51:06	9:00:03
User1	2015-07-27 12:10:54	2015-07-27 21:04:47	8:53:53
User1	2015-07-28 11:24:09	2015-07-28 21:05:32	9:41:23
User1	2015-07-29 12:03:09	2015-07-29 21:00:28	8:57:19
User1	2015-07-30 11:58:43	2015-07-30 21:00:20	9:01:37
User1	2015-07-31 12:11:36	2015-07-31 21:34:07	9:22:31
User2	2016-06-20 23:03:33	2016-06-21 02:36:38	3:33:05
User2	2016-06-21 17:02:29	2016-06-21 23:35:25	6:32:56
User2	2016-06-22 17:42:01	2016-06-23 02:47:18	9:05:17
User2	2016-06-27 11:05:11	NULL	NULL
User2	NULL	2016-06-30 18:25:34	NULL

I hope the will help to understand the requirements and expected results and the point where I am stuck at the moment.
Please help me to complete this task as it has become biggest challenge to complete.

What I have tried:

SQL

Create table #TempData  (EmpName nvarchar(50),EventDateTime DateTime, TrnName nvarchar(20),TrnCode int)

Insert Into #TempData Values
 ('User1','2015-07-20 11:07:29','Entrance','0'),
 ('User1','2015-07-20 11:08:09','Exit','1'),
 ('User1','2015-07-20 21:13:27','Exit','1'),
 ('User1','2015-07-21 12:07:03','Entrance','0'),
 ('User1','2015-07-21 21:04:02','Exit','1'),
 ('User1','2015-07-22 11:48:06','Entrance','0'),
 ('User1','2015-07-22 13:37:15','Exit','1'),
 ('User1','2015-07-22 13:57:58','Entrance','0'),
 ('User1','2015-07-22 20:59:22','Exit','1'),
 ('User1','2015-07-23 12:38:41','Entrance','0'),
 ('User1','2015-07-23 17:33:43','Exit','1'),
 ('User1','2015-07-23 18:09:13','Entrance','0'),
 ('User1','2015-07-23 21:03:13','Exit','1'),
 ('User1','2015-07-24 11:51:03','Entrance','0'),
 ('User1','2015-07-24 14:19:41','Exit','1'),
 ('User1','2015-07-24 14:36:55','Entrance','0'),
 ('User1','2015-07-24 20:51:06','Exit','1'),
 ('User1','2015-07-27 12:10:54','Entrance','0'),
 ('User1','2015-07-27 17:45:36','Exit','1'),
 ('User1','2015-07-27 18:36:24','Entrance','0'),
 ('User1','2015-07-27 19:16:21','Exit','1'),
 ('User1','2015-07-27 20:01:12','Entrance','0'),
 ('User1','2015-07-27 21:04:47','Exit','1'),
 ('User1','2015-07-28 11:24:09','Entrance','0'),
 ('User1','2015-07-28 21:05:32','Exit','1'),
 ('User1','2015-07-29 12:03:09','Entrance','0'),
 ('User1','2015-07-29 18:36:07','Exit','1'),
 ('User1','2015-07-29 19:25:16','Entrance','0'),
 ('User1','2015-07-29 21:00:28','Exit','1'),
 ('User1','2015-07-30 11:58:43','Entrance','0'),
 ('User1','2015-07-30 13:09:18','Exit','1'),
 ('User1','2015-07-30 13:25:02','Entrance','0'),
 ('User1','2015-07-30 21:00:20','Exit','1'),
 ('User1','2015-07-31 12:11:36','Entrance','0'),
 ('User1','2015-07-31 19:46:47','Exit','1'),
 ('User1','2015-07-31 20:44:27','Entrance','0'),
 ('User1','2015-07-31 21:34:07','Exit','1'),
 ('User2','2016-06-20 23:03:33','Entrance','0'),
 ('User2','2016-06-21 02:36:38','Exit','1'),
 ('User2','2016-06-21 17:02:29','Entrance','0'),
 ('User2','2016-06-21 17:27:03','Entrance','0'),
 ('User2','2016-06-21 19:11:24','Exit','1'),
 ('User2','2016-06-21 19:24:41','Entrance','0'),
 ('User2','2016-06-21 23:35:25','Exit','1'),
 ('User2','2016-06-21 23:57:03','Entrance','0'),
 ('User2','2016-06-22 17:27:00','Exit','1'),
 ('User2','2016-06-22 17:42:01','Entrance','0'),
 ('User2','2016-06-22 19:37:43','Exit','1'),
 ('User2','2016-06-22 21:27:35','Entrance','0'),
 ('User2','2016-06-22 21:27:59','Exit','1'),
 ('User2','2016-06-22 21:45:47','Exit','1'),
 ('User2','2016-06-22 21:56:15','Entrance','0'),
 ('User2','2016-06-23 00:42:44','Exit','1'),
 ('User2','2016-06-23 01:03:06','Entrance','0'),
 ('User2','2016-06-23 02:47:18','Exit','1'),
 ('User2','2016-06-27 11:05:11','Entrance','0'),
 ('User2','2016-06-30 18:25:34','Exit','1')

The query:

;WITH CTE1
AS
(
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY CAST(T.EventDateTime AS DATE)) AS RowId
    FROM
        @TempData T
), CTE2
AS
(
        SELECT 
            A.EmpName,
            A.EventDateTime,
            A.TrnName,
            A.TrnCode,
            DENSE_RANK() OVER (ORDER BY MIN(B.RowId)) [Group]
        FROM 
            CTE1 A CROSS JOIN CTE1 B
        WHERE 
            ABS(DATEDIFF(HOUR, A.EventDateTime, B.EventDateTime)) BETWEEN 0 AND 14 
        GROUP BY 
                A.EmpName,
                A.EventDateTime,
                A.TrnName,
                A.TrnCode
), CTE3
AS
(
    SELECT
        T.EmpName,      
        MIN(IIF(T.TrnCode = 0, T.EventDateTime, NULL)) InDate,
        MAX(IIF(T.TrnCode = 1, T.EventDateTime, NULL)) OutDate
    FROM
        CTE2 T
    GROUP BY
        T.EmpName,
        T.[Group]
), FinalTable
AS
(
    SELECT
        T.EmpName ,
        T.InDate,
        IIF(T.InDate > T.OutDate, NULL, T.OutDate) AS OutDate
    FROM CTE3 T 

    UNION

    SELECT
        T.EmpName ,
        IIF(T.InDate > T.OutDate, NULL, T.InDate) AS InDate,
        T.OutDate AS OutDate
    FROM CTE3 T 
)


SELECT
    F.EmpName ,
    F.InDate ,
    F.OutDate,
    DATEDIFF(SECOND, F.InDate, F.OutDate) [Second],
    CONVERT(CHAR(8),DATEADD(SECOND,DATEDIFF(SECOND,F.InDate,F.OutDate),'1900-1-1'),8) WorkTime
FROM 
    FinalTable F

Posted 30-Sep-16 7:10am

AnkurJain14

Updated 30-Sep-16 8:58am

v4

Add a Solution

Comments

[no name] 30-Sep-16 13:19pm

OMG! Kudos to you! Formatted input data? Formatted query where you have actually tried yourself? Only thing that is missing is a clearly defined problem, "not reflecting correctly as it should". An example of your output would help. But what a relief to see an actually, almost there, well constructed question compared to the flotsam and jetsam we usually see. Hope you get an answer.

AnkurJain14 30-Sep-16 13:25pm

Thanks, the actual problem with my code is that I am not able to get the results align. For example: For a First IN punch the query is populating the cell with IN time however the corresponding OUT time is not actual LAST OUT time. which is quiet frustrating as i have been working on this side project from a very long time. Just observe the screen shot in the given link and you will understand the problem.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

David_Wimbley · Answer 1 · 2016-09-30T07:25:00

So here is one way of getting your results. Thanks again for posting schema and sample data. Made this very easy to help you.

So for these two items, should be the DiffHours column that provides this data

Quote:
Calculate regular shift punch First in / Last out
Calculate night shift punch First in / Last out

For these columns below, Use the PunchStatus column

Quote:
Check regular shift with missing punch out - If no OUT punch mark as NULL
Check regular shift with missing punch in - If no IN punch mark as NULL
Check night shift with missing punch out - If no OUT punch mark as NULL
Check night shift with missing punch in - If no IN punch mark as NULL

Use the Diffhours column for this as well.

Quote:
Calculate time difference between First Punch IN and Last OUT

Then the last note for the one below, use the ThresholdAnalyzation column.

Quote:
I do not have any fixed shift timings for each employee and want to check if time difference between punches is more than threshold (15 hours) then treat OUT as missed punch or else calculate hours difference normally.

SQL

WITH TimeClock (EmpName,
	TrnName,
	TrnCode,CurrentDate, PriorDate,PriorTransaction) AS  ( 
SELECT 
	EmpName,
	TrnName,
	TrnCode,
	A.EventDateTime AS CurrentDate, 
	(SELECT TOP 1 B.EventDateTime FROM #TempData AS B WHERE B.EventDateTime < A.EventDateTime AND A.EmpName  = B.EmpName ORDER BY B.EventDateTime DESC) AS PriorDate,
	(SELECT TOP 1 B.TrnName FROM #TempData AS B WHERE B.EventDateTime < A.EventDateTime AND A.EmpName  = B.EmpName ORDER BY B.EventDateTime DESC) AS PriorTransaction
FROM #TempData AS A)

SELECT 
	EmpName,
	TrnName,
	TrnCode,
	CurrentDate, 
	PriorDate,
	PriorTransaction,
	--Anaylzing
	CASE 
		WHEN TimeClock.PriorTransaction = TimeClock.TrnName AND TimeClock.PriorTransaction = 'EXIT' THEN 'MISSED PUNCH OUT'
		WHEN TimeClock.PriorTransaction = TimeClock.TrnName AND TimeClock.PriorTransaction = 'ENTRANCE' THEN 'MISSED PUNCH IN'
		ELSE '' END AS PunchStatus,
	DATEDIFF(HOUR, TimeClock.PriorDate, TimeClock.CurrentDate) AS DiffHours,

	CASE WHEN DATEDIFF(HOUR, TimeClock.PriorDate, TimeClock.CurrentDate) > 15 THEN 'TREAT AS OUT' ELSE 'CALC NORMALLY' END AS ThresholdAnalyzation
FROM TimeClock ORDER BY TimeClock.CurrentDate

This may not be the exact answer but it provides all the data you should need to get your answers.

Wendelius · Answer 2 · 2016-09-30T08:58:00

Sorry, completely misunderstood your requirement. Thanks for the new example data.

Perhaps something like the following? It doesn't calculate the working time, since it's easy to add if the data is otherwise correct?

SQL

with boundaries as (
 select  *
 from #TempData td1
 where td1.trncode = 0
 and  not exists (select 1 
                   from #TempData td2
				   where td2.empname = td1.empname
				   and   td2.trncode = td1.trncode
				   and   td2.EventDateTime < td1.EventDateTime
				   and   td2.EventDateTime > dateadd(hour, -15, td1.EventDateTime))
union
select * 
from #TempData td1
where  td1.trncode = 1
and not exists (select 1 
                   from #TempData td2
				   where td2.empname = td1.empname
				   and   td2.trncode = td1.trncode
				   and   td2.EventDateTime > td1.EventDateTime
				   and   td2.EventDateTime < dateadd(hour, 15, td1.EventDateTime))
union
select * 
from #TempData td1
where  not exists (select 1 
                   from #TempData td2
				   where td2.empname = td1.empname
				   and   td2.EventDateTime < td1.EventDateTime)
union
select * 
from #TempData td1
where  not exists (select 1 
                   from #TempData td2
				   where td2.empname = td1.empname
				   and   td2.EventDateTime > td1.EventDateTime)
)
select b1.empname,b1.EventDateTime, b2.EventDateTime
from boundaries b1, boundaries b2
where b1.TrnCode = 0
and b2.EmpName = b1.EmpName
and b2.trncode = 1
and b2.EventDateTime > b1.EventDateTime
and b2.EventDateTime < DATEADD(hour, 15, b1.EventDateTime)
union all
select b1.empname,b1.EventDateTime, null
from boundaries b1
where b1.TrnCode = 0
and not exists (select 1 
                  from boundaries b2
                  where b2.EmpName = b1.EmpName
                  and b2.trncode = 1
                  and b2.EventDateTime > b1.EventDateTime
                  and b2.EventDateTime < DATEADD(hour, 15, b1.EventDateTime))
union all
select b1.empname,null, b1.EventDateTime
from boundaries b1
where b1.TrnCode = 1
and not exists (select 1 
                  from boundaries b2
                  where b2.EmpName = b1.EmpName
                  and b2.trncode = 0
                  and b2.EventDateTime < b1.EventDateTime
                  and b2.EventDateTime > DATEADD(hour, -15, b1.EventDateTime))

The results I see are

User1	2015-07-20 11:07:29.000	2015-07-20 21:13:27.000
User1	2015-07-21 12:07:03.000	2015-07-21 21:04:02.000
User1	2015-07-22 11:48:06.000	2015-07-22 20:59:22.000
User1	2015-07-23 12:38:41.000	2015-07-23 21:03:13.000
User1	2015-07-24 11:51:03.000	2015-07-24 20:51:06.000
User1	2015-07-27 12:10:54.000	2015-07-27 21:04:47.000
User1	2015-07-28 11:24:09.000	2015-07-28 21:05:32.000
User1	2015-07-29 12:03:09.000	2015-07-29 21:00:28.000
User1	2015-07-30 11:58:43.000	2015-07-30 21:00:20.000
User1	2015-07-31 12:11:36.000	2015-07-31 21:34:07.000
User2	2016-06-20 23:03:33.000	2016-06-21 02:36:38.000
User2	2016-06-21 17:02:29.000	2016-06-21 23:35:25.000
User2	2016-06-22 17:42:01.000	2016-06-23 02:47:18.000
User2	2016-06-27 11:05:11.000	NULL
User2	NULL	                2016-06-30 18:25:34.000

Sql 2012 - find first in and last OUT for employees

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0