作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有一个包含以下示例输出的表格。
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
注意事项:1.忽略重复的IN。原始数据中的第三和第四行。2. 在小时计算中,分钟是小数点到小时。
我需要 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'),
(3175, '20131222 13:49:54.000', 'I'),
(3175, '20131222 13:49:55.000', 'I'),
(3175, '20131222 15:58:42.000', 'O'),
(3175, '20131223 06:02:58.000', 'I'),
(3175, '20131223 14:00:29.000', 'O'),
(3175, '20131224 05:17:09.000', 'I'),
(3175, '20131224 12:34:25.000', 'O'),
(3175, '20131224 12:34:26.000', 'O')
SELECT
t.UserID
, [Date] = DATEADD(dd, 0, DATEDIFF(dd, 0, t.CheckIn))
, CheckIn = CONVERT(VARCHAR(10), t.CheckIn, 108)
, CheckOut = CONVERT(VARCHAR(10), t.CheckOut, 108)
, [Hours] = CAST(DATEDIFF(MINUTE, t.CheckIn, 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, 0, DATEDIFF(dd, 0, t.Checktime)) = DATEADD(dd, 0, DATEDIFF(dd, 0, 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
关于sql - 计算考勤时差,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/20882934/
我正在为员工建立一个时间登记系统。我开始将时间保存为 mysql 中的“Datetime”。问题是,当我有两列日期时间时,注册时间只会填充一列,第二列(取决于登录或退出)设置为“0000-00-00
我正在制作一个小程序,员工可以在其中上下类。员工有一个员工 ID,必须使用该 ID 才能上下类。 计时没问题,但我的问题是员工可以多次计时而不会超时。 我想做的是,每当员工计时时,他/她就不允许再次计
我有一个文本框(用于员工 ID)和一个在单击时将员工 ID、当前日期和当前时间(不同列)保存到数据库的时间按钮。问题是我怎样才能将时间限制在每天一次。 这是我的代码: exec($sql);
根据下面给定的模式,我想在我的数据库中查询员工每天进出的时间。 注意:员工的工作时间很灵活,他们可以上夜类或白类(正如您在员工 #1 中看到的那样)。 架构:出勤表 | id | employee_i
我是一名优秀的程序员,十分优秀!