When Joining a SQL Table to Itself, Return only One Result. Time Log Entry

Question

I am working with some data from Jira. In Jira there are "Issues" and each issue can go through states such as New, In Progress, Review, etc. I want to measure the time something stays in each state. It is possible for things to move back and forth and return to a state multiple times. Jira produces a log table which logs the event of the issue moving from one state to another. To measure how long it’s been in a state you need to have the entry from the log for that state (end) and the previous change of state (start).

I can’t return one entry for each state change. For Issues that moved between a state multiple times I get multiple rows.

I tried the gaps and islands approach. Also a select within the top select. Min or Max in the join was atrociously slow.

The desired result would be a column added to the table in the select which gives the duration for the State in the column ItemFromString. The date difference is between this entry’s Created date and the previous state change entry’s created date, which shows when the issue moved to this state. In the example data below the first entry for Assessment, History ID 436260, would be a duration of 9/19–9/14. When I join I get multiple entries for this History ID since there are multiple Assessments. I filter the join by Issue key and Item from/to String where they match; however, I need to also add a filter where it looks at any entries created before the current items created date and selects the most recent, or largest, one. This is where I am hung up.

Fields:

• Created - This is when the log entry was created which is the date time it changed state from ItemFromString to ItemToString.
• IssueCreated - This is when the issue the log is about, was created. For example, they start in the new state so we need this date to figure out how long it sat in new as the first log entry will be it moving from New to something else.
• IssueKey and IssueID are almost the same thing, they are key ID's for the issue in a different table.
• HistoryID is the key for each log entry in this table.

Assessment

IssueKey	HistoryID	IssueId	Created	IssueCreatedDate	ItemFromString	ItemToString
TPP-16	434905	208965	9/14/2022 14:33	9/14/2022 8:56	New	Assessment
TPP-16	436260	208965	9/19/2022 8:32	9/14/2022 8:56	Assessment	Internal Review
TPP-16	437795	208965	9/19/2022 16:11	9/14/2022 8:56	Internal Review	New
TPP-16	437796	208965	9/19/2022 16:11	9/14/2022 8:56	New	Assessment
TPP-16	439006	208965	9/20/2022 15:08	9/14/2022 8:56	Assessment	New
TPP-16	457786	208965	10/17/2022 11:02	9/14/2022 8:56	New	Assessment
TPP-16	457789	208965	10/17/2022 11:03	9/14/2022 8:56	Assessment	Internal Review
TPP-16	490205	208965	10/27/2022 15:15	9/14/2022 8:56	Internal Review	On Hold
TPP-16	539391	208965	1/11/2023 15:24	9/14/2022 8:56	On Hold	Backlog

This query does not get a duration as the last column in the query. The query creates a table that is then published and utilized by BI products for graphing and analysis.

SELECT 
    IssueChangelogs.IssueKey IssueKey, 
    IssueChangelogs.HistoryId HistoryId, 
    IssueChangelogs.IssueId IssueId, 
    IssueChangelogs.IssueCreatedDate IssueCreatedDate, 
    IssueChangelogs.ItemFromString ItemFromString, 
    IssueChangelogs.ItemToString ItemToString,
    ICLPrev.Created PrevCreated, --For Testing
    IssueChangelogs.Created Created, 
    ICLPrev.HistoryID PrevHistoryID, --For Testing
    CASE 
        -- If the join found a match for a previous status, then we can calculate the Duration it was in that state.
        WHEN ICLPrev.HistoryID IS NOT NULL 
            THEN DATEDIFF(hour, ICLPrev.Created,  IssueChangeLogs.Created)/24
        -- If the state was new then we need to use the IssueCreatedDate as the start date as the default state is New for each issue.
        WHEN  IssueChangeLogs.ItemFromString LIKE '%New%'
            THEN Round(DATEDIFF(hour, IssueChangeLogs.IssueCreatedDate, IssueChangeLogs.Created), 2)/24
        -- Else, let's add something easy to identify so when we test and look at the table we know what occured.
        ELSE 0.01
        END AS Duration
FROM      
    TableNameRedacted AS IssueChangelogs
LEFT JOIN 
    TableNameRedacted AS ICLPrev 
