一定时间迟到的总时间考勤

最新推荐文章于 2025-02-25 20:47:34 发布

fhjzgsy

最新推荐文章于 2025-02-25 20:47:34 发布

阅读量249

点赞数

CC 4.0 BY-SA版权

分类专栏： sqlServer 文章标签： date insert table

本文链接：https://blog.youkuaiyun.com/fhjzgsy/article/details/4713075

sqlServer 专栏收录该内容

36 篇文章

订阅专栏

DECLARE @a TABLE(ID INT,DEPTCODE VARCHAR(20),[DATETIME] DATETIME)
INSERT @a SELECT 32,'财务部','2009-10-17 08:54:02.000'
UNION ALL SELECT 32,'财务部','2009-10-17 12:02:10.000'
UNION ALL SELECT 32,'财务部','2009-10-17 13:07:19.000'
UNION ALL SELECT 32,'财务部','2009-10-17 18:32:34.000'
UNION ALL SELECT 32,'财务部','2009-10-18 08:53:30.000'
UNION ALL SELECT 32,'财务部','2009-10-18 11:38:00.000'
UNION ALL SELECT 32,'财务部','2009-10-18 16:50:28.000'
UNION ALL SELECT 32,'财务部','2009-10-18 18:34:45.000'
UNION ALL SELECT 32,'财务部','2009-10-19 08:52:29.000'
UNION ALL SELECT 32,'财务部','2009-10-19 12:05:29.000'
UNION ALL SELECT 32,'财务部','2009-10-19 13:08:57.000'
UNION ALL SELECT 32,'财务部','2009-10-19 18:34:04.000'
UNION ALL SELECT 32,'财务部','2009-10-20 08:53:28.000'
UNION ALL SELECT 32,'财务部','2009-10-01 09:00:25.000'
UNION ALL SELECT 32,'财务部','2009-10-01 18:30:46.000'
UNION ALL SELECT 32,'财务部','2009-10-02 18:30:53.000'
UNION ALL SELECT 32,'财务部','2009-10-04 09:00:52.000'
UNION ALL SELECT 32,'财务部','2009-10-07 18:30:59.000'
UNION ALL SELECT 32,'财务部','2009-10-12 18:30:33.000'
UNION ALL SELECT 32,'办公室','2009-10-12 18:41:53.000'
UNION ALL SELECT 32,'办公室','2009-10-14 08:59:00.000'
UNION ALL SELECT 32,'办公室','2009-10-14 18:41:13.000'
UNION ALL SELECT 32,'办公室','2009-10-15 08:54:45.000'
UNION ALL SELECT 32,'办公室','2009-10-15 18:33:30.000'
UNION ALL SELECT 32,'办公室','2009-10-16 08:57:13.000'
UNION ALL SELECT 32,'办公室','2009-10-16 17:59:00.000'
UNION ALL SELECT 32,'办公室','2009-10-17 08:52:05.000'
UNION ALL SELECT 32,'办公室','2009-10-17 18:35:23.000'
UNION ALL SELECT 32,'办公室','2009-10-18 08:52:03.000'
UNION ALL SELECT 32,'办公室','2009-10-18 18:37:04.000'
UNION ALL SELECT 32,'办公室','2009-10-20 08:50:56.000'
UNION ALL SELECT 32,'办公室','2009-10-20 18:36:19.000'
UNION ALL SELECT 32,'办公室','2009-10-21 08:54:43.000'

SELECT ID, COUNT(DISTINCT [DATE]) DAYS,
ABS(SUM(CASE WHEN X<0 THEN X ELSE 0 END)) MINS
FROM (SELECT ID,DEPTCODE,CONVERT(VARCHAR(10),[DATETIME],120)
[DATE],[DATETIME],DATEDIFF(MINUTE,CONVERT(VARCHAR(10),
[DATETIME],120)+' 09:00:00.000',[DATETIME]) X
FROM @a WHERE CONVERT(VARCHAR(7),[DATETIME],120)='2009-10'
) AA
GROUP BY ID