Calculating time intervals over history

user_14809304 0
Viewed 38

I have a table of bug activity history:

BugID OldStatus NewStatus DateModified
12345 New Assigned 2022-06-01
12345 Assigned Waiting 2022-06-02
12345 Waiting Assigned 2022-06-04
12345 Assigned Waiting 2022-06-05
12345 Waiting Assigned 2022-06-06
12345 Assigned Confirmed 2022-06-08
12345 Confirmed Coding 2022-06-10
12345 Coding Testing 2022-06-14
12345 Testing Closed 2022-06-18
23456 New Assigned 2022-06-10
23456 Assigned Waiting 2022-06-10
23456 Waiting Assigned 2022-06-15
23456 Assigned Waiting 2022-06-15
23456 Waiting Assigned 2022-06-16
23456 Assigned Waiting 2022-06-17
23456 Waiting Assigned 2022-06-19
23456 Assigned Confirmed 2022-06-21
23456 Confirmed Coding 2022-06-30
23456 Coding Testing 2022-07-05
23456 Testing Closed 2022-07-10

By BugID, I need the total days spent in Waiting (for more info from the bug reporter) and the total days from New to Closed. OR The total days from New to Closed minus Waiting days. The database has BugSubmitted which equals the New DateModified for each BugID.

I've calculated DATADIFF(DAY, BugSubmitted, MAX(DateModified) over (partition by BugID)) AS 'Days to Close'.

How do I calculate just the Waiting days, i.e. 3 of 17 days for #12345 and 8 of 30 days for #23456?

ssms sql
1 Answers

You may use the LEAD() function and simple aggregation as the following:

with cte as 
(
    select BugID,NewStatus,
    DATEDIFF(Day,DateModified, 
    LEAD(DateModified) Over (Partition By BugID Order By DateModified)) As Df 
    from BugHistory
)
select BugID, Sum(Df) As TotalDays, 
Sum(Case When NewStatus='Waiting' Then Df Else 0 End) as TotalWaiting
from cte 
Group By BugID

See a demo from db<>fiddle.

user_127059120
Related