2 Replies Latest reply on Jun 23, 2016 9:26 AM by Stephanie Smay

    Calculate Date using two rows in the same table

    Stephanie Smay

      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:

      Capture.PNG