PL/SQL code to find leave detail of employee

Question

I Have three tables :-

per_Absences_table have start_date, end_date. per_absence_type have Privelege, Casual etc per_people_table have Employee number, Employee name

I Want to find out number of leaves applied by an employee between two dates.

Eg :- If i pass '1-JAN-2013' as start date and '20-JUN-2013' as end date then all the leaves applied by the employee will come as output

I Have written code for it which is working when i pas any parameter. But suppose there is an employee who applies a leave from 10-May-2013 to 20-May-2013 and i pass the starting date as 11-May-2013 i.e. the employees who have taken leave after 11-may-2013 should come.

i.e. the leave applied from 10-May-2013 to 20-May-2013 should appear. Also if i want to find out the employees who have applied leave till 19-May-2103 even

then this leave should appear. All the other cases have been taken care of.

per_people_table :-

EMP_NUM     EMP_NAME 
P101        XYZ

PER_ABSENCE_TABLE

EMP_NUM  START_DATE    END_DATE       TYPE_ID
101      10-May-2013   15-May-2013    1

per_absence_type

type_id     leave_type
1            casual

Now, if i pass the parameter as 11-May-2013 (p_start_date) even then this record should appear.

and if i pass the parameter as 19-May-2013 (before the actual end date) even then this record should appear.

i.e.

declare

l_r varchar2(10) :=NULL;
L_E VARCHAR2(10) := NULL;
p_person_id VARCHAR2(10) :='P101',
                p_start_date VARCHAR2(10);
                p_end_date VARCHAR2(10);
                leave_type VARCHAR2(10);

BEGIN

Leave_detail_packa.Leave_detail_packA(NULL,NULL,'P101','11-MAY-2013',NULL,NULL);
END;

---

Leave_detail_packa body
procedure Leave_details( errbuff out varchar2,
            retcode out varchar2,
            p_person_id VARCHAR2,
            p_start_date varchar2,
            p_end_date varchar2,
            leave_type varchar2
            )
as 
l_st_date :=to_date(trunc(fnd_conc_date.string_to_date(p_start_date)));
l_end_date :=to_date(trunc(fnd_conc_date.string_to_date(p_end_date)));  

/****************Cursor for start date ****************************/
Cursor c_var_st_date
is select
to_char(paa.date_start,'DD-MON-RRRR') Start_date

from    per_Absences_table paa,
    per_absence_type paat

where ( nvl(l_st_date,paa.date_start) between paa.date_start and paa.date_end
    or paa.date_start >=  nvl(l_st_date,paa.date_start))

paa.type_id =paat.type_id
and paat.name = nvl(leave_type,paat.name);



/****************Cursor for end date ****************************/
Cursor c_var_nd_date
is select
to_char(paa.date_end,'DD-MON-RRRR') End_date

from    per_Absences_table paa,
    per_absence_type paat

where ( nvl(l_st_date,paa.date_end) between paa.date_start and paa.date_end
    or paa.date_end <=  nvl(l_st_date,paa.date_end))

paa.type_id =paat.type_id
and paat.name = nvl(leave_type,paat.name);


/********** Cursor to give all the leave details ***************/

Cursor c_var(l_start_date varchar2,
         l_end_date1 varchar2)

is 
select 
emp_number employee_number,
emp_name employee_name,
leave_type Leave_type,
to_char(paa.date_end,'DD-MON-RRRR') End_date,
to_char(paa.date_start,'DD-MON-RRRR') Start_date,
sum(No_of_days) Leave_days

from 
per_Absences_table paa,
    per_absence_type paat,
per_people_table

where 

paa.date_start>= ( nvl(l_start_date),to_char(paa.date_start,'DD-MON-RRRR'))
    and paa.date_end <= ( nvl(l_end_date1),to_char(paa.date_end,'DD-MON-RRRR'))
AND EMP_NUM=P_PERSON_ID;


/**** Begin looping******************************************************/

for ( c_var_number in c_var_st_date)
loop
    for ( c_var_number1 in c_var_nd_date)
loop
    for ( c_var_number3 in c_var(c_var_st_date.start_end,c_var_nd_date.end_date)
loop


    dbms.output_put.line( c_var_number3.end_date,c_var_number3.start_date);

end loop;
end loop;
end loop;

Answer 1

Firstly, there's no need to use PL/SQL to do this at all. It can be done in a single SQL statement.

Secondly, you haven't provided the DDL for your tables so I'm going to assume they're set us as follows (also in a SQL Fiddle with additional cases):

create table per_people ( 
    emp_num number not null
  , emp_name varchar2(100) not null
  , constraint pk_per_people primary key (emp_num)
    );

create table per_absence_type ( 
    type_id number not null
  , leave_type varchar2(100) not null
  , constraint pk_absence_type primary key (type_id)
    );

create table per_absence (
    emp_num number not null
  , start_date date not null
  , end_date date not null
  , type_id number not null
  , constraint pk_per_abs primary key (emp_num, start_date)
  , constraint fk_per_abs_emp foreign key (emp_num)
               references per_people(emp_num)
  , constraint fk_per_abs_typ foreign key (type_id)
               references per_absence_type (type_id)
  , constraint chk_per_abs_dates check (start_date <= end_date )
    );

These are the minimal constraints I'd have on these tables. If you're missing one of them I'd recommend adding it.

This query will give you all the information from all tables for all absences:

select pp.emp_num
     , pp.emp_name
     , pa.start_date
     , pa.end_date
     , pat.leave_type
  from per_people pp
  join per_absence pa
    on pp.emp_num = pa.emp_num
  join per_absence_type pat
    on pa.type_id = pat.type_id

You want to find all employees who will be on leave between two dates. For this you need to add a WHERE clause to the above query. Let's assume that employees are taking the following absences:

insert all
 into per_absence values( 101, date '2013-05-10', date '2013-05-15', 1)
 into per_absence values( 101, date '2013-09-10', date '2013-09-10', 1)
 into per_absence values( 102, date '2013-05-15', date '2013-05-20', 1)
 into per_absence values( 103, date '2013-05-05', date '2013-05-20', 1)
select * from dual;

If you pass a start date of 2013-05-15 and an end date of 2013-05-20, you would expect three rows to be returned. If you pass a start date of 2013-05-16 you would only expect two.

You have to look at how you want to construct your WHERE clause. Looking at the date you want where your start date is between an absence start and end date or your end date is between an absence start and end date. Don't forget that an absence can only be one day so you want to include the start and end dates in the comparison.

This changes the query to:

select pp.emp_num
     , pp.emp_name
     , pa.start_date
     , pa.end_date
     , pat.leave_type
  from per_people pp
  join per_absence pa
    on pp.emp_num = pa.emp_num
  join per_absence_type pat
    on pa.type_id = pat.type_id
 where ( :start_date between pa.start_date and pa.end_date
         or :end_date between pa.start_date and pa.end_date
            )

Now, you want to find the number of leaves for a specific employee between the given dates. As your tables are properly normalised you can just GROUP BY the EMP_NUM to get it for every employee.

Assuming you want this for EMP_NUM 1 (single employee so no need for the GROUP BY) between 2013-05-15 and 2013-05-20 the query would look like this:

select count(*) as no_leaves
  from per_people pp
  join per_absence pa
    on pp.emp_num = pa.emp_num
  join per_absence_type pat
    on pa.type_id = pat.type_id
 where emp_num = 101
   and ( date '2013-05-15' between pa.start_date and pa.end_date
         or date '2013-05-20' between pa.start_date and pa.end_date
            )

Answer 2

If I were faced with this problem, I would create a calendar table, JOIN using BETWEEN paa.date_start AND paa.date_end; that way regardless of when their leave started or ended I know whether they were on leave for any given day. If from there you are interested in finding out how many leaves they took, defining consecutive days as an instance of a leave, then you'd just have to find gaps between leave days.