SQL - classify employee arrival times

Question

I have an SQL table that stores the IN and OUT timestamps of each employee.

There are employees who punch the card more than once ( because they don’t hear that the card was read ), other employees who punch the card for any reason (maybe they meet a friend and continue talking for 1 or 2 minutes) they wait a few minutes before they hit again to get to the turnstile.

An example of SQL table data looks like this (only for 3 employees):

Now I would like to classify each time to find out which timestamp is correct, etc.

For instance:

EMP_01 ( ) LAST_LEAVE 00:38:21, , , LAST_LEAVE 00:38:16.

EMP_04 , 4 , 3 IN , , , , , , , , 1 .

, , ? ""?

UPDATE

EMP_04 3 (IN OUT), :

• 9:04:27 am (IN)
• 9:04:35 am (IN)
• 9:04:40 am (IN)

120 , 9:04:27 , , 120 .

, , EMP_04 :

• 9:04:27 am (IN)
• 9:04:35 am (IN)
• 9:08:10 am (IN)

, , 9:04:35. 9: 04.35 9:08:10 2 .

Answer 1

-, @RamRS , , . , :

CREATE TRIGGER TS_FindDups ON dbo.TS FOR INSERT
AS
BEGIN

;WITH DupRecords
AS
(
        SELECT i.EmployeeId, i.[TimeStamp]
          FROM inserted i
    INNER JOIN dbo.TS SRC ON i.EmployeeId = SRC.EmployeeId AND i.EntranceType = SRC.EntranceType
         WHERE SRC.[Status] != 'Inactive'
               /* same day (nice...thanks to RamRS) */
               AND CAST(i.[TimeStamp] AS date) =  CAST(SRC.[TimeStamp] AS date)
               /* only checking newer timestamps (also need this so DATEDIFF <= 2 works correctly) */
               AND i.[TimeStamp] > SRC.[TimeStamp]
               /* newer timestamps are less than two minutes for same Employee, same day, same EntranceType */
               AND DATEDIFF(MINUTE, SRC.[TimeStamp], i.[TimeStamp]) <= 2
)

      UPDATE dbo.TS
         SET [Status] = 'Inactive'
        FROM dbo.TS SRC
  INNER JOIN DupRecords ON SRC.EmployeeId = DupRecords.EmployeeId AND SRC.[TimeStamp] = DupRecords.[TimeStamp]

END

Answer 2

@mdisibio, "" .

:

CREATE TRIGGER trg_FindDups ON tbl_TimeStamp FOR INSERT
AS
BEGIN
    DECLARE @CurrentMaxSwipeTime DATETIME
    DECLARE @SwipeType
    SELECT @SwipeType = EntranceType FROM INSERTED

    IF (@SwipeType = 'O')
    BEGIN
        /* Employee is swiping OUT */
        SELECT @CurrentMaxSwipeTime = MAX(T.TimeStamp) FROM tbl_TimeStamp T 
        WHERE T.EmployeeID = (SELECT EmployeeID FROM INSERTED) /* Same employee */
        AND DATE(T.TimeStamp) = CAST(GETDATE() AS DATE) /* Same date */
        AND DATEDIFF(minute,T.TimeStamp,GETDATE())<2 /* Within 2 minutes */
        AND T.Status <> 'INACTIVE' /* of an active swipe */
        AND T.EntraceType = 'O' /* Swiping out */
        AND T.TimeStamp <> (SELECT TimeStamp FROM INSERTED) /* not the row we just inserted, H/T @mdisibio
        IF @CurrentMaxSwipeTime IS NOT NULL
            /* SET Status to INACTIVE */
            UPDATE tbl_TimeStamp SET Status='INACTIVE' WHERE EmployeeID=(SELECT EmployeeID FROM INSERTED) AND TimeStamp=(SELECT TimeStamp FROM INSERTED)
    END

    ELSE
    BEGIN
        /* Employee is swiping in */
        SELECT @CurrentMaxSwipeTime = MIN(T.TimeStamp) FROM tbl_TimeStamp T 
        WHERE T.EmployeeID = (SELECT EmployeeID FROM INSERTED) /* Same employee */
        AND DATE(T.TimeStamp) = CAST(GETDATE() AS DATE) /* Same date */
        AND DATEDIFF(minute,T.TimeStamp,GETDATE())<2 /* Within 2 minutes */
        AND T.Status <> 'INACTIVE' /* of an active swipe */
        AND T.EntraceType = 'I' /* Swiping in */
        AND T.TimeStamp <> (SELECT TimeStamp FROM INSERTED) /* not the row we just inserted, H/T @mdisibio
        IF @CurrentMaxSwipeTime IS NOT NULL
            /* SET Status to INACTIVE */
            UPDATE tbl_TimeStamp SET Status='INACTIVE' WHERE EmployeeID=(SELECT EmployeeID FROM INSERTED) AND TimeStamp=(SELECT TimeStamp FROM INSERTED)
    END
END

, SQL Server, , .

-
,
Ram

Answer 3

:

• OUT . ?
• IN, OUT. ?

, :

• IN OUT, .
• , .
• , \ .

... . , . , , IN, OUT. IN . OUT .

, , // , . , , .

, :

• FIRST_ENTRANCE - EntranceType .
• LAST_LEAVE - the line of the last timestamp for the employee on with EntranceType O on a specific day for all employees. Check that all other employees on this day do not have this on the same day before installing it.
• FIRST_IN - the first line of the timeline for an employee on a with EntranceType I on a specific day for all employees. Check that all other employees on this day do not have this on the same day before installing it.