How to join a table to a changelog table?

1
votes

I have two tables related to tickets, DimTickets and want to calculate the time it takes between the CreatedDateTime and when the status was first changed to Done/Released. The DimTickets table is structured like so:

IssueKey IssueType Priority Project Status CreatedDateTime
TEAM1-100 Story High Team 1 Approved for Release 2020-04-02 16:09:45
TEAM1-101 Task Medium Team 1 Done 2020-04-03 15:38:25
TEAM1-102 Sub-task Low Team 1 Done 2020-04-08 09:03:43
TEAM1-103 Bug High Team 1 In Progress 2020-04-13 12:18:56
TEAM1-104 Task Medium Team 1 Done 2020-04-16 11:40:08
TEAM2-100 Task Medium Team 2 Done 2020-04-17 09:06:17
TEAM2-101 Story Medium Team 2 Released 2020-04-17 15:55:45
TEAM2-102 Task Low Team 2 Done 2020-04-20 10:12:41
TEAM1-105 Task High Team 1 In Progress 2020-04-20 15:24:56

and a DimTicketChangelog that's structured like this:

ChangeLogID IssueKey FromStatus ToStatus ChangeLogDateTime
1 TEAM1-100 1 2 2019-06-14 15:56:03
2 TEAM1-100 2 3 2019-06-15 12:58:29
3 TEAM2-102 2 4 2019-06-16 17:58:48
4 TEAM1-100 3 5 2019-06-16 20:01:43
5 TEAM1-104 1 3 2019-06-18 10:02:39
6 TEAM1-105 4 5 2019-06-21 18:03:19
7 TEAM1-104 3 5 2019-06-24 22:05:28
8 TEAM2-102 4 6 2019-07-02 08:06:50
9 TEAM2-103 1 4 2019-07-04 11:06:50

Is there a way for me to join to DimTicketChangelog the first time a ticket is changed from a status < 5 to status 5/6 so that I can create a field that is essentially ChangeLogDateTime - CreatedDateTime to get the amount of time it took between creation of the ticket, to when it had its status changed to a resolved one?

2
sql-servertsqljoin
Create a CTE, filtering to the status 5+and use ranking - Eli

2 Answers

1
votes

Something like this should work

Explanation: You'll use the CTE to find all the instances where a ticket crossed from <5 to 5+ and "rank" them by change log date. You'll then select all of records where this ranking = 1 as that is the first instance, sorted by change log date time
EDIT: I added a date diff to satisfy your second requirement for the time it took. Your sample data is a bit interesting in terms of these dates, though with your real data you should be fine.

;WITH FindFirstChange AS (
    select 
         t.IssueKey  /*Add whatever other columns you need here*/
         ,t.createdDateTime
         ,tcl.ChangeLogDateTime
         ,DATEDIFF(day, t.createdDateTime ,tcl.ChangeLogDateTime) Diff
        ,ranking = ROW_NUMBER() OVER(PARTITION BY tcl.issuekey ORDER BY tcl.ChangeLogDateTime ASC)
    FROM DimTickets t
    INNER JOIN DimTicketchangelog tcl ON t.issuekey = tcl.issuekey 
    WHERE tcl.fromStatus <5 
    AND toStatus >= 5
)
SELECT *
FROM FindFirstChange
WHERE ranking = 1;
1
votes

You have not provided any expected results and your test data doesn't really cover the criteria you describe, also your log table dates are earlier than the createDate of each ticket which makes no sense?! However see if the following works for you:

with l as (
    select issuekey,changelogdatetime, Row_Number() over(partition by issueKey order by ChangeLogID) rn
    from DimTicketChangeLog
    where tostatus in (5,6) and FromStatus<5
)
select t.*, DateDiff(day,t.createddatetime,l.ChangeLogDateTime) Duration
from DimTickets t 
left join l on l.IssueKey=t.IssueKey
where l.rn=1