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
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
DROP TABLE #T
GOCREATE 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' 附近有语法错误。