根据需要查询以获取输出
根据需要查询以获取输出
提问于 2021-09-28 07:55:54
我有一个包含以下示例输出的表格。
ID_Emp| Name | Date
11 |Jonh |14/05/2014 8:16
11 |Jonh |14/05/2014 13:35
11 |Jonh |14/05/2014 17:23
11 |Jonh |14/05/2014 21:09
12 |Elizabe |14/05/2014 14:06
12 |Elizabe |14/05/2014 20:39
12 |Elizabe |14/05/2014 21:39
12 |Elizabe |14/05/2014 22:39
13 |Jimmy |14/05/2014 8:00
13 |Jimmy |14/05/2014 17:12
13 |Jimmy |14/05/2014 18:12我想构建一个查询来实现以下结果：
ID_Emp|Name |Date |TimeIn1 |TimeOut1||TimeIn2|TimeOut2|Hours
11 |Jonh |14/05/2014 |8:16 |13:35 |17:23 |21:09 |5:19
12 |Elizabe |14/05/2014 |14:06 |20:39 |21:39 |22:39 |8:33
13 |Jimmy |14/05/2014 |8:00 |17:12 |18:12 | - |9:12查询：
SELECT cio.emp_reader_id, cio.dt AS CheckIn,
cio.next_dt as CheckOut
FROM (SELECT cio.*,
ROW_NUMBER() OVER (PARTITION BY cio.emp_reader_id, CONVERT(date, cio.dt) ORDER BY cio.dt) as seqnum,
LEAD(cio.dt) OVER (PARTITION BY cio.emp_reader_id, CONVERT(date, cio.dt) ORDER BY cio.dt) as next_dt
FROM trnevents cio
) cio
WHERE seqnum % 2 = 1回答 2
Stack Overflow用户
发布于 2021-09-28 09:13:24
这里使用多个CTE进行计算。此查询适用于每个emp_id的每个日期。使用format函数转换日期和小时:分钟。
-- SQL Server
WITH cte AS (SELECT ID_Emp
, Name
, CAST(tdate AS date) actual_date
, tdate
, ROW_NUMBER() OVER (PARTITION BY ID_Emp, CAST(tdate AS date) ORDER BY tdate) row_num
FROM test
), cte2 AS (
SELECT ID_Emp
, MAX(Name) Name
, actual_date
, MAX(CASE WHEN row_num = 1 THEN tdate END) TimeIn1
, MAX(CASE WHEN row_num = 2 THEN tdate END) TimeOut1
, MAX(CASE WHEN row_num = 3 THEN tdate END) TimeIn2
, MAX(CASE WHEN row_num = 4 THEN tdate END) TimeOut2
FROM cte
GROUP BY ID_Emp, actual_date
)
SELECT ID_Emp, Name
, FORMAT(actual_date, 'dd/MM/yyyy') date
, FORMAT(TimeIn1, 'HH:mm') TimeIn1
, FORMAT(TimeOut1, 'HH:mm') TimeOut1
, FORMAT(TimeIn2, 'HH:mm') TimeIn2
, FORMAT(TimeOut2, 'HH:mm') TimeOut2
, CAST((DATEDIFF(second, TimeIn1, TimeOut1)/3600) AS VARCHAR(2)) + ':' +
CAST(((DATEDIFF(second, TimeIn1, TimeOut1)%3600)/60) AS VARCHAR(2)) Hours
FROM cte2
ORDER BY ID_Emp使用子查询
SELECT t.ID_Emp, t.Name
, FORMAT(t.actual_date, 'dd/MM/yyyy') date
, FORMAT(t.TimeIn1, 'HH:mm') TimeIn1
, FORMAT(t.TimeOut1, 'HH:mm') TimeOut1
, FORMAT(t.TimeIn2, 'HH:mm') TimeIn2
, FORMAT(t.TimeOut2, 'HH:mm') TimeOut2
, CAST((DATEDIFF(second, t.TimeIn1, t.TimeOut1)/3600) AS VARCHAR(2)) + ':' +
CAST(((DATEDIFF(second, t.TimeIn1, t.TimeOut1)%3600)/60) AS VARCHAR(2)) Hours
FROM (SELECT p.ID_Emp
, MAX(p.Name) Name
, p.actual_date
, MAX(CASE WHEN p.row_num = 1 THEN p.tdate END) TimeIn1
, MAX(CASE WHEN p.row_num = 2 THEN p.tdate END) TimeOut1
, MAX(CASE WHEN p.row_num = 3 THEN p.tdate END) TimeIn2
, MAX(CASE WHEN p.row_num = 4 THEN p.tdate END) TimeOut2
FROM (SELECT ID_Emp
, Name
, CAST(tdate AS date) actual_date
, tdate
, ROW_NUMBER() OVER (PARTITION BY ID_Emp, CAST(tdate AS date) ORDER BY tdate) row_num
FROM test
) p
GROUP BY ID_Emp, actual_date) t
ORDER BY t.ID_Emp请查看此url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=54f542847167481298b168d6b0a8d6b9
Stack Overflow用户
发布于 2021-09-29 15:03:41
enter code hereselect Id，Name，Date，TimeIn1，TimeOut1，TimeIn2 from (select Id，Name，date，time TimeIn1，lead(a.time) over(Partition by a.Id，a.Name，a.Date order by time) as TimeOut1，lead(a.time,2) over(Partition by a.Id，a.Name，a.Date order by time) as TimeIn2，ROW_NUMBER()Over(Partition by a.Id，a.Name，a.Date order by time) as rn from (select Id，Name，cast(date as date) as Date，将(日期转换为时间)转换为堆栈中的时间)a)b其中rn=1
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69357810
复制相关文章
相似问题
腾讯云开发者
