sql - Efficiency of quarterly calculation of workforce headcount -

i have 1 table, per_all_peopl_f, following columns:

name  person_id  emp_flag  effective_start_date   effective_end_date   doj --------------------------------------------------------------------------------     abc   123          y       30-mar-2011              30-mar-2013       10-feb-2011 abc   123          y       24-feb-2011              27-feb-2011       10-feb-2011 def   345          n       10-apr-2012              30-dec-4712       15-sep-2011 

there many entries (1000+) repeated data , different effective start dates.

i have calculate workforce headcount. is, number of employees exits company quarterly.

the following columns have fetched:

1. headcount in 2012 (1st quarter)
2. headcount in 2013 (1st quarter)
3. difference between 2 headcounts
4. % difference

the query used find headcount quarterly is:

function1:

create or replace function function_name   (l_end_date ,l_start_date  )    return number;        l_emp    begin     select count(distinct papf.person_id)       l_emp       per_all_people_f papf      papf.emp_flag ='y'        , effective_start_date >=l_end_date        , effective_end_date <=l_start_date ;      return l_emp;  end function_name; 

main package:

create xx_pack_name  body      cursor cur_var             select function_name('01-mar-2012','31-may-2012') emp_2012,               function_name('01-mar-2013','31-may-2013') emp_2013,               function_name('01-mar-2012','31-may-2012')-function_name('01-mar-2013','31-may-2013') diff          dual;  end xx_pack_name  ; 

is cost efficient?

seems variant in case if there index on @ least effective_start_date , effective_end_date fields of per_all_people_f table.

ideal variant query

create index x_per_all_people_search on per_all_people_f(      effective_start_date,   effective_end_date,    person_id,     emp_flag ) 

but may expensive maintain (disk cost, insertion speed).

also, cursor in package body must contain subquery , reuse function call results:

cursor cur_var    select      emp_2012,     emp_2013,     (emp_2013 - emp_2012) diff   (    select       function_name('01-mar-2012','31-may-2012') emp_2012,      function_name('01-mar-2013','31-may-2013') emp_2013    dual   ); 

of course best solution minimize context switches , values single sql query. also, can supply parameters directly cursor:

cursor cur_var(   start_1 date, end_1 date,    start_2 date, end_2 date )    select      emp_2012,     emp_2013,     (emp_2013 - emp_2012) diff   (    select       (        select           count(distinct papf.person_id)                  per_all_people_f papf                  papf.emp_flag = 'y'          ,           effective_start_date >= trunc(start_1)          ,           effective_end_date   <= trunc(end_1)      ) emp_2012,      (        select           count(distinct papf.person_id)                  per_all_people_f papf                  papf.emp_flag = 'y'          ,           effective_start_date >= trunc(start_2)          ,           effective_end_date   <= trunc(end_2)      ) emp_2013    dual   ); 

from point of view function/cursor parameters generic, may better create wrapper takes input parameters quarter number , 2 years compare.

and last, if results planned used in pl/sql (i suppose because of returning single row) don't use cursor @ all, return calculated values through output parameters. point of view if need quarter data full year in 1 cursor may more efficient count quarters , compare in single query.