• 主页
  • 基础类
  • 应用实例
  • 新技术前沿

按天统计打卡时间

snlixing 泰安鲁普耐特塑料有限公司 软件开发部负责人  2019-05-07 11:42:05
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
...全文
33 点赞 收藏 3
写回复
3 条回复
q159846 2019年05月07日
在with上一行后面加个分号SELECT '2','2019-5-2','2019-5-2 18:55','2019-5-3 07:42';
回复 点赞
snlixing 2019年05月07日
引用 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' 附近有语法错误。
回复 点赞
RINK_1 2019年05月07日


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
回复 点赞
发动态
发帖子
MS-SQL Server
创建于2007-09-28

1.4w+

社区成员

25.3w+

社区内容

MS-SQL Server相关内容讨论专区
社区公告
暂无公告