sql – 计算出勤率的时差
发布时间:2021-03-30 22:03:31 所属栏目:MsSql教程 来源:网络整理
导读:我有一个表格,下面的示例输出. UserID Checktime CheckStatus3175 2013-12-22 07:02:10.000 I3175 2013-12-22 13:01:01.000 O3175 2013-12-22 13:49:54.000 I3175 2013-12-22 13:49:55.000 I3175 2013-12-22 15:58:42.000 O3175 2013-12-23 06:02:58.000 I317
我有一个表格,下面的示例输出. UserID Checktime CheckStatus 3175 2013-12-22 07:02:10.000 I 3175 2013-12-22 13:01:01.000 O 3175 2013-12-22 13:49:54.000 I 3175 2013-12-22 13:49:55.000 I 3175 2013-12-22 15:58:42.000 O 3175 2013-12-23 06:02:58.000 I 3175 2013-12-23 14:00:29.000 O 3175 2013-12-24 05:17:09.000 I 3175 2013-12-24 12:34:25.000 O 3175 2013-12-24 12:34:26.000 O 我想构建一个查询来实现以下结果: UserID Date CheckIn CheckOut Hours 3175 2013-12-22 07:02:10 13:01:0 5.98 3175 2013-12-22 13:49:54 15:58:42 2.15 注意: 我需要tsql查询的帮助才能获得这些结果. 我当前的代码导致了许多其他问题 – 因为每次都必须在临时表中重新计算. 解决方法试试这个 –DECLARE @temp TABLE ( UserID INT,Checktime DATETIME,CheckStatus CHAR(1) ) INSERT INTO @temp (UserID,Checktime,CheckStatus) VALUES (3175,'20131222 07:02:10.000','I'),(3175,'20131222 13:01:01.000','O'),'20131222 13:49:54.000','20131222 13:49:55.000','20131222 15:58:42.000','20131223 06:02:58.000','20131223 14:00:29.000','20131224 05:17:09.000','20131224 12:34:25.000','20131224 12:34:26.000','O') SELECT t.UserID,[Date] = DATEADD(dd,DATEDIFF(dd,t.CheckIn)),CheckIn = CONVERT(VARCHAR(10),t.CheckIn,108),CheckOut = CONVERT(VARCHAR(10),t.CheckOut,[Hours] = CAST(DATEDIFF(MINUTE,t.CheckOut) / 60. AS DECIMAL(10,2)) FROM ( SELECT t.UserID,CheckIn = t.Checktime,CheckOut = r.Checktime,RowNum = ROW_NUMBER() OVER (PARTITION BY t.UserID,r.Checktime ORDER BY 1/0) FROM @temp t OUTER APPLY ( SELECT TOP 1 * FROM @temp t2 WHERE t2.UserID = t.UserID AND t2.Checktime > t.Checktime AND DATEADD(dd,t.Checktime)) = DATEADD(dd,t2.Checktime)) AND t2.CheckStatus = 'O' ORDER BY t2.Checktime ) r WHERE t.CheckStatus = 'I' ) t WHERE t.RowNum = 1 输出 – UserID Date CheckIn CheckOut Hours ----------- ----------------------- ---------- ---------- -------- 3175 2013-12-22 00:00:00.000 07:02:10 13:01:01 5.98 3175 2013-12-22 00:00:00.000 13:49:54 15:58:42 2.15 3175 2013-12-23 00:00:00.000 06:02:58 14:00:29 7.97 3175 2013-12-24 00:00:00.000 05:17:09 12:34:25 7.28 (编辑:上饶站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |