Forum Stats

  • 3,770,140 Users
  • 2,253,075 Discussions
  • 7,875,340 Comments

Discussions

Request for Query for Employee Attendance

User_8KQYD
User_8KQYD Member Posts: 7 Red Ribbon
edited Oct 25, 2021 7:50PM in SQL & PL/SQL

Hello All,

create table xx_attendance
(personID number,
trans_date date,
trans_calc varchar2(30),
trans_action varchar2(30),
attribute1 varchar2(30));


select * from xx_attendance;
Duty Hours: 07:30 to 14:30
--employee 1 normal IN and OUT 07:30 to 14:30 Attendance= 7 hours
insert into xx_attendance
values (1, to_date('24-oct-2021'),'0730','IN',null);
insert into xx_attendance
values (1, to_date('24-oct-2021'),'1430','OUT',null);

--employee 2 early OUT 14:30 pm - 11:00 am = 3:30 (3.5 Hrs)
--Attendance = 3.5 hrs
insert into xx_attendance
values (2, to_date('24-oct-2021'),'0730','IN',null);
insert into xx_attendance
values (2, to_date('24-oct-2021'),'1100','OUT',null);

--employee 3 multi IN, OUT then we take min IN, max OUT time 
--IN 7:15 am and OUT 14:30 pm
--Attendance = 7:00 hrs
insert into xx_attendance
values (3, to_date('24-oct-2021'),'0715','IN',null);
insert into xx_attendance
values (3, to_date('24-oct-2021'),'0730','IN',null);
insert into xx_attendance
values (3, to_date('24-oct-2021'),'1230','OUT',null);
insert into xx_attendance
values (3, to_date('24-oct-2021'),'1430','OUT',null);

--emplyoee 4 without OUT then working hrs should be 0
--Attendance = 0 hrs
insert into xx_attendance
values (4, to_date('24-oct-2021'),'0730','IN',null);

--EMPLOYEE 5 worked after office hours tille 4:30 pm, overtime 2 hours
--Attendance = 9 hrs
insert into xx_attendance
values (5, to_date('24-oct-2021'),'0730','IN',null);
insert into xx_attendance
values (5, to_date('24-oct-2021'),'1630','OUT',null);

--employee 6 late coming after 7:30 am , 30 min late , 0.5 hr
--Attendance: 6:5 hours
--Late: 0:30 = 0.5 hrs
insert into xx_attendance
values (6, to_date('24-oct-2021'),'0800','IN',null);
insert into xx_attendance
values (6, to_date('24-oct-2021'),'1430','OUT',null);


Required output:

personID  TransDate           IN     OUT     Worked(hrs)    Early(hr) Overtime(hr)   Late(hr) 
1      24/10/2021            07:30   14:30    7
2      24/10/2021            07:30   11:00    3.5             3.5 (Early)
3      24/10/2021             07:30   14:30    7 
4      24/10/2021              07:30               0
5      24/10/2021               07:30   16:30    9                          2 (overtime)
6      24/10/2021              08:00   14:30    6.5                                    0.5(Late)


Tried using pivot table feature, got IN, OUT.

but need help in the other columns.

Thanks,

Afzal.  


Edit:

Column Attribute1 not related to the query.

Overtime will calculated if employee attendance is more than 7 hrs

Ex: 9 - 7 = 2 hrs OT

Early hours is opposite to OT employee attendance less than 7 hrs ex, 3.5 hrs

Ex : early hrs is 7 - 3.5 = 3.5 hrs

Database : 11.2.0.4.0

Tagged:

Best Answer

  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy
    Accepted Answer

    Not all cases are considered.

    with function ho(t1 varchar2, t2 varchar2) return number as
    begin return nullif(greatest(0, round(24*(to_date(t2,'hh24:mi')-to_date(t1,'hh24:mi')),2)),0); end;
    t(a, b) as (select '07:30', '14:30' from dual
      union all select '07:30', '11:00' from dual
      union all select '07:30', null    from dual
      union all select '07:30', '16:30' from dual
      union all select '08:00', '14:30' from dual)
    select a, b, ho(a, b) du,
                 nullif(greatest(0, ho(a, b)-7), 0) ot,
                 ho('07:30', a) ea,
                 ho(b, '14:30') la
    from t
    /
    
    A     B             DU         OT         EA         LA
    ----- ----- ---------- ---------- ---------- ----------
    07:30 14:30          7                                 
    07:30 11:00        3.5                              3.5
    07:30                                                  
    07:30 16:30          9          2                      
    08:00 14:30        6.5                    .5           
    
    

    Result may be wrong at 00:00:00 first day of month.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Oct 25, 2021 6:49PM

    Hi, @User_8KQYD

    Thanks for posting the CREATE TABLE and INSERT statements, but

    insert into xx_attendance
    values (1, to_date('24-oct-2021'),'0730','IN',null);
    

    never all TO_DATE with just one argument; that's just asking for mistakes. Always call TO_DATE with at least two arguments.

    Don't forget to post your full Oracle version (e.g. 18.4.0.0.0).

    What role does attribute1 play in this problem?

    Don't forget to explain how you get the desired results from the given data. For example, person_id=2 has 3.5 early hours. Wat is there in the table that tells us that these hours were early? How do we know there was no overtime for that person_id?

  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy
    Accepted Answer

    Not all cases are considered.

    with function ho(t1 varchar2, t2 varchar2) return number as
    begin return nullif(greatest(0, round(24*(to_date(t2,'hh24:mi')-to_date(t1,'hh24:mi')),2)),0); end;
    t(a, b) as (select '07:30', '14:30' from dual
      union all select '07:30', '11:00' from dual
      union all select '07:30', null    from dual
      union all select '07:30', '16:30' from dual
      union all select '08:00', '14:30' from dual)
    select a, b, ho(a, b) du,
                 nullif(greatest(0, ho(a, b)-7), 0) ot,
                 ho('07:30', a) ea,
                 ho(b, '14:30') la
    from t
    /
    
    A     B             DU         OT         EA         LA
    ----- ----- ---------- ---------- ---------- ----------
    07:30 14:30          7                                 
    07:30 11:00        3.5                              3.5
    07:30                                                  
    07:30 16:30          9          2                      
    08:00 14:30        6.5                    .5           
    
    

    Result may be wrong at 00:00:00 first day of month.