sql - MySQL querying transition states -

i'm new sql , 1 of project find transition states of issue 1 status (see enclosed image) stored in mysql jira database.

i sorted data based on issue id followed transition dates order data.

if @ first 2 rows see transition date of issue open fixing. on second row same issue moves fixing on hold. need find out days issue remained in fixing state.

in excel or using 2d arrays doesn't seem difficult sql ... seems impossible. suggestion , pointers kindly appreciated :).

i created table based on mentioned , here schema , query.

create table tablename      (      tid int ,       startdate date,       transitiondate date,       fromstatus varchar(10),       tostatus varchar(10)     ); 

pay attention not exists subquery. tid 1 goes in , out of fixing takes care of incorrect calculations.

select t1.tid,  sum(t2.transitiondate - t1.transitiondate) tablename t1, tablename t2 t1.tid = t2.tid , t1.tostatus = "f" , t2.fromstatus = "f"      , t1.transitiondate <= t2.transitiondate ,     not exists (select * tablename t3 t3.tostatus = "f" ,               t3.transitiondate != t1.transitiondate ,               t3.transitiondate != t2.transitiondate ,               t3.transitiondate between t1.transitiondate , t2.transitiondate) group t1.tid 

if want counts every time issue being fixed -

select t1.tid,  (t2.transitiondate - t1.transitiondate) tablename t1, tablename t2 t1.tid = t2.tid , t1.tostatus = "f" , t2.fromstatus = "f"      , t1.transitiondate <= t2.transitiondate ,     not exists (select * tablename t3 t3.tostatus = "f" ,               t3.transitiondate != t1.transitiondate ,               t3.transitiondate != t2.transitiondate ,               t3.transitiondate between t1.transitiondate , t2.transitiondate) group t1.tid, t1.transitiondate 

sql fiddle

if possible add primary key (identity) date comparisons can replaced primary key.