Arcade or Python: Iterate through Related Table for Consecutive Values for Each GUID?

ArmstKP

We are looking to find the duration of time something was closed.  We have a status (open/closed) field and a datetime field.  How would I iterate through the related table for each GUID to find the amount of time something was closed?  I am guessing part of this would be to sort by date and GUID and then a DateDiff?

example data for one guid/relfeatureglobalid:

 

 

DavidPike

I think the simplest way is actually using field calculator.  As you say, you first need to sort your data first by GUID, then  by datetime DESCENDING.  you need to physically reorder the table using https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/sort.htm

sorting just the attribute table view and running field calculator won't work as the internal cursor is by ObjectID (I believe)

#imports
import datetime

#create variables to store the global id and datetime
#records from the previous iteration
prev_dtime=''
prev_gid=''

#your datetime field, your globalid field
def my_func(dtime, gid):
  #make your globals
  global prev_dtime
  global prev_gid

  try:
    #if global ids match
    if gid == prev_gid:  
      difference = dtime - prev_dtime  
      #hours difference from previous record (sorted desc so it's actually 
      #the next record) 
      return float(difference.total_seconds()/3600)
  except: 
    #return whatever you like if error etc 
    return -999
  finally:
    #update the prev dtime and gid for the next iteration
    prev_dtime = dtime  
    prev_gid = gid

After you run this (obviously create a new float field to calculate into) you could also then summarize by GUID attribute and get the totals (definition query or filter out any 'open' values first e.g. 'FIELD LIKE 'Open')

Might get you started anyway