I am looking to identify the average life time in days for open bugs based on severity.

I would like to track no of days it takes for each of the severity (A, B, C, D) since their first status like (open/opened) till their status changes to closed (close/ closed/ closed with fix)

This is what I tried so far,

select severity, count(distinct date_assigned) as no_of_days 
from Table 1 as a
join Table 1 as b
where status = 'open' | 'opened' and status = 'close' | 'closed' | 'closed with fix'

But this isn't giving me the expected output

CodePudding user response：

Assuming that there is one "open" and "close" per bug, then you can use two levels of aggregation:

select severity,
       avg( close_date - open_date ) as avg_days
from (select bug, severity, min(date_assigned) as open_date,
             max(date_assigned) as close_date
      from table1
      where status like '%open%' or
            status like '%closed%'
      group by bug, severity
     ) t1
group by severity
order by severity;

Note that date functions vary considerably among databases. The exact syntax for the data difference may differ from above.

EDIT:

In Databricks, I think you want:

select severity,
       avg( datediff(close_date, open_date) ) as avg_days