I have some typical transaction data: transaction datetime, switch_id, transaction code. looking to count the number of Z transaction codes that have a single A within the previous 10 minutes and then group by switch_id. below the 1st and 4th group would qualify. so count for switch_id 111 = 1 and switch_id 222 = 1. I realize that there could be some crazy nested data (A, A, Z, Z) examples but this is a high level data review and not worried about the 1% of data exceptions. Thanks for any help.

txn_dt, swith_id, txn_code

2021-02-18 13:00:00.770, 111, A
2021-02-18 13:04:00.770, 111, Z

2021-02-18 14:00:00.770, 111, A
2021-02-18 14:15:00.770, 111, Z

2021-02-18 15:00:00.770, 111, B
2021-02-18 16:04:00.770, 111, Z

2021-02-18 13:00:00.770, 222, A
2021-02-18 13:01:00.770, 222, A
2021-02-18 13:04:00.770, 222, Z

expected results

switch 111 - 1
switch 222 - 1

But the last Z does not have a single A within the previous 10 minutes.

Try this:

; WITH CTE AS (
   SELECT txn_dt, prev_dt = LAG(txt_dt) OVER (PARTITION BY switch_id ORDER BY txn_dt),
          switch_id, txn_code, prev_code = LAG(txt_code) OVER (PARTITION BY switch_id ORDER BY txn_dt)
   FROM   tbl
)
SELECT switch_id
FROM   CTE
WHERE  txn_code = 'Z' 
  AND  prev_code = 'A'
  AND  datediff(second, prev_dt, txn_dt) <= 600

@erlandsomm how would i change this if i want to find an A within the last 10 minutes, but not need to be the row directly above the Z?

I would want set of rows to qualify. The A and Z are the same switch and within 10 minutes of each other. But there is a row in between them.

datetime, switch, txt_code
3/1/2020 07:01 123 A
3/1/2020 07:03 123 Q
3/1/2020 07:05 123 Z

That's a completely different query. If I understand you correctly, you want:

SELECT  a.switch_id
FROM    tbl a
WHERE   a.txn_code = 'Z' 
  AND   EXISTS (SELECT *
                FROM   tbl b
                WHERE  b.switch_id = a.switch_id
                  AND  b.txn_code = 'A' 
                  AND  datediff(second, prev_dt, txn_dt) <= 600)

Hi @brenda grossnickle ,

Welcome to Microsoft Q&A!

Please also refer below:

create table trx  
(  
txn_dt datetime,  
switch_id int,  
txn_code varchar(2)  
)  
  
insert into trx values  
('2021-02-18 13:00:00.770', 111, 'A'),  
('2021-02-18 13:04:00.770', 111, 'Z'),  
('2021-02-18 14:00:00.770', 111, 'A'),  
('2021-02-18 14:15:00.770', 111, 'Z'),  
('2021-02-18 15:00:00.770', 111, 'B'),  
('2021-02-18 16:04:00.770', 111, 'Z'),  
('2021-02-18 13:00:00.770', 222, 'A'),  
('2021-02-18 13:01:00.770', 222, 'A'),  
('2021-02-18 13:04:00.770', 222, 'Z')  
  
select 'switch '+ cast(switch_id as char(4))+' - '+cast(count(txn_code) as char(1)) result  
from (SELECT *, next_dt = lead(txn_dt) OVER (PARTITION BY switch_id ORDER BY txn_dt),  
next_code = lead(txn_code) OVER (PARTITION BY switch_id ORDER BY txn_dt)  
FROM trx)a   
where txn_code='A' and next_code='Z'   
and next_dt<=DATEADD(MINUTE,10,txn_dt)  
group by switch_id  

Output:

result  
switch 111 - 1  
switch 222 - 1  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

2021-02-18 13:00:00.770, 222, A
2021-02-18 13:01:00.770, 222, A --- this is within 10 minutes of 13:04
2021-02-18 13:04:00.770, 222, Z

But there is another suitable A at 13:00. It seems that you are interested in Z transactions, which have any number of A (not just a single A) within the previous 10 minutes.