ON ICLPrev.IssueKey = IssueChangeLogs.IssueKey AND 
       ICLPrev.ItemToString = IssueChangeLogs.ItemFromString
WHERE
    IssueChangelogs.IssueKey LIKE '%TPP%'

Answer 1

I used the Row_number function to identify the rows I wanted, then wrap it in a CTE and pull those row numbers, then add one more criteria to the join to rule out oddities.

First we select what we need from the table and then pull out the previous entry related to the state (status) change so we can calculate the time it spent in that state as Duration. So create a Temporary Table (CTE) and order this to use the Row_Number function to identify the rows we want. Then select only those rows from the CTE.

WITH CTE AS (
SELECT
    IssueChangelogs.IssueKey IssueKey, 
    IssueChangelogs.HistoryId HistoryId, 
    IssueChangelogs.IssueId IssueId, 
    IssueChangelogs.AuthorDisplayName AuthorDisplayName, 
    IssueChangelogs.IssueCreatedDate IssueCreatedDate, 
    IssueChangelogs.ItemFromString ItemFromString, 
    IssueChangelogs.ItemToString ItemToString,
    ICLPrev.Created PrevRelatedCreatedDate,
    ICLPrev.HistoryId PrevRelatedHistoryID,
    IssueChangelogs.Created Created,
    CASE 
        -- If the join found a match for a previous status, then we can calculate the Duration it was in that state.
        WHEN ICLPrev.HistoryID IS NOT NULL 
        --  Funky math is so we can get the number of days with 2 decimal points.
            THEN CAST((DATEDIFF(second, ICLPrev.Created, IssueChangeLogs.Created)/86400.00)AS DECIMAL(6,2))
        -- If the state was new then we need to use the IssueCreatedDate as the start date as the default state is New for each issue.
        WHEN  IssueChangeLogs.ItemFromString LIKE '%New%'
        --  Again calculate to get 2 decimal places for duration in days.
            THEN CAST((DATEDIFF(second, IssueChangeLogs.IssueCreatedDate, IssueChangeLogs.Created)/86400.00)AS DECIMAL(6,2))
        -- Else, let's add something easy to identify so when we test and look at the table we know what occured.
        ELSE NULL
        END AS Duration,
    -- Here we are going to assign the number 1 to the rows we want to keep. 
    ROW_NUMBER() OVER(PARTITION BY IssueChangeLogs.IssueKey, IssueChangeLogs.HistoryID, IssueChangeLogs.Created
        -- Since duplicates can exist we only want the most recent match. The join ensures we don't have any start dates greater than the end date and the order by here puts the most recent (DESC) on top so its gets tagged with a 1.
        ORDER BY ICLPrev.Created DESC) AS RowNo
FROM      
    /shared/"Space"/CUI/TandTE/Physical/Metadata/JIRA/CIS/JIRA/IssueChangelogs IssueChangelogs
LEFT JOIN 
    /shared/"Space"/CUI/TandTE/Physical/Metadata/JIRA/CIS/JIRA/IssueChangelogs AS ICLPrev 
    -- These are the closest we can get to a key; however, it will still produce duplicates since an issue can return to a previous state multiple times.
    ON ICLPrev.IssueKey = IssueChangeLogs.IssueKey AND ICLPrev.ItemToString = IssueChangeLogs.ItemFromString
    -- This will remove anything that doesn't make sense in the join, for example a state change where the start date is greater than the end date. Still leaves duplicates which the row number will fix.
    AND ICLPrev.Created < IssueChangeLogs.Created
WHERE
    IssueChangelogs.IssueKey LIKE '%TPP%'
)
-- Now that the CTE table has been created and we have identifed the rows we want from the join with a 1 in RowNo, we filter by those and only pull the columns we want.
SELECT 
    CTE.IssueKey, 
    CTE.HistoryId, 
    CTE.IssueId, 
    CTE.AuthorDisplayName, 
    CTE.IssueCreatedDate, 
    CTE.ItemFromString, 
    CTE.ItemToString,
    CTE.PrevRelatedHistoryID,
    CTE.PrevRelatedCreatedDate,
    CTE.Created,
    CTE.Duration
FROM CTE 
WHERE CTE.RowNo = 1