SQL Script to get report of Last Swipe Per Employee on Access Control

WITH RankedData AS ( SELECT EmpID, CardNumber, DatePart, Time, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY DatePart DESC) AS rn FROM sy_ac_data ) SELECT e.Code, e.Lastname, e.Firstname, r.CardNumber, FORMAT(r.DatePart, 'dd-MM-yyyy') AS Date, -- Convert Time (seconds since midnight) to HH:mm:ss RIGHT('0' + CAST(r.Time / 3600 AS VARCHAR(2)), 2) + ':' + -- Hours RIGHT('0' + CAST((r.Time % 3600) / 60 AS VARCHAR(2)), 2) + ':' + -- Minutes RIGHT('0' + CAST(r.Time % 60 AS VARCHAR(2)), 2) AS RawTimeFormatted -- Seconds FROM RankedData r JOIN Employees e ON r.EmpID = e.ID WHERE r.rn = 1 ORDER BY e.Lastname, e.Firstname;

***** TO save this as a SQL VIEW ****

if you add this as a SQL View run this script then you can use the attached custom report imported to add in report builder to have it as a report on demand.

CREATE VIEW Last_AC_Swipe AS WITH RankedData AS ( SELECT EmpID, CardNumber, DatePart, Time, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY DatePart DESC) AS rn FROM sy_ac_data ) SELECT e.Code, e.Lastname, e.Firstname, r.CardNumber, FORMAT(r.DatePart, 'dd-MM-yyyy') AS Date, -- Convert Time (seconds since midnight) to HH:mm:ss RIGHT('0' + CAST(r.Time / 3600 AS VARCHAR(2)), 2) + ':' + -- Hours RIGHT('0' + CAST((r.Time % 3600) / 60 AS VARCHAR(2)), 2) + ':' + -- Minutes RIGHT('0' + CAST(r.Time % 60 AS VARCHAR(2)), 2) AS RawTimeFormatted -- Seconds FROM RankedData r JOIN Employees e ON r.EmpID = e.ID WHERE r.rn = 1