PL/SQL(如何计算一年中任何一个季度的第一天和最后一天) [英] PL/SQL (How to calculate the first and last day of any quarter of any year)
问题描述
我有一个表per_all_peopl_f，其中包含以下列:
I have one table, per_all_peopl_f, with 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
有很多条目(1000多个)，这些条目具有重复的数据和不同的有效开始日期.
There are many entries (1000+) with repeated data and different effective start dates.
我必须计算劳动力人数.也就是说，每季度退出公司的员工人数.
I have to calculate the Workforce headcount. That is, the number of employees that exits the company quarterly.
必须提取以下列:
- 2012年员工人数(第一季度)
- 2013年员工人数(第一季度)
- 两个员工人数之间的差异
- ％差异
此操作必须每季度执行一次.那是我通过员工人数的那个季度，都应根据该季度进行计算.
This has to be done quarterly. that is whichever quarter I pass the workforce headcount should be calculated according to that.
我写的查询是
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 >=l_end_date
and effective_end_date <=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 ;
此软件包花费的时间太长.
This package is taking too long.
我还有其他方法可以计算任何一年的季度的最后一天和第一天吗????
Is there any other way I can calculate the last and first day of quarter of any year ????
当我计算
函数名称百分比(l_start_date，l_end_date )-function_name(l_start_date1，l_end_date1)/100
percentage function_name(l_start_date ,l_end_date )-function_name(l_start_date1,l_end_date1 ) /100
输出未出现在select语句中
the output is not coming in the select statement
推荐答案
我发现这个问题非常令人困惑.如果真正的问题是如何计算任意DATE的四分之一，那么已经有很多示例，例如:
I find this question very confusing. If the real question is how to calculate the quarter of an arbitrary DATE then there's already plenty of examples, like:
- Oracle - break dates into quarters
- Find First and Last Day of the last Quarter in ORACLE
如何计算任意日期的四分之一
一些测试日期:
create table lots_of_dates as
select trunc(sysdate - level * 7) as d
from dual
connect by level <= 52;
找到宿舍:
select d,
to_char(d, 'YYYY-Q') as QUARTER,
trunc(d, 'Q') as Q_FIRST_DAY,
add_months(trunc(d, 'Q'), 3) - 1 as Q_LAST_DAY
from lots_of_dates
order by 1;
结果:
D QUARTE Q_FIRST_DAY Q_LAST_DAY
------------------ ------ ------------------ ------------------
02-SEP-12 2012-3 01-JUL-12 30-SEP-12
09-SEP-12 2012-3 01-JUL-12 30-SEP-12
16-SEP-12 2012-3 01-JUL-12 30-SEP-12
23-SEP-12 2012-3 01-JUL-12 30-SEP-12
30-SEP-12 2012-3 01-JUL-12 30-SEP-12
07-OCT-12 2012-4 01-OCT-12 31-DEC-12
14-OCT-12 2012-4 01-OCT-12 31-DEC-12
21-OCT-12 2012-4 01-OCT-12 31-DEC-12
28-OCT-12 2012-4 01-OCT-12 31-DEC-12
04-NOV-12 2012-4 01-OCT-12 31-DEC-12
11-NOV-12 2012-4 01-OCT-12 31-DEC-12
...
一个PL/SQL过程，该过程返回一个季度的第一天和最后一天
除年份部分外，所有年份的季度开始日期和结束日期都是恒定的. IE.第二季度始终在每年的4月1日开始，并在每年的6月30日结束.这样就可以固定日期和月份，而只需要调整年份部分.
The quarter's start and end dates are constant for all years except the year part. I.e. the second quarter always begins on 1st April and end on 30th June on every year. Thus the day and month can be fixed and only year part have to be adjusted.
一个函数只能返回一个值，因此该子例程将被实现为过程.我还为该过程提供了一个函数包装器:
A function can only return one value so the subroutine is implemented as procedure instead. I also provided a function wrappers to the procedure:
-- raises CASE_NOT_FOUND for non-existing quarters
create or replace procedure get_quarter_days(
p_year in number,
p_quarter in number,
p_first_day out date,
p_last_day out date
) deterministic as
begin
case p_quarter
when 1 then
p_first_day := to_date(p_year || '-01-01', 'YYYY-MM-DD');
p_last_day := to_date(p_year || '-03-31', 'YYYY-MM-DD');
when 2 then
p_first_day := to_date(p_year || '-04-01', 'YYYY-MM-DD');
p_last_day := to_date(p_year || '-06-30', 'YYYY-MM-DD');
when 3 then
p_first_day := to_date(p_year || '-07-01', 'YYYY-MM-DD');
p_last_day := to_date(p_year || '-09-30', 'YYYY-MM-DD');
when 4 then
p_first_day := to_date(p_year || '-10-01', 'YYYY-MM-DD');
p_last_day := to_date(p_year || '-12-31', 'YYYY-MM-DD');
end case;
end;
/
show errors
create or replace function get_quarter_first_day(
p_year in number,
p_quarter in number
) return date deterministic as
v_first_day date;
v_last_day date;
begin
get_quarter_days(p_year, p_quarter, v_first_day, v_last_day);
return v_first_day;
end;
/
show errors
create or replace function get_quarter_last_day(
p_year in number,
p_quarter in number
) return date deterministic as
v_first_day date;
v_last_day date;
begin
get_quarter_days(p_year, p_quarter, v_first_day, v_last_day);
return v_last_day;
end;
/
show errors
如何使用上面的子例程:
How to use the subroutines above:
declare
v_first_day date;
v_last_day date;
begin
get_quarter_days(2011, 1, v_first_day, v_last_day);
dbms_output.put_line(v_first_day || ' - ' || v_last_day);
get_quarter_days(2012, 2, v_first_day, v_last_day);
dbms_output.put_line(v_first_day || ' - ' || v_last_day);
get_quarter_days(2013, 3, v_first_day, v_last_day);
dbms_output.put_line(v_first_day || ' - ' || v_last_day);
get_quarter_days(2014, 4, v_first_day, v_last_day);
dbms_output.put_line(v_first_day || ' - ' || v_last_day);
dbms_output.put_line(get_quarter_first_day(2015, 1) || ' - ' ||
get_quarter_last_day(2015, 1));
end;
/
这篇关于PL/SQL(如何计算一年中任何一个季度的第一天和最后一天)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
