sql - Getting logically-corresponding records where no direct association exists -

i have table logging requests , responses cases, , times each occurs. problem there no direct association between particular response , request it's responding to. make matters more difficult, there might multiple responses 1 request, , requests , responses aren't collated - might have 2 requests followed 2 responses, means first response relates first request, , second response relates second request. simplified table below gives examples of each of situations:

case_id     subject     date 123         request     2014-12-03 15:48:02 123         response    2014-12-03 16:01:45 123         response    2014-12-03 17:32:23 123         request     2015-04-10 12:12:17 123         request     2015-04-10 12:13:34 123         response    2015-04-10 13:31:20 123         response    2014-04-10 13:37:12 234         request     2014-12-03 12:12:20 234         response    2014-12-03 12:20:23 234         request     2015-03-03 15:09:44 234         response    2015-03-03 16:23:54 

what want find first response corresponds each request within case calculate time took respond. output of query should be:

case_id     request time            response time 123         2014-12-03 15:48:02     2014-12-03 16:01:45 123         2015-04-10 12:12:17     2015-04-10 13:31:20 123         2015-04-10 12:13:34     2015-04-10 13:37:12 234         2014-12-03 12:12:20     2014-12-03 12:20:23 234         2015-03-03 15:09:44     2015-03-03 16:23:54 

the problem i'm having non-collated examples have 2 (or more) requests followed 2 (or more) responses. far i've got this:

select n1.case_id, n1.date request_date, n2.date response_date notes n1   join notes n2     on n1.subject = 'request'     , n2.date = (       select min(date)       notes       case_id = n1.case_id         , subject = 'response'         , date > n1.date     ) 

which works, can see sql fiddle here fails when have multiple requests followed multiple responses - takes first response regardless of fact it's been "claimed" previous request. i've searched around, cannot figure out how make exclude responses have been joined previously.

this unpleasant, seems work data. had fix small glitch in data have different value row #7 in data shown in results.

declare @data table (     case_id int,     [subject] varchar(50),     [date] datetime); insert @data select 123, 'request', '2014-12-03 15:48:02'; insert @data select 123, 'response', '2014-12-03 16:01:45'; insert @data select 123, 'response', '2014-12-03 17:32:23'; insert @data select 123, 'request', '2015-04-10 12:12:17'; insert @data select 123, 'request', '2015-04-10 12:13:34'; insert @data select 123, 'response', '2015-04-10 13:31:20'; insert @data select 123, 'response', '2015-04-10 13:37:12'; insert @data select 234, 'request', '2014-12-03 12:12:20'; insert @data select 234, 'response', '2014-12-03 12:20:23'; insert @data select 234, 'request', '2015-03-03 15:09:44'; insert @data select 234, 'response', '2015-03-03 16:23:54'; rowids (     select         *,         row_number() on (order case_id, [date]) rowid             @data), requests (     select         case_id,         [date]             @data             [subject] = 'request'), nextresponse (     select         r.case_id,         r.[date] request_time,         min(d.[date]) response_time             requests r         inner join @data d on d.case_id = r.case_id , d.[subject] = 'response' , d.[date] > r.[date]     group         r.case_id,         r.[date]), assignids (     select         nr.*,         r.rowid             nextresponse nr         inner join rowids r on r.case_id = nr.case_id , r.[subject] = 'response' , r.[date] = nr.response_time), findduplicates (     select         rowid             assignids     group         rowid     having         count(*) > 1), applyduplicateoffsets (     select         a.case_id,         a.request_time,         a.response_time,         a.rowid,         row_number() on (partition a.rowid order a.response_time) offset             findduplicates fd         inner join assignids on a.rowid = fd.rowid), fixduplicates (     select         a.case_id,         a.request_time,         min(case when d.rowid null a.response_time else a2.[date] end) response_time             assignids         left join findduplicates d on d.rowid = a.rowid         left join applyduplicateoffsets o on o.rowid = a.rowid , o.request_time = a.request_time         left join rowids a2 on a2.case_id = a.case_id , a2.rowid >= a.rowid + o.offset - 1     group         a.case_id,         a.request_time) select     *     fixduplicates order     case_id,     request_time; 

basically works follows:

• first make list of data unique row id assigned each row;
• next perform simple match, picking next response each request;
• this ends using same response multiple rows find happens;
• next create order duplicates, ordered date, first duplicate assigned 1, 2, etc.;
• now can reassign response dates (assuming each request has it's own response) picking original response first duplicate, next available response second duplicate, etc.;
• finally spew out results.

results this:

case_id request_time    response_time 123 2014-12-03 15:48:02.000 2014-12-03 16:01:45.000 123 2015-04-10 12:12:17.000 2015-04-10 13:31:20.000 123 2015-04-10 12:13:34.000 2015-04-10 13:37:12.000 234 2014-12-03 12:12:20.000 2014-12-03 12:20:23.000 234 2015-03-03 15:09:44.000 2015-03-03 16:23:54.000 

is solution? not really, thought need form of recursion , maybe better way solve problem?