I have a database with many tables. One of the tables maintains the history of the job status. This tables has many columns but the columns of importance are Job Number, Status, and Status Start Time. I need to calculate the time the job spent from start to completion.
For most jobs this is easy since I have the job request time and the job complete time in the job table. However there are instances when a job can be marked complete but the reopened for further work. This can potentially happen many times.
The users want to see the time between start of the cycle and job complete. For the first job completion this is simply the job complete time - job request time. I can do this one easily. For a job that has multiple completions I need the time calculated as Job Complete Time - Previous Job Complete Time.
I don't know how to select the previous Job Complete Time from the data based on the completion record I have.
I cannot modify existing tables in the database but I can add a view or build a derived table in the universe the reports are based on