0

could anyone help me with this:

The records are from attendance tracking software. Any employee could accidentally check in or check out more than once, and even worse (first 3 rows) - on different readers. Entrance: 1101, 1102. Exit: 101, 102.

SELECT * FROM `attendance` WHERE ATUserID = 856 AND (ATReaderID = 101 OR ATReaderID = 102 OR ATReaderID = 1101 OR ATReaderID = 1102);
+--------+------------+----------+----------+------------+
| ATid   | ATDate     | ATTime   | ATUserID | ATReaderID |
+--------+------------+----------+----------+------------+
| 403396 | 2017-05-08 | 07:55:20 |      856 |       1102 |
| 403399 | 2017-05-08 | 07:55:22 |      856 |       1102 |
| 403450 | 2017-05-08 | 07:55:40 |      856 |       1101 |
| 407934 | 2017-05-08 | 16:11:40 |      856 |        102 |
| 407940 | 2017-05-08 | 16:11:45 |      856 |        102 |
| 409990 | 2017-05-09 | 07:56:35 |      856 |       1102 |
| 411490 | 2017-05-09 | 07:56:40 |      856 |       1102 |
| 413525 | 2017-05-09 | 15:59:40 |      856 |        102 |
| 413583 | 2017-05-09 | 15:59:52 |      856 |        102 |
| 413603 | 2017-05-10 | 07:54:50 |      856 |       1101 |
| 422315 | 2017-05-10 | 16:01:00 |      856 |        101 |
+--------+------------+----------+----------+------------+

I would like to get only the last one event in every sequence (ATReaderID):

+--------+------------+----------+----------+------------+
| ATid   | ATDate     | ATTime   | ATUserID | ATReaderID |
+--------+------------+----------+----------+------------+
| 403450 | 2017-05-08 | 07:55:40 |      856 |       1101 |
| 407940 | 2017-05-08 | 16:11:45 |      856 |        102 |
| 411490 | 2017-05-09 | 07:56:40 |      856 |       1102 |
| 413583 | 2017-05-09 | 15:59:52 |      856 |        102 |
| 413603 | 2017-05-10 | 07:54:50 |      856 |       1101 |
| 422315 | 2017-05-10 | 16:01:00 |      856 |        101 |
+--------+------------+----------+----------+------------+

Can't rely on date column (last entrance and exit for each day) because of night shifts (entrance today / exit tomorrow), and allowed multiply entrances/exits per day.

Tried to workout with this solution Retrieving the last record in each group, unsuccessfully.

Hello World
  • 2,673
  • 7
  • 28
  • 60
beba
  • 82
  • 9

1 Answers1

0

Use a variable to compare the next record's reader type with the current:

select
  atid, atdate, attime, atuserid, atreaderid
from
(
  select 
    atid, atdate, attime, atuserid, atreaderid, 
    (atreaderid div 1000 <> @after div 1000) as keepit,
    @after := atreaderid
  from attendance
  cross join (select @after := 2000) var
  where atuserid = 856 and atreaderid in (101, 102, 1101, 1102)
  order by timestamp(atdate, attime) desc, atid desc
) marked
where keepit
order by timestamp(atdate, attime), atid;

Demo: http://rextester.com/ZCPUB70643

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Thank you for the quick response. At first glance, this is the working solution. Unfortunately something I can't explain is going wrong. Please take a look.... will edit your answer - can't paste tables in the comment. – beba May 25 '17 at 07:19
  • By the way, it's not obviously. More than 99% results are ok. – beba May 25 '17 at 07:31
  • This is because you introduced a new situation: records with a datetime tie. I've added the `ATid` to the order by clauses to deal with these. – Thorsten Kettner May 25 '17 at 12:27
  • Thank You Thorsten! – beba May 25 '17 at 12:44