See picture below - the top part is the data. There is an alarm event A, and then a possible response and response time B. There can be multiple alarms and multiple responses, or no responses.
Basically I want to find the two closest in time pairs between A and B. The key thing is that there can be one, or more than one, per day.
So in the first example, there were 3 alarms. The first and third were ignored. I only want the pair of 8:08(A) and 8:11(B) for that day, since they are the closest in time.
However, on 4/4, there were 3 alarms, and each one was answered, so for that day there are three pairs. 0:14(A) - 0:49(B), 2:29(A) - 3:32(B), and 5:24(A) - 9:33(B)
I can either get: all the pairs on both days, or only one on both days. I guess the issue is I can't really group by anything. I can't group by the date because that will just get me the min time on ONE pair that day. But I can't really think on what else I could group it by. Also, it could cross over midnight, so a pair could span two days.
SELECT alarm,
CAST(A AS DATE) AS A,
MIN(shortest_ab_duration) AS shortest_ab_duration
FROM
(
SELECT alarm,
A,
MIN(B - A) AS shortest_ab_duration
FROM
(
SELECT *,
MIN(B) OVER (PARTITION BY alarm ORDER BY A DESC) AS next_b_timestamp
FROM
(
SELECT a.alarm,
a.A,
b.B
FROM #data_a a
LEFT JOIN #data_b b
ON a.alarm = b.alarm
AND a.A <= b.B
) t
) t
GROUP BY alarm,
A
) T
GROUP BY alarm,
A
ORDER BY alarm,
A;