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

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

Time Series

Accepted answers

OllieClarke

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

shifts.yxmd

All comments

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

Hey @bperiasw

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

shifts.yxmd

bperiasw

Thanks this works perfect.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703