Subtracting time value from previous row and based on a condition capture the time value

[bperiasw]

I have a requirement to compute from below table to arrive timeslots for each door group ID like

1) 0:00 ~ 1:00 (after adding :30 to the last value)

2) 17:00 ~ 00:00.

Basically if the timesegment row value differences are more than 30 mins then those are separate slots having a different shift start time and all slots in the below table are 30 mins slots. Any pointers to this will be helpful.

doorgroupid	weekday	timesegment	active
10	1	0:00	T
10	1	0:30	T
10	1	17:00	T
10	1	17:30	T
10	1	18:00	T
10	1	18:30	T
10	1	19:00	T
10	1	19:30	T
10	1	20:00	T
10	1	20:30	T
10	1	21:00	T
10	1	21:30	T
10	1	22:00	T
10	1	22:30	T
10	1	23:00	T
10	1	23:30	T

[apathetichell]

Hi!

Could you show me an example of the output you'd like? Its fairly straight forward to divide times up by group - but I'm not quite sure what you want as the final output. If you want to just segment into 1) and 2) - you could use a formula like:

if [timesegment]<"17:00" then 1 else 2 endif

[bperiasw]

I would like to have a new column Shift start time and end time.

[OllieClarke · Accepted solution]

Hey @bperiasw 

Here's a workflow which does what you want (I think). One thing I should say is that ideally there would be a date element to your time segment, otherwise shifts which run from pre-midnight to post will be hard to deal with. 

I've kept the Shift ID in the output, but you could drop it in the join tool if you want

Hope that helps,

Ollie

[bperiasw]

Thanks this works perfect.