Sql 2012 - 为员工找到第一个和最后一个OUT [英] Sql 2012 - find first in and last OUT for employees

查看：87 发布时间：2019/6/11 3:55:28 SQL SQL-Server time

本文介绍了Sql 2012 - 为员工找到第一个和最后一个OUT的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试在SQL Server 2012中创建一个查询来计算First IN和&之间的时差。公司每位员工的最后一次刷卡（正常的白班和夜班）。

要求是：

计算常规换档打卡进/出最后输出

计算夜班打卡进/出最后一次

检查常规班次缺失打卡 - 如果没有OUT打卡标记为NULL

检查常规班次是否缺少打卡 - 如果没有IN打卡标记为空

检查夜班缺失打卡 - 如果没有OUT打卡标记为NULL

检查缺少打卡的夜班 - 如果没有IN打孔标记为空

计算第一个打孔IN和最后一个OUT之间的时差

我没有为每位员工设置任何固定班次时间，并且想要检查每次打卡之间的时差是否大于阈值（15小时），然后将OUT视为错误打卡或者通常计算小时差异。

当前结果：

 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  
 Us er1  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   -  < span class =code-digit> 07   -   23   21 ： 03 ： 13 。 000   08 ： 24 ： 32  
 User1  2015   -   07   -   24   11 ： 51 ： 03 。 000  NULL NULL 
 User1  2015   -   07   -  < span class =code-digit> 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 ：< span class =code-digit> 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  < span class =code-digit> 02 ： 47 ： 18 。 000   09 ： 05 ： 17

上述结果没有正确反映，因为它应该。不知道该怎么做才能解决这个问题。运行以下查询后，您将能够识别结果问题。

预期结果：

 EmpName InDate OutDate WorkTime 
 User1  2015   -   07   -   20   11 ： 07 ： 29   2015   -   07   -   20   21 ： 13 ： 27  < span class =code-digit> 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   -  < span class =code-digit> 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 ：< span class =code-digit> 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 ：< span class =code-digit> 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

我希望这将有助于理解要求和预期结果以及我目前陷入困境的地方。

请帮助我完成这项任务，因为它已经成为最大的挑战。

我尝试过：

<前lang =SQL > 创建表 #TempData（EmpName nvarchar （ 50 ），EventDateTime DateTime ，TrnName nvarchar （ 20 ），TrnCode int ）

插入进入 #TempData 值
（' User1'，' 2015-07-20 11:07:29'，' 入口' ，' 0'），
（' User1'，' 2015-07- 20 11:08:09'，' 退出'，' 1'），
（' User1'，' 2015-07-20 21:13:27'，' 退出'，' 1'），
（' User1'，' 2015-07-21 12:07:03'，' 入口'，' 0'），
（' User1'， ' 2015-07-21 21:04:02'，' 退出'，' 1'），
（' User1'，' 2015-07-22 11:48:06'，' 入口'，' 0'），
（' User1'，' 2015- 07-22 13:37:15'，' 退出'，' 1'），
（' User1'，' 2015-07-22 13:57： 58'，' 入口'，' 0'），
（' User1'，' 2015-07-22 20:59:22'，' 退出'，' 1'），
（' User1'， ' 2015-07-23 12:38:41'，' 入口'，' 0' ），
（' User1'，' 2015-07-23 17:33:43'，' 退出'，' 1'），
（' User1'，' 2015-07-23 18:09:13'，' 入口，' 0'），
（' User1'，' 2015 -07-23 21:03:13'，' 退出'，' 1'），
（' User1'，' 2015-07-24 11:51 ：03'，' 进入'， ' 0'），
（' User1'，' 2015-07-24 14:19:41' ，' 退出'，' 1'），
（' User1'，' 2015-07-24 14:36:55'，' 入口'，' 0'），
（' User1'，' 2015-07-24 20:51:06'，' 退出'，' 1' ），
（' User1'，' 2015-07-27 12:10:54'，' 入口'，' 0'），
（' User1'，' 2015-07-27 17:45:36'，' 退出' ，' 1'），
（' User1'，' 2015-07- 27 18:36:24'，' 进入'，' 0'），
（' User1'，' 2015-07-27 19:16:21' ，' 退出'，' 1'），
（' User1' ，' 2015-07-27 20:01:12'，入口， 0'），
（' User1'，' 2015-07-27 21:04:47'，' 退出'，' 1'），
（' User1'，' 2015-07-28 11:24:09'，' 入口， 0'），
（ ' User1'，' 2015-07-28 21:05:32'，' 退出' ，' 1'），
（' User1'，' 2015-07-29 12:03:09'，' 进入'，' 0'），
（'' User1'，' 2015-07-29 18： 36:07'，' 退出'， 1'），
（' User1'，' 2015-07-29 19:25:16'， ' 进入'，' 0'），
（' User1'，' 2015-07-29 21:00:28'，' 退出'，' 1'），
（' User1'，' 2015-07-30 11:58:43'，' 入口'，' 0'），
（' User1'，' 2015-07-30 13:09:18'，' 退出'，' 1'），
（' User1'，' 2015-07-30 13:25:02'，' 进入'，' 0'），
（' User1'，' 2015-07-30 21 ：00：20'，' 退出'，' 1'），
（' User1'，' 2015-07-31 12:11:36' ，' 入口'，' 0'），
（' User1'，' 2015-07-31 19:46:47'，' 退出'，' 1'），
（' User1'，' 2015-07-31 20:44:27'，' 入口'，' 0' ），
（' User1'，' 2015-07-31 21:34:07'，' 退出'，' 1'），
（' User2'，' 2016-06-20 23:03:33'，' 入口，' 0'），
（' User2'，' 2016 -06-21 02:36:38'，' 退出'，'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)) [G roup] 
         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

