我有一个表格,下面的示例输出.
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'),'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
