Results 1 to 1 of 1
  1. #1
    Join Date
    May 2016
    Posts
    1

    Unhappy Unanswered: need help to clean Time In&Time Out from attendance biometric records

    currently the attach file contains the list of multiple check-in and check-out for single user at the same day

    I try this approach but it does not satisfy my expected result

    SELECT EnrollNumber
    ,DateOfCheckInOut= cast(dateinout as date)
    ,MinCheckIn=MIN(CASE InOutMode WHEN 0 THEN DateInOut END)
    ,MaxCheckOut=MAX(CASE InOutMode WHEN 1 THEN DateInOut END)
    FROM tblCheckInOut WHERE
    CAST(DateInOut AS DATE) >= DATEADD(dd,-DAY(cast(getdate() as date)) + 1, cast(getdate() as date))
    AND CAST(DateInOut AS DATE) < DATEADD(dd,DAY(DATEADD(mm, 1, cast(getdate() as date))), DATEADD(mm, 1, cast(getdate()as date)))
    GROUP BY cast(DateInOut as date), EnrollNumber;

    Rersult:

    EnrollNumber DateOfCheckInOut MinCheckIn MaxCheckOut
    1306 2016-05-03 2016-05-03 05:17:08 NULL
    1306 2016-05-05 2016-05-05 05:33:56 NULL
    1306 2016-05-10 2016-05-10 05:55:40 NULL
    1306 2016-05-11 2016-05-11 05:43:20 NULL
    1306 2016-05-12 2016-05-12 05:52:54 NULL
    1306 2016-05-16 NULL 2016-05-16 05:31:04
    1306 2016-05-17 2016-05-17 12:51:54 2016-05-17 05:34:33
    1306 2016-05-23 NULL 2016-05-23 05:35:31
    1306 2016-05-24 2016-05-24 05:31:15 NULL
    1306 2016-05-25 2016-05-25 05:34:40 NULL
    1306 2016-05-26 NULL 2016-05-26 05:33:18


    also I try this second solution

    DECLARE @MissedPunchThreshold int
    SET @MissedPunchThreshold = 20


    ;with attendance As (SELECT
    EnrollNumber
    ,DateInOut
    ,PunchSequence = ROW_NUMBER() OVER(PARTITION BY EnrollNumber ORDER BY DateInOut)
    FROM tblCheckInOut),
    EmployeeTimeCTE AS (
    SELECT
    checkIn.EnrollNumber
    ,CAST(checkIn.DateInOut AS DATE) AS CheckDate
    ,checkIn.DateInOut AS Time_In
    ,checkOut.DateInOut AS Time_Out
    ,DateDiff(hour, checkIn.DateInOut, checkOut.DateInOut) AS HoursBetweenPunch
    ,checkOut.PunchSequence AS PunchOutSequence
    FROM attendance AS checkIn
    LEFT OUTER JOIN attendance AS checkOut
    ON checkIn.EnrollNumber = checkOut.EnrollNumber
    AND checkOut.PunchSequence = checkIn.PunchSequence + 1
    WHERE checkIn.PunchSequence = 1
    UNION ALL
    SELECT
    ETS_In.EnrollNumber
    ,CAST(ETS_In.DateInOut AS DATE) AS CheckDate
    ,ETS_In.DateInOut AS Time_In
    ,ETS_Out.DateInOut AS Time_Out
    ,DateDiff(hour, ETS_In.DateInOut, ETS_Out.DateInOut) AS HoursBetweenPunch
    ,ETS_Out.PunchSequence AS PunchOutSequence
    FROM attendance AS ETS_In
    INNER JOIN EmployeeTimeCTE ET
    ON ET.EnrollNumber = ETS_In.EnrollNumber
    AND ETS_In.PunchSequence =
    CASE
    WHEN ET.HoursBetweenPunch > @MissedPunchThreshold
    THEN ET.PunchOutSequence
    ELSE ET.PunchOutSequence + 1
    END
    INNER JOIN attendance AS ETS_Out
    ON ETS_In.EnrollNumber = ETS_Out.EnrollNumber
    AND ETS_Out.PunchSequence = ETS_In.PunchSequence + 1

    )
    SELECT
    EnrollNumber
    ,CheckDate
    ,Time_In
    ,CASE WHEN HoursBetweenPunch > @MissedPunchThreshold THEN NULL ELSE Time_Out END AS Time_Out
    ,CASE WHEN HoursBetweenPunch > @MissedPunchThreshold THEN NULL ELSE HoursBetweenPunch END AS HoursBetweenPunch
    FROM EmployeeTimeCTE
    ORDER BY EnrollNumber, CheckDate
    OPTION (MAXRECURSION 1000)


    Please help me...
    Thanks
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •