我有一个表格,其中列出了员工活跃/不活跃的日期,我想计算员工在某个日期范围内活跃的周数。
因此表 (ps_job) 将具有如下值:
EMPLID EFFDT HR_STATUS
------ ----- ------
1000 01-Jul-11 A
1000 01-Sep-11 I
1000 01-Jan-12 A
1000 01-Mar-12 I
1000 01-Sep-12 A
该查询需要向我显示该 emplid 从 11 年 7 月 1 日到 12 年 12 月 31 日活跃的周数。
所需的结果集将是:
EMPLID WEEKS_ACTIVE
------ ------------
1000 35
通过添加以下 SQL 的结果,我得到了数字 35:
SELECT (NEXT_DAY('01-Sep-11','SUNDAY') - NEXT_DAY('01-Jul-11','SUNDAY'))/7 WEEKS_ACTIVE FROM DUAL;
SELECT (NEXT_DAY('01-Mar-12','SUNDAY') - NEXT_DAY('01-Jan-12','SUNDAY'))/7 WEEKS_ACTIVE FROM DUAL;
SELECT (NEXT_DAY('31-Dec-12','SUNDAY') - NEXT_DAY('01-Sep-12','SUNDAY'))/7 WEEKS_ACTIVE FROM DUAL;
问题是我似乎无法弄清楚如何创建一个查询语句,该语句将遍历特定日期范围内每个员工的所有行,并只返回每个 emplid 和他们活跃的周数。我更喜欢使用基本 SQL 而不是 PL/SQL,以便我可以将其传输到可以由用户运行的 PeopleSoft 查询,但如果需要,我愿意使用 Oracle SQL Developer 为用户运行它。
数据库:Oracle 数据库 11g 企业版 11.2.0.3.0 版 - 64 位生产
最佳答案
在这里,我在子查询中使用 lead
来获取下一个日期,然后对外部查询中的间隔求和:
with q as (
select EMPLID, EFFDT, HR_STATUS
, lead (EFFDT, 1) over (partition by EMPLID order by EFFDT) as NEXT_EFFDT
from ps_job
order by EMPLID, EFFDT
)
select EMPLID
, trunc(sum((trunc(coalesce(NEXT_EFFDT, current_timestamp)) - trunc(EFFDT)) / 7)) as WEEKS_ACTIVE
from q
where HR_STATUS = 'A'
group by EMPLID;
如果找不到匹配的
coalesce
记录(员工在职),I
函数将获取系统日期。如果那是您的规范,您可以替换年底。请注意,我没有进行任何严格的测试来查看您的条目是否按 A/I/A/I 等排序,因此如果您知道您的数据需要它,您可能想要添加这种性质的检查。
随意在 SQL Fiddle 上玩这个。
关于查找员工在两个日期之间活跃的周数的 SQL,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/16446598/