SQLTeam.com | Weblogs | Forums

Closest pairs in time

Transact-SQL
#1

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.

picture

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;

#2 

SELECT
    Date,
    A, B,
    A_B_Diff AS Min_Duration_Mins
FROM (
    SELECT
        CAST(A.A AS date) AS Date,
        A.A, B.B, 
        DATEDIFF(MINUTE, A.A, B.B) AS A_B_Diff,
        ROW_NUMBER() OVER(PARTITION BY CAST(A.A AS date) 
            ORDER BY ISNULL(DATEDIFF(MINUTE, A.A, B.B), 999999999), A.A) 
            AS row_num
    FROM #TableA A
    OUTER APPLY (
        SELECT TOP (1) *
        FROM #TableB B
        WHERE B.B >= CAST(A.A AS date) AND 
            B.B < DATEADD(DAY, 1, CAST(A.A AS date)) AND
            B.B > A.A
        ORDER BY B.B
    ) AS B
) AS derived
WHERE row_num = 1
ORDER BY Date

/* sample data in usable form: please post data like this in the future */
/*

CREATE TABLE #TableA ( A datetime NOT NULL )
CREATE TABLE #TableB ( B datetime NOT NULL )
TRUNCATE TABLE #TableA;
INSERT INTO #TableA VALUES ('2022-03-19 00:49'), ('2022-03-19 08:08'), ('2022-03-19 15:04'), 
    ('2022-04-04 00:14'), ('2022-04-04 02:29'), ('2022-04-04 05:24')
TRUNCATE TABLE #TableB;
INSERT INTO #TableB VALUES ('2022-03-19 08:11'),
    ('2022-04-04 00:49'), ('2022-04-04 03:32'), ('2022-04-04 09:33')

*/
#3

Oh sorry about the data.

Your code gets me one pair PER day : Part A

if I comment out where row_num = 1 then I get all three for EACH day : Part B

The issue is you can have one pair per day, none per day, or many per day. Also, like I mentioned, it's not even really by day, since you can cross midnight.
3/19 should have only one pair, but 4/4 should have three pairs. I'm not looking for the shortest pair PER DAY. I'm looking basically for the the pairs with the shortest distance, and then move on to the NEXT closest pair. That's what makes it tricky.

Basically start with an alarm, look for the closest response, then it's a pair. If there another alarm later than the first that's closer to the response, that's the pair. Maybe more of a cursor thing?

3/19 alarm went out 0:49, nothing happened. Second alarm went out 8:08, response at 8:11, that's a pair. Third alarm went out 15:04, nothing happened. One pair on 3/19

4/4 alarm went out 0:14, response at 0:49, that's a pair. Second alarm went out at 2:29, response at 3:32, that's a pair. Third alarm went out 5:24, response at 9:33, that's the third pair for 4/4.

pic5
pic5691×639 150 KB

#4

You don't really need a cursor, although of course you could use one if you wanted to.

If there's a lot of data, you will need appropriate indexes to speed up the query.


SELECT
    A, B,
    A_B_Diff
FROM (
    SELECT
        CAST(A.A AS date) AS Date,
        A.A, B.B, 
        DATEDIFF(MINUTE, A.A, ISNULL(B.B, A2.A)) AS A_B_Diff
    FROM #TableA A
    OUTER APPLY (
        SELECT TOP (1) A2.A 
        FROM #TableA A2 
        WHERE A2.A > A.A AND
            CAST(A2.A AS date) = CAST(A.A AS date)
        ORDER BY A2.A
    ) AS A2
    OUTER APPLY (
        SELECT TOP (1) *
        FROM #TableB B
        WHERE B.B >= CAST(A.A AS date) AND 
            B.B < DATEADD(DAY, 1, CAST(A.A AS date)) AND
            B.B > A.A AND
            B.B < ISNULL(A2.A, '20670101')
        ORDER BY B.B
    ) AS B
) AS derived
ORDER BY A
1 Like
#5

I didn't think of approaching it that way. That should work, thanks