SQL calculating time between assignments

I have to write an SQL statement which contain a field that contain two different values consecutively but in the way I have wrote it, it return always null because it is interpreted as having the two value in the same time!

My conditions should be : (ci.field = ‘Group’ and ci.oldString = ‘Triage’ ) and (ci.field=’assignee’ and ci.newString is not NULL)

That means calculate time between: when the issue is assigned to group named Triage and when the issue is assigned to a person.

How can I fix it?

My SQL statement:

select TIMEDIFF(a.created,b.created) 
from
(select g.created, g.issueid as groupid1 
    from   changegroup g
    join   changeitem ci on (ci.groupid = g.id)
    join   jiraissue ji on (ji.id = g.issueid)
    join   project p on (p.id = ji.project)
    join   priority pr on (pr.id = ji.priority)
    where  ci.field = 'Group'
    and  ci.oldString = 'Triage' 
    and  ci.field='assignee'
    and ci.newString is not NULL
    and  p.pname = 'Test'
    and pr.pname='P1'
    and  ji.created between '2011-08-11 14:01:00' and  '2011-08-12 14:11:00'
) a 
left join (
    select ji.created, ji.id as groupid2 
    from   jiraissue ji
    join   changegroup g on (g.issueid = ji.id)
    join   project p on (p.id = ji.project)
    where  p.pname = 'Test'
      and  ji.created between '2011-08-11 14:01:00' and '2011-08-12 14:11:00' 
) b ON (a.groupid1 = b.groupid2);

This is the table from which I should retrieve data

Answer

See my comment about the quality of your question but a hint at how to solve this goes like (assuming you can make sure this doesn’t create 1-n joins)

select groupid_orsomething_else, TIMEDIFF(a.created, b.created) 
from yourtable
left join 
 (select groupid_orsomething_else, created 
  from yourtable 
  where field = 'Group' and oldstring is 'Triage'
 ) a 
on a.groupid_orsomething_else = yourtable.groupid_orsomething_else
left join 
 (select groupid_orsomething_else, created 
  from yourtable 
  where field = 'assignee' and oldstring is null) b 
on b.groupid_orsomething_else = yourtable.groupid_orsomething_else