Rollback HandPunch Templates

This topic is for advanced users

When an existing HandPunch is replaced with a new HandPunch, there can be a difference in the calibration from the existing to the new clock. As such, a hand template that previously worked can now have a problem punching at the new clock. It is not the clock that is causing the issue, but the hand template. This occurs because the hand template adjusts to the clocks' gradual change in calibration. If a small number of employees are affected, it is easiest to remove and [[HandPunch Enrollment|re-enroll] the employees. If a large number of employees are affected then the templates can be rolled back to an older template. This is possible because the hand template is retrieved from the HandPunch for every punch that is polled from the clock. The rollback procedure needs to be performed in SQL Management Studio.

It is best to rollback templates only for employees that were having biometric issues. An example of a query that selects TRY AGAIN or ID REFUSED clock punches is: <nowiki> SELECT * FROM RawPunch WHERE RawPunchType = 3 OR RawPunchType = 6

This is a more advanced query that limits the results to a date range and to a single time clock:

SELECT * FROM RawPunch WHERE (RawPunchType = 3 OR RawPunchType = 6) AND RawPunchDateTime > '05/05/2015 22:00' AND RawPunchDateTime < '05/06/2015 09:00' AND FkTimeClockID = 876861399

Once the subset of employees have been identified then the full query needs to update the Employee Clock ID (EmployeeClockID) table with the template information from the Raw Punch table (RawPunch), joining on the employee key (FkEmployeeID), and ensuring that we use only successful punches (RawPunchType = 7) from a given date range (RawPunchDateTime), and finally, filtering the scope of this query to the employees returned in the above query.

UPDATE EmployeeClockID SET HandPunchTemplate0 = RawPunch.HandPunchTemplate0, HandPunchTemplate1 = RawPunch.HandPunchTemplate1, HandPunchTemplate2 = RawPunch.HandPunchTemplate2, HandPunchTemplate3 = RawPunch.HandPunchTemplate3, HandPunchTemplate4 = RawPunch.HandPunchTemplate4, HandPunchTemplate5 = RawPunch.HandPunchTemplate5, HandPunchTemplate6 = RawPunch.HandPunchTemplate6, HandPunchTemplate7 = RawPunch.HandPunchTemplate7, HandPunchTemplate8 = RawPunch.HandPunchTemplate8 FROM EmployeeClockID INNER JOIN RawPunch ON EmployeeClockID.FkEmployeeID = RawPunch.FkEmployeeID WHERE RawPunch.RawPunchType = 7 AND RawPunch.RawPunchDateTime > '02/01/2015' AND RawPunch.RawPunchDateTime < '03/01/2015' AND RawPunch.HandPunchTemplate0 <> 0 AND RawPunch.HandPunchTemplate0 <> 255 AND EmployeeClockID.FkEmployeeID IN

(SELECT FkEmployeeID FROM RawPunch WHERE (RawPunchType = 3 OR RawPunchType = 6) AND RawPunchDateTime > '05/05/2015 22:00' AND RawPunchDateTime < '05/06/2015 09:00' AND FkTimeClockID = 876861399)

Note that we are using the templates from successful punches made on any time clock. The rollback procedure is best done for a range on one month, moving back a month at a time.