I am working on a file that shows status of items. As items progress through steps of the process, they move to a new status. I need to calculate the time that an items spends in each step.
Unfortunately my data also shows, as a new line, if updates were made in a step before it moves to the next step. Ex: While in the "test" step for item ID AAA, I updated a note to indicate progress was made. The data returns a line for this update, but the step remains unchanged and the entry date to and subsequent exit date from this step also remain unchanged.
I would like to return "0" for instances that just show updates in steps, with no movement. In these instances, the ID, process step, entered queue date, exit queue date are all the same, however the notes are different.
I have attached a file which is representative of my data file.
Thank you for your help.