Skip to content
Advertisement

How to account for double entries in a card swipe table?

How do I account for accidental swipes in a card swipe table? I tried selecting the max(time_cst) by grouping them on entry. It did not solve the problem. http://www.sqlfiddle.com/#!18/06cc8/2

This is how I want it to show up. http://www.sqlfiddle.com/#!18/5cfbb/2

Advertisement

Answer

I’ve added a fiddle to show this working but I’ll copy the query here for future reference.

http://www.sqlfiddle.com/#!18/87593fe/12/0

Below is very close to the fiddle version, just using a temp table instead of a permanent table.

Also, I edited the sample data as there were extra spaces between the names on the last two records. I assume this was in error.

Note The LEAD function needs to partitioned by Name to avoid excluding rows where the TimeATT column is the same on two consecutive rows but for different people.

I used LEAD instead of LAG as your sample returned the 2nd “Entry” record when duplicates were found.

This could be written more elegantly with CTEs but this does work.

Here is the final result..

enter image description here

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement