按天统计打卡时间

[snlixing]

ID 员工ID 上班日期 上班打卡时间 下班打卡时间

1 1 2019-5-1 2019-5-1 07:45 2019-5-1 17:45
2 2 2019-5-1 2019-5-1 07:55 2019-5-1 17:42
3 3 2019-5-1 2019-5-1 07:49 2019-5-1 17:47
4 1 2019-5-2 2019-5-2 07:45 2019-5-1 17:45
5 2 2019-5-2 2019-5-2 18:55 2019-5-3 07:42


得到如下表：


员工ID 1 2 3 4 。。。。。。。。。。。31
1 07:45 07:45
17:45 17:45
2 07:55 18:55
17:42 07:42
3 07:49
17:47

[RINK_1]

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT IDENTITY(1,1),
 EMP_ID VARCHAR(5),
 WORK_DATE DATETIME,
 BEGIN_DATE DATETIME,
 END_DATE DATETIME)

INSERT INTO #T
SELECT '1','2019-5-1','2019-5-1 07:45','2019-5-1 17:45' UNION ALL
SELECT '2','2019-5-1','2019-5-1 07:55','2019-5-1 17:42' UNION ALL
SELECT '3','2019-5-1','2019-5-1 07:49','2019-5-1 17:47' UNION ALL
SELECT '1','2019-5-2','2019-5-2 07:45','2019-5-2 17:45' UNION ALL
SELECT '2','2019-5-2','2019-5-2 18:55','2019-5-3 07:42' 

WITH CTE
AS
(SELECT ID,EMP_ID,WORK_DATE,BEGIN_DATE,'IN' AS TYPE,DATEPART(D,WORK_DATE) AS WORK_DAY FROM #T
UNION ALL
SELECT ID,EMP_ID,WORK_DATE,END_DATE,'OUT' AS TYPE,DATEPART(D,WORK_DATE) AS WORK_DAY FROM #T)



SELECT CASE WHEN TYPE='IN' THEN EMP_ID ELSE '' END AS EMP_ID_NEW,TYPE,MAX(CONVERT(VARCHAR(5),CASE WHEN WORK_DAY=1 THEN BEGIN_DATE ELSE NULL END,108)) AS DAY_1,
MAX(CONVERT(VARCHAR(5),CASE WHEN WORK_DAY=2 THEN BEGIN_DATE ELSE NULL END,108)) AS DAY_2
FROM CTE
GROUP BY EMP_ID,TYPE
ORDER BY EMP_ID,TYPE

[snlixing]

引用 1 楼 RINK_1 的回复:

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT IDENTITY(1,1),
 EMP_ID VARCHAR(5),
 WORK_DATE DATETIME,
 BEGIN_DATE DATETIME,
 END_DATE DATETIME)

INSERT INTO #T
SELECT '1','2019-5-1','2019-5-1 07:45','2019-5-1 17:45' UNION ALL
SELECT '2','2019-5-1','2019-5-1 07:55','2019-5-1 17:42' UNION ALL
SELECT '3','2019-5-1','2019-5-1 07:49','2019-5-1 17:47' UNION ALL
SELECT '1','2019-5-2','2019-5-2 07:45','2019-5-2 17:45' UNION ALL
SELECT '2','2019-5-2','2019-5-2 18:55','2019-5-3 07:42' 

WITH CTE
AS
(SELECT ID,EMP_ID,WORK_DATE,BEGIN_DATE,'IN' AS TYPE,DATEPART(D,WORK_DATE) AS WORK_DAY FROM #T
UNION ALL
SELECT ID,EMP_ID,WORK_DATE,END_DATE,'OUT' AS TYPE,DATEPART(D,WORK_DATE) AS WORK_DAY FROM #T)



SELECT CASE WHEN TYPE='IN' THEN EMP_ID ELSE '' END AS EMP_ID_NEW,TYPE,MAX(CONVERT(VARCHAR(5),CASE WHEN WORK_DAY=1 THEN BEGIN_DATE ELSE NULL END,108)) AS DAY_1,
MAX(CONVERT(VARCHAR(5),CASE WHEN WORK_DAY=2 THEN BEGIN_DATE ELSE NULL END,108)) AS DAY_2
FROM CTE
GROUP BY EMP_ID,TYPE
ORDER BY EMP_ID,TYPE

大神，有错误提示
消息 102，级别 15，状态 1，第 16 行
'CTE' 附近有语法错误。

[q159846]

在with上一行后面加个分号SELECT '2','2019-5-2','2019-5-2 18:55','2019-5-3 07:42';