27,579
社区成员
发帖
与我相关
我的任务
分享
部门编号 部门名称 员工编号 员工姓名 刷脸时间
6 IT部 10143 李四 2018-07-26 07:26:13
6 IT部 10143 李四 2018-07-26 17:04:05
6 IT部 10143 李四 2018-07-27 07:19:15
6 IT部 10143 李四 2018-07-27 16:31:16
6 IT部 10143 李四 2018-07-28 07:21:35
6 IT部 10143 李四 2018-07-28 16:35:00
6 IT部 10143 李四 2018-07-30 07:24:24
6 IT部 10143 李四 2018-07-30 16:35:02
6 IT部 10143 李四 2018-07-31 06:55:04
6 IT部 10143 李四 2018-07-31 16:41:00
6 IT部 10143 李四 2018-08-01 07:17:36
6 IT部 10143 李四 2018-08-01 16:34:55
6 IT部 10143 李四 2018-08-02 07:17:57
6 IT部 10143 李四 2018-08-02 16:36:48
6 IT部 10143 李四 2018-08-03 07:20:04
6 IT部 10143 李四 2018-08-03 16:47:44
6 IT部 10143 李四 2018-08-06 07:27:50
6 IT部 10143 李四 2018-08-06 17:05:20
6 IT部 10143 李四 2018-08-08 07:25:19
6 IT部 10143 李四 2018-08-08 16:45:18
6 IT部 10143 李四 2018-08-09 07:19:58
6 IT部 10143 李四 2018-08-09 16:26:43
6 IT部 10143 李四 2018-08-10 07:19:59
6 IT部 10143 李四 2018-08-10 16:43:12
6 IT部 10143 李四 2018-08-11 07:22:22
6 IT部 10143 李四 2018-08-11 16:42:41
6 IT部 10143 李四 2018-08-14 07:35:11
员工编号 员工姓名 部门名称 考勤日期 考勤时间1 考勤时间2
10143 李四 IT部 2018-07-26 7:26:13 17:04:05
10143 李四 IT部 2018-07-27 7:19:15 16:31:16
10143 李四 IT部 2018-07-28 7:21:35 16:35:00
10143 李四 IT部 2018-07-30 7:24:24 16:35:02
10143 李四 IT部 2018-07-31 6:55:04 16:41:00
10143 李四 IT部 2018-08-01 7:17:36 16:34:55
10143 李四 IT部 2018-08-02 7:17:57 16:36:48
10143 李四 IT部 2018-08-03 7:20:04 16:47:44
10143 李四 IT部 2018-08-06 7:27:50 17:05:20
10143 李四 IT部 2018-08-08 7:25:19 16:45:18
10143 李四 IT部 2018-08-09 7:19:58 16:26:43
10143 李四 IT部 2018-08-10 7:19:59 16:43:12
10143 李四 IT部 2018-08-11 7:22:22 16:42:41
10143 李四 IT部 2018-08-14 7:35:11
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
部门编号 VARCHAR(10),
部门名称 NVARCHAR(20),
员工编号 VARCHAR(10),
员工姓名 NVARCHAR(20),
刷脸时间 DATETIME
)
--部门编号 部门名称 员工编号 员工姓名 刷脸时间
INSERT INTO t
SELECT '6','IT部','10143','李四','2018-07-26 07:26:13'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-26 17:04:05'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-27 07:19:15'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-27 16:31:16'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-27 13:31:15' --增加的
UNION ALL SELECT '6','IT部','10143','李四','2018-07-27 12:31:13' --增加的
UNION ALL SELECT '6','IT部','10143','李四','2018-07-28 07:21:35'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-28 16:35:00'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-30 07:24:24'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-30 16:35:02'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-31 06:55:04'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-31 16:41:00'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-01 07:17:36'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-01 16:34:55'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-02 07:17:57'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-02 16:36:48'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-03 07:20:04'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-03 16:47:44'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-06 07:27:50'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-06 17:05:20'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-08 07:25:19'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-08 16:45:18'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-09 07:19:58'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-09 16:26:43'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-10 07:19:59'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-10 16:43:12'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-11 07:22:22'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-11 16:42:41'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-14 07:35:11'
--以上为测试表及数据
;WITH cte AS (
SELECT
[部门编号],[部门名称],[员工编号],[员工姓名],CONVERT(CHAR(10),刷脸时间,120) AS [考勤日期]
, MIN(刷脸时间) AS [考勤时间1]
, CASE WHEN MIN(刷脸时间)=MAX(刷脸时间) THEN NULL ELSE MAX(刷脸时间) END AS [考勤时间2]
FROM t
GROUP BY [部门编号],[部门名称],[员工编号],[员工姓名],CONVERT(CHAR(10),刷脸时间,120)
)
SELECT *
,STUFF((SELECT ',' + CONVERT(CHAR(19),t.刷脸时间,120)
FROM t
WHERE t.[员工编号]=cte.[员工编号] AND t.刷脸时间> cte.[考勤时间1] AND t.刷脸时间<cte.[考勤时间2]
ORDER BY t.刷脸时间
FOR XML PATH('')
),1,1,'') AS [其它时间]
FROM cte
--部门编号 部门名称 员工编号 员工姓名 刷脸时间
WITH V_TMP AS (
SELECT '6' 部门编号,'IT部' 部门名称,'10143' 员工编号,'李四' 员工姓名,'2018-07-26 07:26:13' 刷脸时间
UNION ALL SELECT '6','IT部','10143','李四','2018-07-26 08:04:05'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-26 17:26:13'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-26 17:34:05'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-27 07:19:15'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-27 07:19:15'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-28 07:21:35'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-28 16:35:00'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-30 07:24:24'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-30 16:35:02'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-31 06:55:04'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-31 16:41:00'
--以上为测试表及数据
)
SELECT 部门编号,MAX(部门名称) 部门名称,员工编号,MAX(员工姓名) 员工姓名,
CASE WHEN DATEPART(HOUR,MIN(刷脸时间))<=12 THEN MIN(刷脸时间) END 上午打卡,
CASE WHEN DATEPART(HOUR,MAX(刷脸时间))>12 THEN MAX(刷脸时间) END 下午打卡
FROM V_TMP
GROUP BY 部门编号,员工编号,CONVERT(VARCHAR(10),刷脸时间,23)
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
部门编号 VARCHAR(10),
部门名称 NVARCHAR(20),
员工编号 VARCHAR(10),
员工姓名 NVARCHAR(20),
刷脸时间 DATETIME
)
--部门编号 部门名称 员工编号 员工姓名 刷脸时间
INSERT INTO t
SELECT '6','IT部','10143','李四','2018-07-26 07:26:13'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-26 17:04:05'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-27 07:19:15'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-27 16:31:16'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-28 07:21:35'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-28 16:35:00'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-30 07:24:24'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-30 16:35:02'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-31 06:55:04'
UNION ALL SELECT '6','IT部','10143','李四','2018-07-31 16:41:00'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-01 07:17:36'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-01 16:34:55'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-02 07:17:57'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-02 16:36:48'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-03 07:20:04'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-03 16:47:44'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-06 07:27:50'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-06 17:05:20'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-08 07:25:19'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-08 16:45:18'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-09 07:19:58'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-09 16:26:43'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-10 07:19:59'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-10 16:43:12'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-11 07:22:22'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-11 16:42:41'
UNION ALL SELECT '6','IT部','10143','李四','2018-08-14 07:35:11'
--以上为测试表及数据
SELECT
[部门编号],[部门名称],[员工编号],[员工姓名],CONVERT(CHAR(10),刷脸时间,120) AS [考勤日期]
, MIN(刷脸时间) AS [考勤时间1]
, CASE WHEN MIN(刷脸时间)=MAX(刷脸时间) THEN NULL ELSE MAX(刷脸时间) END AS [考勤时间2]
FROM t
GROUP BY [部门编号],[部门名称],[员工编号],[员工姓名],CONVERT(CHAR(10),刷脸时间,120)
ORDER BY 5