Count number of leave days according to location

Question

The program is to create a package count the no. of leaves taken by an employee according to location of org. Eg :- If company XYZ is a company which is located in many locations.Each location has its own set of rules... in some locations saturday is working and in some both saturday and sunday are considered to be holidays. i have made the package as follows but i think it can be made easier.

Create or replace package Body
Leave_location
Procedure Leave_location_proc(Person_id number,
                              Start_date Date
                              )
as 
k Number;
count_weekend number;

Cursor c_var
is 
select pt.person_id,
lt.end_date-lt.start_date Number_of_leaves_excluding_the_end_date,
lt.Start_date      //Start leave date,
lt.Saturday      // 1 if saturday is not working, null if working 
lt.Sunday          ,
loc.location_id
from person_table pt,
leave_table lt
and leave_table.person_id=person_table.person_id
and loc.location_id=pt.person_id \\filtering the location for the particular employee;
Begin

Count_weekend :=0;
       for cursor_var in c_var
       k :=cursor_var.Number_of_leaves_excluding_the_end_date+1
       loop
         for i in..k
         loop
          if(to_char(cursor_var.start_date,'DY') in ('Sat','Sun')
          Then
          if(cursor_var.Saturday is not null and cursor_var.sunday is not null))
          Then
          Count_weekend :=2;
          Else
          count_weekend :=1;
          end if ;
          end if;
       cursor_var.Start_date :=cursor_var.Start_date+1;
No_of_leaves :=K-Count_weekends;
Count_weekends:=Count_weekends+1;
end loop;
end loop;

End Leave_location;
Leave_location_proc;

This code doesnt seem to be efficient. Any more better methods

THis does not look like code that is actually final and running, but why do you think it is inefficient? — Darius X., Feb 26 '13 at 16:52
This "feels" like something that can be done in one query, but your cursor isn't so clear... can you show us your table structure and give some input examples ? — A.B.Cade, Feb 26 '13 at 16:55
See [this post](http://stackoverflow.com/questions/14898357/calculate-business-days-in-oracle-sqlno-functions-or-procedure) , it might give you some ideas — A.B.Cade, Feb 26 '13 at 16:59
@A.B.Cade Actually the tables are built in.... oracle hrms tables — user2065695, Feb 26 '13 at 17:07

Art · Answer 1 · 2013-02-26T18:17:31.757

You do not need PL/SQL for this. Here's what I suggest - this is query only that can be converted to cursor. You can calculate all that you need in the query. Then you may use it in your cursor if necessary:

-- calculate values from inner/main query
SELECT all_needed_columns_from_inner_query
    , (Number_of_leaves_excluding_the_end_date - nbr_of_wkends) whatever_count
  FROM
 ( -- your main query --
  SELECT ...
       , lt.end_date-lt.start_date Number_of_leaves_excluding_the_end_date
       , (CASE WHEN lt.Saturday IS NOT NULL AND lt.Sunday IS NOT NULL THEN 2 ELSE 1) nbr_of_wkends
    ...
 WHERE...
 )

Post sample data if you need more help. But example above should give you clear direction and idea - use CASE to calculate all values you need. Calculate in PL/SQL only those values that absolutely cannot be calculated in SQL for whatever reason...

Count number of leave days according to location

1 Answers1