查找员工在两个日期之间活跃的周数的 SQL

Question

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

因此表 (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 位生产

Answer 1

我在这里使用 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 玩这个.