如何解决刷卡表中的双重条目? [英] How to account for double entries in a card swipe table?

问题描述

如何解决刷卡表中的意外刷卡问题?我尝试通过在输入时将它们分组来选择 max(time_cst).它没有解决问题.

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

EmpID   Enter/Exit  Time 
 2999   Entry   06:00AM 
 2999   Entry   06:01AM 
 2999   Exit    12:00PM
 2999   Entry   01:00PM 
 2999   Exit    03:00PM 
 2999   Entry   04:00PM
 2999   Exit    06:00PM

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

EmpID   EntryTime  ExitTime
 2999   06:01AM    12:00PM
 2999   01:00PM    03:00PM
 2999   04:00PM    06:00PM

解决方案

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.

DROP TABLE IF EXISTS #Table1
CREATE TABLE #Table1
    ([Name] varchar(9), [TimeATT] int, [DateTime] varchar(19))
;

INSERT INTO #Table1
    ([Name], [TimeATT], [DateTime])
VALUES
    ('jane doe', 1, '2019-04-23T08:00:00'),
    ('jane doe', 1, '2019-04-23T08:01:01'),
    ('jane doe', 2, '2019-04-23T12:00:00'),
    ('jane doe', 1, '2019-04-23T12:05:00'),

    ('john doe', 1, '2019-04-23T08:00:00'),
    ('john doe', 2, '2019-04-23T09:00:01'),
    ('john doe', 1, '2019-04-23T09:05:00'),
    ('john doe', 2, '2019-04-23T12:00:00')

SELECT [Name],[EntryTime], [ExitTime]
 FROM ( -- sub query to get matching exit time for each entry if it exists
        SELECT 
         [Name],
         [DateTime] as EntryTime,
         LEAD([DateTime], 1, NULL) OVER(PARTITION BY [Name] ORDER BY [DateTime]) AS ExitTime
         ,TimeATT, [DateTime]
        FROM ( -- subquery to exclude duplicate records
                SELECT * FROM ( -- subquery to identify records to ignore
                              SELECT 
                                  [Name], [TimeATT], [DateTime],
                                  CASE LEAD(TimeATT, 1, 0) OVER(PARTITION BY [Name] ORDER BY [DateTime]) 
                                    WHEN TimeATT THEN 1
                                    ELSE 0
                                  END AS Exclude
                              FROM #Table1) a
                              WHERE Exclude = 0
              ) t
        ) z
  WHERE [TimeATT] = 1 -- filter so left column is always entry time.
  ORDER BY [Name], [DateTime]

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..

这篇关于如何解决刷卡表中的双重条目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！