将时间列拆分为开始时间/结束时间列 [英] Splitting time column into start time / end time columns

问题描述

我有一张表格，其中包含有关他们一整天所做工作的信息.我需要获取每个任务的开始时间/结束时间.

目前我能够提取每个任务的时间戳，但我希望创建开始时间和结束时间列.开始时间是前一行的时间戳，结束时间是当前行的时间戳.

有没有什么简单的方法可以做到这一点?这是我目前使用的查询.

选择时间戳，斯库，车站，用户名，动作类型来自整体日志在哪里和 ActionType = 'Put'和用户名 = '姓名'和时间戳= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)按时间戳降序排序

解决方案

如果你想得到最后一行的值，你可以使用 LAG 函数.示例

;WITH sampledata(ID, t) AS(选择 1 ,DATEADD(HOUR,-12,GETDATE()) 联合选择 2 ,DATEADD(HOUR,-8,GETDATE()) 联合选择 3 ,DATEADD(HOUR,-4,GETDATE()) 联合选择 4 ,DATEADD(HOUR,-2,GETDATE()) 联合选择 5 ,DATEADD(HOUR,-1,GETDATE()))SELECT *,LAG(t)OVER(ORDER BY t) AS start_t FROM sampledata

<前>ID t start_t——————————————————————————————-------1 2017-06-03 06:48:59.447 NULL2 2017-06-03 10:48:59.447 2017-06-03 06:48:59.4473 2017-06-03 14:48:59.447 2017-06-03 10:48:59.4474 2017-06-03 16:48:59.447 2017-06-03 14:48:59.4475 2017-06-03 17:48:59.447 2017-06-03 16:48:59.447

I have a table that contains information regarding what they've done throughout the day. I need to get the start time/end time for each task.

Currently I am able to pull the timestamp of each task, but I am hoping to create Start Times and End Times columns. Start time would be the timestamp from the previous row, and end time would be the timestamp from the current row.

Is there any simple way to do this? Here is the query that I am currently using.

select  
Timestamp,
Sku,
Station,
Username,
ActionType
from OverallLogs


where 
and ActionType = 'Put'
and Username = 'Name'
and timestamp < DateAdd(Day, DateDiff(Day, 0, GetDate())+1, 0)
AND timestamp >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

Order by timestamp desc

解决方案

If you want to get the value of last line ,you can use LAG function. Example

;WITH sampledata(ID, t) AS(
  SELECT 1 ,DATEADD(HOUR,-12,GETDATE()) UNION
  SELECT 2 ,DATEADD(HOUR,-8,GETDATE()) UNION
  SELECT 3 ,DATEADD(HOUR,-4,GETDATE()) UNION
  SELECT 4 ,DATEADD(HOUR,-2,GETDATE()) UNION
  SELECT 5 ,DATEADD(HOUR,-1,GETDATE()) 

)
SELECT *,LAG(t)OVER(ORDER BY t ) AS start_t FROM sampledata

ID          t                       start_t
----------- ----------------------- -----------------------
1           2017-06-03 06:48:59.447 NULL
2           2017-06-03 10:48:59.447 2017-06-03 06:48:59.447
3           2017-06-03 14:48:59.447 2017-06-03 10:48:59.447
4           2017-06-03 16:48:59.447 2017-06-03 14:48:59.447
5           2017-06-03 17:48:59.447 2017-06-03 16:48:59.447

这篇关于将时间列拆分为开始时间/结束时间列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！