34,591
社区成员
发帖
与我相关
我的任务
分享
--测试数据
IF OBJECT_ID('#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab(
perID VARCHAR(20),
cardID VARCHAR(20),
dt DATETIME
)
INSERT INTO #tab
select '0001','2345','2017-09-01 5:00:01'UNION ALL
select '0002','2347','2017-09-01 5:03:01'UNION ALL
select '0003','2349','2017-09-01 6:00:01'UNION ALL
select '0004','2315','2017-09-01 8:00:01'UNION ALL
select '0005','2362','2017-09-01 9:00:01'UNION ALL
select '0006','2341','2017-09-01 9:01:01'UNION ALL
select '0007','2377','2017-09-01 9:03:01'UNION ALL
select '0006','2341','2017-09-01 15:00:01'UNION ALL
select '0002','2347','2017-09-01 15:03:01'UNION ALL
select '0001','2345','2017-09-01 16:00:01'UNION ALL
select '0004','2315','2017-09-01 18:00:01'UNION ALL
select '0005','2362','2017-09-01 19:00:01'UNION ALL
select '0003','2341','2017-09-01 19:01:01'UNION ALL
select '0007','2377','2017-09-01 19:03:01'
--测试数据结束
SELECT perID,
cardID,
MIN(dt) AS 最早时间,
MAX(dt) AS 最晚时间,
DATEDIFF(hour, MIN(dt), MAX(dt)) AS 工作小时数
--INTO #tab--如果需要插入零时表
FROM #tab
GROUP BY
perID,
cardID,
CONVERT(VARCHAR(100), dt, 23)
perID cardID 最早时间 最晚时间 工作小时数
-------------------- -------------------- ----------------------- ----------------------- -----------
0001 2345 2017-09-01 05:00:01.000 2017-09-01 16:00:01.000 11
0002 2347 2017-09-01 05:03:01.000 2017-09-01 15:03:01.000 10
0003 2341 2017-09-01 19:01:01.000 2017-09-01 19:01:01.000 0
0003 2349 2017-09-01 06:00:01.000 2017-09-01 06:00:01.000 0
0004 2315 2017-09-01 08:00:01.000 2017-09-01 18:00:01.000 10
0005 2362 2017-09-01 09:00:01.000 2017-09-01 19:00:01.000 10
0006 2341 2017-09-01 09:01:01.000 2017-09-01 15:00:01.000 6
0007 2377 2017-09-01 09:03:01.000 2017-09-01 19:03:01.000 10
(8 行受影响)
/* 测试数据开始 */
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t(id VARCHAR(20),cardNo VARCHAR(30),d datetime)
INSERT INTO t(id,cardNo,d)
SELECT '0001','2345','2017-09-01 5:00:01'
UNION SELECT '0002','2347','2017-09-01 5:03:01'
UNION SELECT '0003','2349','2017-09-01 6:00:01'
UNION SELECT '0004','2315','2017-09-01 8:00:01'
UNION SELECT '0005','2362','2017-09-01 9:00:01'
UNION SELECT '0006','2341','2017-09-01 9:01:01'
UNION SELECT '0007','2377','2017-09-01 9:03:01'
UNION SELECT '0006','2341','2017-09-01 15:00:01'
UNION SELECT '0002','2347','2017-09-01 15:03:01'
UNION SELECT '0001','2345','2017-09-01 16:00:01'
UNION SELECT '0004','2315','2017-09-01 18:00:01'
UNION SELECT '0005','2362','2017-09-01 19:00:01'
UNION SELECT '0003','2341','2017-09-01 19:01:01'
UNION SELECT '0007','2377','2017-09-01 19:03:01'
/* 测试数据结束 */
SELECT id AS [员工ID]
,cardNo AS [卡号]
,MIN(d) AS [最早时间]
,MAX(d) AS [最晚时间]
,DATEDIFF(hour,MIN(d),MAX(d)) as [共工作了x小时]
FROM t
GROUP BY id,cardNo
ORDER BY id
select 员工id,
卡号,
min(刷卡时间) as 最早时间,
max(刷卡时间) as 最晚时间,
datediff(hh,min(刷卡时间),max(刷卡时间)) as 工作时间长度
from table
group by 员工id,卡号,