我有一个表,其中列出了员工成为活动/非活动的日期,我想计算员工在特定日期范围内活动的周数。
所以表(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
查询需要显示该empid从7月1日到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;
问题是我似乎无法弄清楚如何创建一个查询语句,该查询语句将在某个日期范围内为每个员工遍历所有行,并且只返回每个empid和他们活跃的周数。我更愿意使用基本的SQL而不是PL/SQL,这样我就可以将它转移到可以由用户运行的PeopleSoft查询中,但如果需要的话,我愿意使用Oracle SQL Developer为用户运行它。
数据库:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
在这里,我使用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;
如果不能找到匹配的I
记录(雇员是当前的),coalesce
函数将获取系统日期。如果你的要求是年底的话,你可以把它替换成年底。
请注意,我没有做任何严格的测试,看看你的条目是有序的A/I/A/I等,所以你可能想要添加这种性质的检查,如果你知道你的数据需要它。
如果客户只是想要一个粗略的估计,我会从每次工作的天数开始,除以7并四舍五入。
技巧是将活动日期与其相应的非活动日期对齐,我认为这样做的最佳方法是分别挑选活动日期和非活动日期,按日期对它们进行排序,并通过EmplID
和排名将它们重新连接在一起。在这种情况下,ROW_NUMBER()
分析函数是排名的最佳方法:
WITH
EmpActive AS (
SELECT
EmplID,
EffDt,
ROW_NUMBER() OVER (PARTITION BY EmplID ORDER BY EffDt NULLS LAST) DtRank
FROM ps_job
WHERE HR_Status = 'A'
),
EmpInactive AS (
SELECT
EmplID,
EffDt,
ROW_NUMBER() OVER (PARTITION BY EmplID ORDER BY EffDt NULLS LAST) DtRank
FROM ps_job
WHERE HR_Status = 'I'
)
SELECT
EmpActive.EmplID,
EmpActive.EffDt AS ActiveDate,
EmpInactive.EffDt AS InactiveDate,
ROUND((NVL(EmpInactive.EffDt, TRUNC(SYSDATE)) - EmpActive.EffDt) / 7) AS WeeksActive
FROM EmpActive
LEFT JOIN EmpInactive ON
EmpActive.EmplID = EmpInactive.EmplID AND
EmpActive.DtRank = EmpInactive.DtRank
EmplID = 1000
的第三个gig有一个活动日期,但没有非活动日期,因此ROW_NUMBER
顺序中的NULLS LAST
和两个子查询之间的左连接。
我在这里使用了"days/7"的数学;当你收到客户的回复时,你可以替换你需要的东西。注意,如果没有对应的非活动日期,查询将使用当前日期。
这里有一个SQLFiddle
下面应该可以满足您的要求。我必须在NVL语句
中硬编码结束日期SELECT emplid,
hr_status,
ROUND(SUM(end_date - start_date)/7) num_weeks
FROM (SELECT emplid,
hr_status,
effdt start_date,
NVL(LEAD(effdt) OVER (PARTITION BY emplid ORDER BY effdt),
TO_DATE('12312012','MMDDYYYY')) end_date
FROM ps_job
)
WHERE hr_status = 'A'
GROUP BY emplid,
hr_status
ORDER BY emplid
内部查询将从表中提取员工和HR状态信息,并使用effdt列作为开始日期,并使用LEAD分析函数从表中获取下一个effdt日期值,该值指示下一个状态的开始,因此将是当前行的end_date。如果LEAD函数返回NULL,我们将为其分配您想要的完成日期(12/31/2012)。然后out语句将结果集限制为具有活动HR状态的记录,并计算周数。