Pairing Send and Receive Data Rows in SQL

Question

Having issues pairing data rows in SQL to identify when an item goes out, but does not return. The send and receive entries are on different rows, and there is a data relationship between the two, so I think this is doable, I'm just stumped as to the solution. The Module and User should tie together the transaction, however there is additionally a "batch" column that could be used to further ensure a unique transaction

Example Table Structure:

 MODULE         USER       EVENTDTTM                ACTION     Batch
 --------------------------------------------------------------------
 MODULE1        USERB      2016-01-09 13:00:00      SENT        001
 MODULE1        USERB      2016-01-09 13:01:00      RECEIVED    001
 MODULE2        USERA      2016-01-09 13:00:00      SENT        001
 MODULE2        USERA      2016-01-09 13:01:00      RECEIVED    001
 MODULE1        USERA      2016-01-09 13:03:00      SENT        002
 MODULE2        USERB      2016-01-09 13:04:00      SENT        002

I've tried a to do a join on the table itself, but I'm only seeing the paired data (with crazy amounts of duplicates)...I cant find the scenarios where an item was sent, but no paired receipt was found.

@Gordon I see unique combinations of `Module`, `User` and `Batch` in that sample. — shawnt00, Feb 02 '16 at 20:42

shawnt00 · Accepted Answer · 2016-02-02T20:49:37.347

0

select *
from
    (select * from T where Action = 'SENT') s
    left outer join
    (select * from T where Action = 'RECEIVED') r
        on r.Module and s.Module and r.User = s.User and r.Batch = s.Batch

From the limited amount of sample data it appears that you can uniquely determine a match by having a common module, user and batch. I'm not sure why you came up with duplicates in your queries. The only other issue appears to be using an outer join to keep the "sends" that don't have a "receive" yet.

I think you still wanted everything in the result. If you did only want the unpaired scenarios then add:

where r.Module is null

edited Feb 02 '16 at 20:49

answered Feb 02 '16 at 20:41

shawnt00

16,443
3
17
22

Actually...this is the correct answer. The response I originally marked as correct worked...but only if there was only one instance of a send/receipt pair not matching....the solution you provided returns all instances of a send without a receipt. Thanks again. – Micah Smith Mar 16 '16 at 14:45

score 0 · Answer 2 · answered Feb 02 '16 at 20:46

0

SELECT
    T1.module,
    T1.user,
    T1.eventdttm,
    T1.action,
    T1.batch
FROM
    My_Table T1
WHERE
    T1.action = 'SENT' AND
    NOT EXISTS
        (
        SELECT *
        FROM My_Table T2
        WHERE
            T2.module = T1.module AND
            T2.user = T1.user AND
            T2.batch = T1.batch AND
            T2.action = 'RECEIVED'
        )

answered Feb 02 '16 at 20:46

Tom H

46,766
14
87
128

Nailed it. Thanks so much, that was perfect. It only returned the rows where there was a "Sent" but no matching Received. Thanks again. – Micah Smith Feb 02 '16 at 21:07

Pairing Send and Receive Data Rows in SQL

2 Answers2

Linked