Forum Stats

  • 3,838,255 Users
  • 2,262,346 Discussions
  • 7,900,558 Comments

Discussions

General Attendance Logic clarification

M.bro
M.bro Member Posts: 128 Blue Ribbon

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.

M.bro.

Answers

  • Stax
    Stax Member Posts: 120 Bronze Badge

    hi, M.bro

    1) why for 3  total break hours is 18 minutes? (10+18=28)

    2) are there always in/out pairs?

    fff,xxx_out,10:15

    fff,xxx_out,10:20

    fff,xxx_in,,10:35

    is it permissible?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,228 Red Diamond

    Hi, @M.bro

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. For example: "I need to output one row per employee with the total time between the mode='IN' row and the mode='OUT' row. If either of those is missing, then ... I need to subtract break and personal times. Since some employees are inconsistent about how they record breaks, I want to consider the first 'BREAK IN' or 'BREAK OUT' as the start of the break, and the second 'BREAK IN' or 'BREAK OUT' as the end of the break. If they take another break, then the third 'BREAK IN' or 'BREAK OUT' is the start of the second break, and the fourth 'BREAK IN' or 'BREAK OUT' is the end of that break. If there are an odd number of 'BREAK IN's and 'BREAK OUT's, then. 'PERSONAL IN' and 'PERSONAL OUT' work the same way. If a normal break overlaps with a personal bnreak, then ..."

  • M.bro
    M.bro Member Posts: 128 Blue Ribbon

    @Stax


    thanks for your reply.

    1.) Sorry not 18 minutes you are correct 28 minutes.

    2) are there always in/out pairs? - Not required always.

    3) Yes I mentioned already there is no control over the machine. people will click break in or break out to start the break.

  • Stax
    Stax Member Posts: 120 Bronze Badge

    Example: 

    fff,IN,10:00

    fff,xxx_out,10:15

    fff,xxx_out,10:20

    fff,xxx_in,,10:35

    fff,OUT,11:00

    how to calculate a break?

    ps

    there is always an in/out pair (start/end)

  • M.bro
    M.bro Member Posts: 128 Blue Ribbon

    @Frank Kulash


    thanks for your time and your detailed message. I will prepare the script for sure. i will share.

    Output :

    note : if there only in and no out time then I will not show the total hours and total working hours.


  • M.bro
    M.bro Member Posts: 128 Blue Ribbon

    @Stax


    Example: 

    fff,IN,10:00

    fff,xxx_out,10:15

    fff,xxx_out,10:20

    fff,xxx_in,,10:35

    fff,OUT,11:00

    how to calculate a break?

    ps

    there is always an in/out pair (start/end) - Not really required( the reason is some went official out they will not come to the office).

    The above-mentioned sync. the first thing we need to calculate like below is


  • M.bro
    M.bro Member Posts: 128 Blue Ribbon

    @Frank Kulash


    As we discussed previously simple script i created.


    create table attendance_hr (emp_id number,emp_name varchar2(500),logtime date,mode_type varchar2(100),created_date date);


    insert into attendance_hr VALUES (1,'aaa',TO_DATE('13-06-2022 07:00:00','DD-MM-RRRR hh24:mi:ss'),'IN',TO_DATE('13-06-2022 07:00:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (1,'aaa',TO_DATE('13-06-2022 13:00:00','DD-MM-RRRR hh24:mi:ss'),'OUT',TO_DATE('13-06-2022 13:05:00','DD-MM-RRRR hh24:mi:ss'))


    insert into attendance_hr VALUES (2,'BBB',TO_DATE('13-06-2022 07:20:00','DD-MM-RRRR hh24:mi:ss'),'IN',TO_DATE('13-06-2022 07:20:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (2,'BBB',TO_DATE('13-06-2022 09:00:00','DD-MM-RRRR hh24:mi:ss'),'BREAK IN',TO_DATE('13-06-2022 09:01:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (2,'BBB',TO_DATE('13-06-2022 09:18:00','DD-MM-RRRR hh24:mi:ss'),'BREAK OUT',TO_DATE('13-06-2022 09:19:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (2,'BBB',TO_DATE('13-06-2022 15:40:00','DD-MM-RRRR hh24:mi:ss'),'OUT',TO_DATE('13-06-2022 15:45:00','DD-MM-RRRR hh24:mi:ss'));




    insert into attendance_hr VALUES (3,'CCC',TO_DATE('13-06-2022 07:50:00','DD-MM-RRRR hh24:mi:ss'),'IN',TO_DATE('13-06-2022 07:50:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (3,'CCC',TO_DATE('13-06-2022 08:30:00','DD-MM-RRRR hh24:mi:ss'),'BREAK OUT',TO_DATE('13-06-2022 08:30:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (3,'CCC',TO_DATE('13-06-2022 08:40:00','DD-MM-RRRR hh24:mi:ss'),'BREAK IN',TO_DATE('13-06-2022 08:40:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (3,'CCC',TO_DATE('13-06-2022 11:32:00','DD-MM-RRRR hh24:mi:ss'),'BREAK IN',TO_DATE('13-06-2022 11:32:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (3,'CCC',TO_DATE('13-06-2022 11:50:00','DD-MM-RRRR hh24:mi:ss'),'BREAK OUT',TO_DATE('13-06-2022 11:50:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (3,'CCC',TO_DATE('13-06-2022 14:40:00','DD-MM-RRRR hh24:mi:ss'),'OUT',TO_DATE('13-06-2022 14:40:00','DD-MM-RRRR hh24:mi:ss'));



    insert into attendance_hr VALUES (4,'DDD',TO_DATE('13-06-2022 07:50:00','DD-MM-RRRR hh24:mi:ss'),'IN',TO_DATE('13-06-2022 07:50:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (4,'DDD',TO_DATE('13-06-2022 08:30:00','DD-MM-RRRR hh24:mi:ss'),'PERSONAL IN',TO_DATE('13-06-2022 09:50:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (4,'DDD',TO_DATE('13-06-2022 08:50:00','DD-MM-RRRR hh24:mi:ss'),'PERSONAL OUT',TO_DATE('13-06-2022 09:50:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (4,'DDD',TO_DATE('13-06-2022 08:52:00','DD-MM-RRRR hh24:mi:ss'),'BREAK OUT',TO_DATE('13-06-2022 09:50:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (4,'DDD',TO_DATE('13-06-2022 09:10:00','DD-MM-RRRR hh24:mi:ss'),'BREAK IN',TO_DATE('13-06-2022 09:50:00','DD-MM-RRRR hh24:mi:ss'));


    insert into attendance_hr VALUES (4,'DDD',TO_DATE('13-06-2022 14:50:00','DD-MM-RRRR hh24:mi:ss'),'OUT',TO_DATE('13-06-2022 14:50:00','DD-MM-RRRR hh24:mi:ss'));

    thanks & regards,

    M.bro

  • Stax
    Stax Member Posts: 120 Bronze Badge

    a lot of nuances

    I don't understand why there is no break (10:20-10:15)+(11:00-10:35)?

    how to count for?

    IN,10:00

    xxx_in,10:17

    yyy_in,10:20

    xxx_out,10:30

    yyy_in,10:35

    zzz_in,11:00

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,228 Red Diamond

    Hi, @M.bro

    Thanks for posting the sample data. Don't forget to post the exact results you want from the given sample data.

    In your sample data, there is never more than one row with mode_type='IN' for the same emp_id. Will yur table every contain multiple 'IN's for the same emp_id? If so, the sample data and results should show what you want in that case.

  • Stax
    Stax Member Posts: 120 Bronze Badge

    template/workpiece

    with attendance_hr (emp_id,emp_name,logtime,mode_type,created_date) as (
    select 1,'aaa',TO_DATE('13-06-2022 07:00:00','DD-MM-RRRR hh24:mi:ss'),'IN',TO_DATE('13-06-2022 07:00:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 1,'aaa',TO_DATE('13-06-2022 13:00:00','DD-MM-RRRR hh24:mi:ss'),'OUT',TO_DATE('13-06-2022 13:05:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 2,'BBB',TO_DATE('13-06-2022 07:20:00','DD-MM-RRRR hh24:mi:ss'),'IN',TO_DATE('13-06-2022 07:20:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 2,'BBB',TO_DATE('13-06-2022 09:00:00','DD-MM-RRRR hh24:mi:ss'),'BREAK IN',TO_DATE('13-06-2022 09:01:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 2,'BBB',TO_DATE('13-06-2022 09:18:00','DD-MM-RRRR hh24:mi:ss'),'BREAK OUT',TO_DATE('13-06-2022 09:19:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 2,'BBB',TO_DATE('13-06-2022 15:40:00','DD-MM-RRRR hh24:mi:ss'),'OUT',TO_DATE('13-06-2022 15:45:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 3,'CCC',TO_DATE('13-06-2022 07:50:00','DD-MM-RRRR hh24:mi:ss'),'IN',TO_DATE('13-06-2022 07:50:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 3,'CCC',TO_DATE('13-06-2022 08:30:00','DD-MM-RRRR hh24:mi:ss'),'BREAK OUT',TO_DATE('13-06-2022 08:30:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 3,'CCC',TO_DATE('13-06-2022 08:40:00','DD-MM-RRRR hh24:mi:ss'),'BREAK IN',TO_DATE('13-06-2022 08:40:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 3,'CCC',TO_DATE('13-06-2022 11:32:00','DD-MM-RRRR hh24:mi:ss'),'BREAK IN',TO_DATE('13-06-2022 11:32:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 3,'CCC',TO_DATE('13-06-2022 11:50:00','DD-MM-RRRR hh24:mi:ss'),'BREAK OUT',TO_DATE('13-06-2022 11:50:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 3,'CCC',TO_DATE('13-06-2022 14:40:00','DD-MM-RRRR hh24:mi:ss'),'OUT',TO_DATE('13-06-2022 14:40:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 4,'DDD',TO_DATE('13-06-2022 07:50:00','DD-MM-RRRR hh24:mi:ss'),'IN',TO_DATE('13-06-2022 07:50:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 4,'DDD',TO_DATE('13-06-2022 08:30:00','DD-MM-RRRR hh24:mi:ss'),'PERSONAL IN',TO_DATE('13-06-2022 09:50:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 4,'DDD',TO_DATE('13-06-2022 08:50:00','DD-MM-RRRR hh24:mi:ss'),'PERSONAL OUT',TO_DATE('13-06-2022 09:50:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 4,'DDD',TO_DATE('13-06-2022 08:52:00','DD-MM-RRRR hh24:mi:ss'),'BREAK OUT',TO_DATE('13-06-2022 09:50:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 4,'DDD',TO_DATE('13-06-2022 09:10:00','DD-MM-RRRR hh24:mi:ss'),'BREAK IN',TO_DATE('13-06-2022 09:50:00','DD-MM-RRRR hh24:mi:ss') from dual union all
    select 4,'DDD',TO_DATE('13-06-2022 14:50:00','DD-MM-RRRR hh24:mi:ss'),'OUT',TO_DATE('13-06-2022 14:50:00','DD-MM-RRRR hh24:mi:ss') from dual
    )
    , tt as (
      select t.*
       ,lead(logtime,1,created_date) over (partition by emp_id order by logtime) le
       ,mod(sum(decode(mode_type,'IN',0,'OUT',0,1)) over (partition by emp_id order by logtime),2) p
      from attendance_hr t
    )
    select 
      emp_id
     ,(max(logtime)-min(logtime))*24*60 total
     ,(sum(decode(p,1,le-logtime,0)))*24*60 break
    from tt
    group by emp_id
    --select * from tt
    /
    SQL> /
    
    
        EMP_ID      TOTAL      BREAK
    ---------- ---------- ----------
             1        360          0
             2        500         18
             4        420         38
             3        410         28
    
    
    SQL>
    
    
    User_WI23P