SQL – Aggregates data with dates interval

i’m facing a problem joining two table with date interval that represent user status change. ID is the same in both table.

Table_A

ID  StatusA    FromA              ToA
1   Active     01/01/2020 10:00   01/01/2020 11:30
1   NonActive  02/01/2020 09:00   03/01/2020 11:00
2   Active     01/01/2020 10:00   01/01/2020 11:30
3   Active     02/01/2020 09:30   02/01/2020 15:50

Table_B

ID  StatusB    FromB               ToB
1   Chatting   01/01/2020 10:02   01/01/2020 10:15
1   Calling    01/01/2020 10:10   01/01/2020 10:20
2   Awaiting   02/01/2020 10:00   02/01/2020 10:15
2   Calling    02/01/2020 10:16   02/01/2020 10:20
3   Awating    02/01/2020 09:30   02/01/2020 15:50
1   Awating    02/01/2020 09:00   03/01/2020 11:00

What i’m trying to obtain is a table like that:

ID  StatusA    FromA              ToA               StatusB   FromB              ToB
1   Active     01/01/2020 10:00   01/01/2020 11:30  Chatting  01/01/2020 10:02   01/01/2020 10:15
1   Active     01/01/2020 10:00   01/01/2020 11:30  Calling   01/01/2020 10:10   01/01/2020 10:20
2   Active     01/01/2020 10:00   01/01/2020 11:30  Awaiting  02/01/2020 10:00   02/01/2020 10:15
2   Active     01/01/2020 10:00   01/01/2020 11:30  Calling   02/01/2020 10:16   02/01/2020 10:20

The query i wrote at the very beginning:

SELECT Table_A.ID, 
Table_A.StatusA AS "Primary_Status", 
Table_A.FromA, 
Table_A.ToA,
Table_B.StatusB AS "Secondary_Status",
Table_B.FromB,
Table_B.ToB,
FROM TableA 
INNER JOIN Table_B ON Table_A.ID = Table_B.ID
WHERE ((Table_B.startTime BETWEEN Table_A.FromA AND Table_A.ToA)
or (Table_B.FromB <= Table_A.FromA AND Table_B.ToB <= Table_A.ToA)
or (Table_B.FromB <= Table_A.FromA AND Table_B.ToB >= Table_A.ToA)
or (Table_B.FromB BETWEEN Table_A.FromA AND Table_A.ToA AND Table_B.ToB >= Table_A.ToA) 
);

Thanks in advance!

EDIT: each ID uniquely identifies a user. So, user with ID = 1 in Table_A is the same user with ID = 1 in Table_B

EDIT_02: Thanks to Gordon Linoff solution, i have now

SELECT Table_A.ID, 
Table_A.StatusA AS "Primary_Status", 
Table_A.FromA, 
Table_A.ToA,
Table_B.StatusB AS "Secondary_Status",
Table_B.FromB,
Table_B.ToB,
FROM TableA 
INNER JOIN Table_B ON (Table_A.ID = Table_B.ID AND Table_B.FromB >= Table_A.FromA AND Table_B.ToB <= Table_A.ToA)

This query is working, but i could have the situation that

b.FromB <= a.FromA && b.ToB <= a.ToA
b.FromB >= a.FromA && b.ToB >= a.ToA
b.FromB <= a.FromA && b.ToB >= a.ToA

Answer

You seem to just want a join with the right conditions on the time columns:

SELECT a.ID, a.StatusA AS "Primary_Status", a.FromA, a.ToA,
       b.StatusB AS "Secondary_Status", b.FromB, b.ToB,
FROM TableA a JOIN
     Table_B b
     ON a.ID = b.ID AND
        b.FromB >= a.FromA AND
        b.ToB <= b.FromB;

Advertisement

Answer