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