SQL query to capture data with self join

Question

Example Data:

Id  | SwipeTime               | SwipeStatus | Invalid_message
----+-------------------------+-------------+-----------------
100 | 2018-04-07 02:45:12.290 | Invalid     | 1st Attempt
100 | 2018-04-07 02:45:14.290 | Invalid     | 2nd Attempt
100 | 2018-04-07 02:45:21.290 | Valid       | NA
100 | 2018-04-07 18:01:51.980 | Valid       | NA
101 | 2018-04-07 02:55:12.290 | Invalid     | 1st Attempt
101 | 2018-04-07 02:55:14.290 | Invalid     | 2nd Attempt
101 | 2018-04-07 18:01:51.980 | Valid       | NA

DDL:

DECLARE @tbl TABLE
             (
                 Id INT, 
                 SwipeTime DATETIME, 
                 SwipeStatus VARCHAR(10), 
                 Invalid_message VARCHAR(20)
             );

INSERT INTO @tbl 
VALUES (100, '2018-04-07 02:45:12.290', 'Invalid', '1st Attempt'),
       (100, '2018-04-07 02:45:14.290', 'Invalid', '2nd Attempt'),
       (100, '2018-04-07 02:45:21.290', 'Valid', 'NA'),
       (100, '2018-04-07 18:01:51.980', 'Valid', 'NA'),
       (101, '2018-04-07 02:55:12.290', 'Invalid', '1st Attempt'),
       (101, '2018-04-07 02:55:14.290', 'Invalid', '2nd Attempt'),
       (101, '2018-04-07 18:01:51.980', 'Valid', 'NA');

Employee 100 came to work at 2.45 and tried swiping 2 times and it didn't go through (invalid). But the third time he swiped, it worked. He went to work and swiped-out around 6.

Employee 101 came to work at 2.55, swiped two times and it did not work. He still went to work and swiped-out around 6.

I want to capture employees like 101 for a particular day. i.e., for a particular day, take the timestamp of an employee's first invalid attempt, check if within 5 minutes of his first attempt if a valid attempt was made. If not, we want his details in output.

Can this be done with a single select query? Since I am not able to write a single query, I am right now writing a procedure to do this. But I will very much like it if it can be done in a single query.

Answer 1

Try this query:

Sample data:

declare @tbl table (Id int, SwipeTime datetime, SwipeStatus varchar(10), Invalid_message varchar(20));
insert into @tbl values
(100,'2018-04-07 02:45:12.290','Invalid','1st Attempt'),
(100,'2018-04-07 02:45:14.290','Invalid','2nd Attempt'),
(100,'2018-04-07 02:45:21.290','Valid','NA'),
(100,'2018-04-07 18:01:51.980','Valid','NA'),
(101,'2018-04-07 02:55:12.290','Invalid','1st Attempt'),
(101,'2018-04-07 02:55:14.290','Invalid','2nd Attempt'),
(101,'2018-04-07 18:01:51.980','Valid','NA');

T-SQL:

select id from @tbl t
where Invalid_message = '1st Attempt'
  and not exists(select 1 from @tbl
                 where id = t.id 
                   and SwipeStatus = 'Valid'
                   and SwipeTime between t.SwipeTime and dateadd(minute, 5, t.SwipeTime))