SQL query to compute longest travel time for each id number

I am a novice in SQL and I have a problem as follows.

There are two tables

TRNSCTN_TAB:

CREATE TABLE TRNSCTN_TAB(
    `transactionId` int
    , `UniqueId` int
    , `transactionTimeStamp` varchar(17)
    , `transactionAmt` int
    , `personalId` int
    , `merchantId` int
);

Note: UniqueId, personalId, merchantId are not necessarily unique for all the records.

Data sample:

transactionId   | UniqueId  | transactionTimeStamp  | transactionAmt    | personalId    | merchantId
1               | 2340      | 20150123 10:00:00     | 300.0             | 100           | 301
2               | 2340      | 20150124 09:00:00     | 30.0              | 100           | 302
3               | 2340      | 20150124 14:30:00     | 301.0             | 100           | 303
4               | 2340      | 20150125 12:00:00     | 31.0              | 100           | 302
5               | 2341      | 20150125 14:30:00     | 200               | 101           | 303
6               | 2341      | 20150125 23:00:00     | 33                | 101           | 304
7               | 2341      | 20150125 23:30:00     | 23                | 100           | 303
8               | 2341      | 20150126 06:00:00     | 300               | 101           | 303
9               | 2341      | 20150126 06:30:00     | 300               | 101           | 303
10              | 2342      | 20150126 11:00:00     | 30                | 102           | 304
11              | 2342      | 20150126 16:00:00     | 310               | 102           | 304
12              | 2342      | 20150127 06:00:00     | 120               | 102           | 305
13              | 2342      | 20150127 16:00:00     | 300               | 102           | 304
14              | 2342      | 20150128 03:00:00     | 30                | 102           | 305

ATTRXN_TAB:

CREATE TABLE ATTRXN_TAB(
    `perosnalId` int
    , `merchantId` int
);

Data sample:

```
personalId | merchantId
100 | 301
100 | 302
100

As far as I understood the question with what we have so far, I think this first query returns the correct date within 24h.

Query:

SELECT UniqueId, transactionTimeStamp
  , DATE_ADD(transactionTimeStamp, INTERVAL -24 hour)
    , (
        SELECT max(transactionTimeStamp) as transactionTimeStamp 
        FROM TRNSCTN_TAB s
        WHERE s.personalId IN (100) AND s.merchantId IN (301, 302)
            AND UniqueId = s.UniqueId
            AND transactionTimeStamp > tt.transactionTimeStamp 
            AND transactionTimeStamp <= DATE_ADD(transactionTimeStamp, INTERVAL 24 hour)
    ) mx
FROM TRNSCTN_TAB tt
INNER JOIN ATTRXN_TAB at 
    ON at.personalId = tt.personalId AND at.merchantId = tt.merchantId
WHERE tt.personalId IN (100) AND tt.merchantId IN (301, 302)
;

It returns the MAX(transactionTimeStamp) for each row with a set of personalId / merchantId using a correlated subquery. You can look for SQL correlated subquery on Google or another search engine.

Output:

UniqueId    | transactionTimeStamp      | mxd
2340        | 2015-01-23 10:00:00.000   | 2015-01-24 09:00:00.000
2340        | 2015-01-24 09:00:00.000   | NULL
2340        | 2015-01-25 12:00:00.000   | NULL

Then it only has to GROUP BY UniqueId and return the MAX(TIMEDIFF(...)):

SELECT UniqueId
    ,  DATE_FORMAT(MAX(TIMEDIFF(mxd, transactionTimeStamp)),'%H:%i:%s')
FROM 
    SELECT UniqueId, transactionTimeStamp 
        , (
            SELECT max(transactionTimeStamp) as transactionTimeStamp 
            FROM TRNSCTN_TAB s
            WHERE s.personalId IN (100) AND s.merchantId IN (301, 302)
                AND UniqueId = s.UniqueId
                AND transactionTimeStamp > tt.transactionTimeStamp 
                AND transactionTimeStamp <= DATE_ADD(tt.transactionTimeStamp, INTERVAL 24 hour)
        ) mxd
    FROM TRNSCTN_TAB tt
    INNER JOIN ATTRXN_TAB at 
        ON at.personalId = tt.personalId AND at.merchantId = tt.merchantId
    WHERE tt.personalId IN (100) AND tt.merchantId IN (301, 302)
) mx
GROUP BY UniqueId
;

It will only return a rows for each UniqueId if there is a valid period. Otherwise it return no rows:

UniqueId    | 
2340        | 23:00:00.0000000

SQL Fiddle with both queries.

StackExchange Database Administrators Q#131881, answer score: 4