Splitting time column into start time / end time columns

Question

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

What version of sql server? – RBarryYoung Jun 03 '17 at 19:32 — RBarryYoung, Jun 03 '17 at 19:32

score 0 · Answer 1 · answered Jun 03 '17 at 19:15

0

you might want to use sql cte option, look for some help on the link, SQL Find difference between previous and current row

My suggestion would be keep single record for activity, if your first record is start and next is end of that task.

If you do not want to change schema, then may add one more column depicting the task type column that it was a start or end. It will help you in future in queries.

answered Jun 03 '17 at 19:15

DASH

297
1
4
10

Thanks, the cte option was exactly what I needed. – absent_moose Jun 03 '17 at 20:44

score 0 · Answer 2 · answered Jun 03 '17 at 22:48

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

Splitting time column into start time / end time columns

2 Answers2

Linked