问SQL -对员工开始时间进行分类。

EN

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

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