解决方案

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.
WITH TimeClock (EmpName, 
 \tTrnName, 
 \tTrnCode,CurrentDate, PriorDate,PriorTransaction) AS ( 
 SELECT 
 \tEmpName, 
 \tTrnName, 
 \tTrnCode, 
 \tA.EventDateTime AS CurrentDate, 
 \t(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, 
 \t(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 
 \tEmpName, 
 \tTrnName, 
 \tTrnCode, 
 \tCurrentDate, 
 \tPriorDate, 
 \tPriorTransaction, 
 \t--Anaylzing 
 \tCASE 
 \t\tWHEN TimeClock.PriorTransaction = TimeClock.TrnName AND TimeClock.PriorTransaction = 'EXIT' THEN 'MISSED PUNCH OUT' 
 \t\tWHEN TimeClock.PriorTransaction = TimeClock.TrnName AND TimeClock.PriorTransaction = 'ENTRANCE' THEN 'MISSED PUNCH IN' 
 \t\tELSE '' END AS PunchStatus, 
 \tDATEDIFF(HOUR, TimeClock.PriorDate, TimeClock.CurrentDate) AS DiffHours, 
 
 \tCASE 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.

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?

with boundaries as ( 
  select  * 
  from #TempData td1 
  where td1.trncode = 0 
  and  not exists (select 1  
                    from #TempData td2 
 \t\t\t\t   where td2.empname = td1.empname 
 \t\t\t\t   and   td2.trncode = td1.trncode 
 \t\t\t\t   and   td2.EventDateTime < td1.EventDateTime 
 \t\t\t\t   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 
 \t\t\t\t   where td2.empname = td1.empname 
 \t\t\t\t   and   td2.trncode = td1.trncode 
 \t\t\t\t   and   td2.EventDateTime > td1.EventDateTime 
 \t\t\t\t   and   td2.EventDateTime < dateadd(hour, 15, td1.EventDateTime)) 
 union 
 select *  
 from #TempData td1 
 where  not exists (select 1  
                    from #TempData td2 
 \t\t\t\t   where td2.empname = td1.empname 
 \t\t\t\t   and   td2.EventDateTime < td1.EventDateTime) 
 union 
 select *  
 from #TempData td1 
 where  not exists (select 1  
                    from #TempData td2 
 \t\t\t\t   where td2.empname = td1.empname 
 \t\t\t\t   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.t rncode = 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\t2015-07-20 11:07:29.000\t2015-07-20 21:13:27.000 
 User1\t2015-07-21 12:07:03.000\t2015-07-21 21:04:02.000 
 User1\t2015-07-22 11:48:06.000\t2015-07-22 20:59:22.000 
 User1\t2015-07-23 12:38:41.000\t2015-07-23 21:03:13.000 
 User1\t2015-07-24 11:51:03.000\t2015-07-24 20:51:06.000 
 User1\t2015-07-27 12:10:54.000\t2015-07-27 21:04:47.000 
 User1\t2015-07-28 11:24:09.000\t2015-07-28 21:05:32.000 
 User1\t2015-07-29 12:03:09.000\t2015-07-29 21:00:28.000 
 User1\t2015-07-30 11:58:43.000\t2015-07-30 21:00:20.000 
 User1\t2015-07-31 12:11:36.000\t2015-07-31 21:34:07.000 
 User2\t2016-06-20 23:03:33.000\t2016-06-21 02:36:38.000 
 User2\t2016-06-21 17:02:29.000\t2016-06-21 23:35:25.000 
 User2\t2016-06-22 17:42:01.000\t2016-06-23 02:47:18.000 
 User2\t2016-06-27 11:05:11.000\tNULL 
 User2\tNULL\t                2016-06-30 18:25:34.000

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

解决方案

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

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?

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 - 为员工找到第一个和最后一个OUT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

Sql 2012 - 为员工找到第一个和最后一个OUT [英] Sql 2012 - find first in and last OUT for employees

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录关闭

Sql 2012 - 为员工找到第一个和最后一个OUT [英] Sql 2012 - find first in and last OUT for employees

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录 关闭

登录关闭