This content has been marked as final. Show 2 replies
I want to calculate the elapsed time between two statuses for a specific quote in our database.
The problem is the quote data is in one table and the history data (where the status timestamps are stored) are in a separate table. One column in the history table is called "Status" and contains all of the statuses and the other column contains all of the timestamps. There is a Serial Number column that links each row to a specific quote. I tried joining to the history table twice and filtering each table by the status I wanted to measure, then created a calculated field to measure the difference. It got me the data I wanted but was maxing out my TEMPDB table everytime I tried to run the report. After some research I discovered that joining to the same table twice can cause this.
So now I'm trying to do the calculation with a single join to the history table. I have created one Status filter pulling in both statuses (Plant Quoted and Quote Needed) and each timestamp is a different row in my query, linked to the same quote number. How do I create a calculation to find the difference between the two timestamps for a single serial number? Here is an example of what this looks like: