Click here to Skip to main content
15,171,993 members
Please Sign up or sign in to vote.
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
Updated 30-Sep-16 9:58am
v4
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.

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.
   
Comments
AnkurJain14 30-Sep-16 13:39pm
   
Hi David, Thanks for the quick response but the results are not what I am looking for. For your assistance, please refer the below link for the results I have at the moment and I am looking for little optimization in the code to fix the output.
http://i.stack.imgur.com/yvqby.png
Please refer the above and please help to come up with some logic to fix the results. I have updated the schema with more records to cover other scenarios as well.
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
   
v2
Comments
AnkurJain14 1-Oct-16 8:14am
   
Thanks Mika. It works for the data I have provided and I am currently testing the query for different data to cover all scenarios.
Wendelius 1-Oct-16 14:49pm
   
That's good to hear :)
AnkurJain14 4-Oct-16 15:07pm
   
Hi @Mika,
I am again struggling to get correct results from below data.
('UserX', 2015-08-06 10:05:49 ,'Entrance', '0'),
('UserX', 2015-08-06 18:03:05 ,'Exit', '1'),
('UserX', 2015-08-06 18:28:27 ,'Entrance', '0'),
('UserX', 2015-08-06 20:36:37 ,'Exit', '1'),
('UserX', 2015-08-07 07:53:34 ,'Entrance', '0'),
('UserX', 2015-08-07 07:55:12 ,'Entrance', '0'),
('UserX', 2015-08-07 08:17:35 ,'Exit', '1'),
('UserX', 2015-08-07 08:19:13 ,'Entrance', '0'),
('UserX', 2015-08-07 08:20:20 ,'Exit', '1'),
('UserX', 2015-08-07 08:26:53 ,'Entrance', '0'),
('UserX', 2015-08-07 08:28:49 ,'Exit', '1'),
('UserX', 2015-08-07 08:30:14 ,'Entrance', '0'),
('UserX', 2015-08-07 08:30:39 ,'Exit', '1'),
('UserX', 2015-08-07 08:43:07 ,'Exit', '1'),
('UserX', 2015-08-07 09:17:22 ,'Entrance', '0'),
('UserX', 2015-08-07 13:09:26 ,'Exit', '1'),
('UserX', 2015-08-07 14:00:27 ,'Entrance', '0'),
('UserX', 2015-08-07 17:15:16 ,'Exit', '1')

Using your query, following is the result for above data.
UserX 2015-08-06 10:05:49 NULL
UserX NULL 2015-08-07 17:15:16

I have tried to make changes in the dateadd condition you used to in each select query but it doesn't change the above result. The problem is that the CTE(top 2 select queries) is not picking the data based on the filters used. Could you please suggest something to solve this issue without impacting other data?

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900