title: PL/SQL (How to calculate the first and last day of any quarter of any year)
body: <p>I have one table, <code>per_all_peopl_f</code>, with following columns:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>There are many entries (1000+) with repeated data and different effective start dates.</p>
  
  <p>I have to calculate the Workforce headcount. That is, the number of employees that exits the company quarterly.</p>
  
  <p>The following columns have to be fetched:</p>
  
  <ul>
  <li>Headcount in 2012 (1st quarter) </li>
  <li>Headcount in 2013 (1st quarter)</li>
  <li>difference between the two headcounts</li>
  <li>% difference</li>
  </ul>
  
  <p>This has to be done quarterly. that is whichever quarter I pass the workforce headcount should be calculated according to that.</p>
  
  <p>The query I have written is </p>
  
  <pre><code>CREATE OR REPLACE FUNCTION function_name
    (l_end_date ,l_start_date  ) 
    RETURN number;
    IS 
      l_emp
  
    BEGIN
      select count(distinct papf.person_id)
        into l_emp
        from per_all_people_f papf
       where papf.emp_flag ='Y'
         and effective_start_date &gt;=l_end_date
         and effective_end_date &lt;=l_start_date ;
  
      return l_emp; 
  END function_name;
  
  
  create xx_pack_name  body
  is 
  crate or replace procedure proc_name( l_quarter number,
                        p_person_id number,
                        l_year number )
      cursor cur_var
      is 
         select function_name(l_start_date ,l_end_date ) EMP_2012,
                function_name(l_start_date1,l_end_date1 ) EMP_2013,
                function_name(l_start_date ,l_end_date )-function_name(l_start_date1,l_end_date1 ) Diff
           from dual;
  
  
  Begin
  if(l_year=2012)
  
  if l_quarter =1
  then
  l_start_date :='01-Jan-2013';
  l_end_date :='31-APR-2013';
  
  elsif l_quarter =2
  then
  l_start_date :='01-May-2013';
  l_end_date :='31-Aug-2013';
  
  else
  l_start_date :='01-Sep-2013';
  l_end_date :='31-Dec-2013';
  
  end if;
  end if;
  if(l_year=2013)
  
  then 
  
  if l_quarter =1
  then
  l_start_date :='01-Jan-2013';
  l_end_date :='31-APR-2013';
  
  elsif l_quarter =2
  then
  l_start_date :='01-May-2013';
  l_end_date :='31-Aug-2013';
  
  else
  l_start_date :='01-Sep-2013';
  l_end_date :='31-Dec-2013';
  
  end if;
  end if;
  
  for cur_val in cur_var 
  loop
  dbms_output.put_line(cur_var.emp_2012 || cur_var.emp_2013 ||cur_var.diff )
  
  end loop
  
  
  
  end xx_pack_name  ;
  </code></pre>
  
  <p>This package is taking too long. </p>
  
  <p>Is there any other way I can calculate the last and first day of quarter of any year ????</p>
  
  <p>And also when I am calculating the </p>
  
  <blockquote>
    <p>percentage function_name(l_start_date ,l_end_date
    )-function_name(l_start_date1,l_end_date1 ) /100</p>
  </blockquote>
  
  <p>the output is not coming in the select statement</p>