CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Learn
Learn

Reputation: 548

SQL compare with a dynamic time for each Partition item

I want to find all transactions that happen within 0-5 minutes before its bad transaction, with Partition by cardkey. For example, I need to compare all cardley=999's trans_time with bad_trans_time (5/27/18 6:42 AM)

sample data

trans_key   trans_time       cardkey    bad_trans_key   bad_trans_time
1         4/15/18 2:00 AM      999        NULL           NULL
2         4/15/18 2:01 AM      999        NULL           NULL
3         5/27/18 6:38 AM      999        NULL           NULL
4         5/27/18 6:39 AM      999        NULL           NULL
5         5/27/18 6:40 AM      999        NULL           NULL
6         5/27/18 6:41 AM      999        NULL           NULL
7         5/27/18 6:42 AM      999         7       5/27/18 6:42 AM
8         5/27/18 6:43 AM      999        NULL           NULL
9         3/24/18 2:36 AM      333        NULL           NULL
10        3/24/18 2:37 AM      333        NULL           NULL
11        3/24/18 2:38 AM      333        NULL           NULL
12        3/24/18 2:39 AM      333         12      3/24/18 6:39 AM
13        3/24/18 2:40 AM      333        NULL           NULL

expected output

trans_key   trans_time       cardkey    bad_trans_key   bad_trans_time
3         5/27/18 6:38 AM      999        NULL           NULL
4         5/27/18 6:39 AM      999        NULL           NULL
5         5/27/18 6:40 AM      999        NULL           NULL
6         5/27/18 6:41 AM      999        NULL           NULL
8         5/27/18 6:43 AM      999        NULL           NULL
9         3/24/18 2:36 AM      333        NULL           NULL
10        3/24/18 2:37 AM      333        NULL           NULL
11        3/24/18 2:38 AM      333        NULL           NULL

Upvotes: 0

Views: 124

Answers (2)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

Another method uses a window function:

WITH 
  BadTransTime AS (
    SELECT trans_key, trans_time, cardkey, bad_trans_key, bad_trans_time
      , MAX(bad_trans_time) OVER (PARTITION BY cardkey) AS cardkey_bad_trans_time
    FROM t
  )
SELECT trans_key, trans_time, cardkey, bad_trans_key, bad_trans_time
FROM BadTransTime 
WHERE DATEADD(minute, 5, bad_trans_time) >= cardkey_bad_trans_time
  AND bad_trans_time < cardkey_bad_trans_time

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

One method uses exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.cardkey = t.cardkey and
                    t2.bad_trans_time is not null and
                    t.trans_time >= dateadd(minute, -5, t2.bad_trans_time) and
                    t.trans_time <= t2.bad_trans_time
             );

Upvotes: 1

Related Questions