One thing to note is that there is a bug with the USR5 table in SAP Business One. The time is in military, but if it is before 10 o clock the colon separator gets messed up. To read the time field, all you have to do is move the colon to the left one digit. For example, if the time reads 92:23:10 then move the colon so it reads basically 09:22:31. I used a SAP Note to code the SUBSTRING specially due to this bug, best I could do.
Code: Select all
--CODE BY SBOHUB.COM WITH summary AS ( SELECT T1.U_NAME, LEFT(T0.Date, 12) AS Date, SUBSTRING (CAST ([Time] as char), 1,2) + ':' +SUBSTRING (CAST ([Time] as char), 3,2) + ':' +SUBSTRING (CAST ([Time] as char), 5,2) AS Time, CAST(ROW_NUMBER() OVER(PARTITION BY T0.UserCode ORDER BY T0.Date DESC, T0.Time DESC)AS INT) AS Row FROM USR5 T0 INNER JOIN OUSR T1 ON T0.UserCode = T1.USER_CODE WHERE T0.Date NOT LIKE '%3913%' AND T0.Action = 'I' AND T0.UserCode <> 'B1i') SELECT S.* FROM summary S WHERE Row = 1