Skip to Main Content


General Attendance Logic clarification

M.broJul 13 2022

Hi All,
I am using Oracle DB - 12.1.1. I received one requirement related to Attendance related login hours, break hours, official visit hours, etc.
I can get the data from the biometric device to the SQL server and I will sync the data to the oracle database.
I can receive the data from the SQL server as per the below structure. My requirement is that I can receive multiple times for employee but the mode is different like a break-in or break-out, personal-in or personal-out. some of them click the break-out (to start the break) or some of them click the break-in (to start the break ) there is no control over the devices. The problem is I need to calculate the hours like break hours (multiple break entries for a single day against one employee). and official hours also I need to calculate. Please suggest some sample quey or PLSQL logic also.
Example: employee 2 break-in start 9:30 am to 9:40 (break time 10 minutes),
but employee 3 break-out starts at 08:30 to break-in 08:40 (break time 10 minutes and 11:32 to 11:50 18 minutes) total break hours is 18 minutes employee 3 at 13-06-2022
Note: multiple entries for a break and another mode too.

Thanks & Regards.

Post Details
Added on Jul 13 2022