我有一个表格,其中列出了员工活跃/不活跃的日期,我想计算员工在某个日期范围内活跃的周数。

因此表 (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/

10-10 20:15